Date Conversion again

[Guest]

I have the following insert query that gives me a type conversion error when
I try to run it. I have tried CDate() and FormatDateTime() in different
combinations but none work. The BirthDate field is a SQL smalldatetime
datatype in a table linked to the Access database I am running this in. This
is in a vb module. Any ideas would be greatly appriciated.

sqlString = "INSERT INTO guest_MET_Consumer (BirthDate) VALUES ("
sqlString = sqlString + FormatDateTime(Me.BirthDate.Value, vbShortDate) + ")"
DoCmd.RunSQL sqlString

 

[Ken Snell [MVP]]

Delimit dates with # characters:

sqlString = "INSERT INTO guest_MET_Consumer (BirthDate) VALUES (#"
sqlString = sqlString + FormatDateTime(Me.BirthDate.Value, vbShortDate) +
"#)"
DoCmd.RunSQL sqlString

 

[Guest]

Thanks Ken. Worked great.
Barry

Delimit dates with # characters:

sqlString = "INSERT INTO guest_MET_Consumer (BirthDate) VALUES (#"
sqlString = sqlString + FormatDateTime(Me.BirthDate.Value, vbShortDate) +
"#)"
DoCmd.RunSQL sqlString

 

[Wayne Morgan]

In addition to Ken's comment, be careful when concatenating values that
could be interpreted as numbers by using + instead of &. You may get
unexpected results. The & symbol will always concatenate the string, the +
symbol may act as an operator.

Example:
If txtMyTextbox is Null then

strMyString = "This is a test " & txtMyTextbox + chr(13) + chr(10) & " This
should be on another line"

Will yield:
This is a test This should be on another line

because the Null gets propagated through the expression (where the + signs
are) and returns Null for the entire expression. If the values are number or
can be interpreted as numbers, the + is likely to try an arithmetic
operation. Also, the above example will fail if the value in txtMyTextbox
can be interpreted as a number.