Type Mismatch on Format$ Date

[cfbeginner]

Hi,

I'm getting Run time error 13: Type mistmatch' on the below code:


If (Me.Date = "") Or IsNull(Me.Date) Then Forms! frmContact!contactdate = Null
Else
Forms!frmContact!contactdate = Format$(DateValue(Me.Date), "yyyy-mm-dd")
End If

My debugger points the problem line is

Forms!frmContact!contactdate = Format$(DateValue(Me.Date), "yyyy-mm-dd")

Also, Me.Date is in the format of '2/29/2006' before I use the Format$ to convert it to "yyyy-mm-dd'

This is a MS Access 2003 application that use SQL server as a backend.

Any help is appreciated!

 

[MajP]

get rid of the $ sign

Some functions have two versions: one that returns a Variant data type and one that returns a String data type. The Variant versions are more convenient because variants handle conversions between different types of data automatically. They also allow Null to be propagated through an expression. The String versions are more efficient because they use less memory.

Consider using the String version when:

· Your program is very large and uses many variables.
· You write data directly to random-access files.

The following functions return values in a String variable when you append a dollar sign ($) to the function name. These functions have the same usage and syntax as their Variant equivalents without the dollar sign.

Chr$ ChrB$ *Command$
CurDir$ Date$ Dir$
Error$ Format$ Hex$
Input$ InputB$ LCase$
Left$ LeftB$ LTrim$
Mid$ MidB$ Oct$
Right$ RightB$ RTrim$
Space$ Str$ String$
Time$ Trim$ UCase$

 

[MajP]

Oops. Never mind. Looks like you do want to return a string, I think. Thought you wanted to return a date type.

 

[JerryKlmns]

DateValue function converts a string to a date

>Me.Date is in the format of '2/29/2006'
and propably is not a text field containing a string but rather a date field

Just use Format$(Me.Date, "yyyy-mm-dd")

And BTW, are we going to experience a leap year in 2006 ?????

 

[cfbeginner]

I used Format$(Me.Date, "yyyy-mm-dd")
but it gave me the below error:
Runtime error '2113': The value you entered isn't valid for this field.

ContactDate field is in 'Datetime' in my SQL table.

Any ideas??

JerryKlmns,
Leap year is in 2008.

 

[JerryKlmns]

So this should run ok

Format(DateSerial(Year(Me.Date),Month(Me.Date),Day(Me.Date),"yyyy-mm-dd")

The text box on your form couldn't be formated as yyyy-mm-dd since values are dates

 

[cfbeginner]

Hi There,
I'm now getting the error:

Wrong number of arugments or invalid peoperty assignment.

Thanks

 

[PHV]

And why not simply this ?
If Not IsDate(Me.Date) Then
Forms!frmContact!contactdate = Null
Else
Forms!frmContact!contactdate = Me.Date
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886