Insert into does keep the same value

[ping99]

Hi all,

I try to insert a string value date into a temptable ACCESS table but changed to unexpected value : WHY ?

dim strdate as string
dim strsql as string

strdate = "2006-04-30"

strsql = " INSERT INTO TEMPTABLE(VDATE) VALUES ("&strdate&")"

docmd.runsql(strsql)

but always gave me the value 1972 in my temptable for
the field vdate is the text field not date ? instead of 2006-04-30

Please assist or explain why ? it changed from 2006-04-30 to 1972.

Thank in advance,
ping

 

[mharroff]

mathematically speaking 2006 minus 4 minus 30 equals 1972

 

[BobRodes]

Let's see.

If you evaluate strSql in your code, it will be this string:

INSERT INTO TEMPTABLE(VDATE) VALUES (2006-04-30)

Now what you're really saying here is to insert into the vdate field the value 2006 minus 4 minus 30, or 1972. So, it's performing the arithmetic and inputting the result.

To fix this, you'll want to amend your SQL statement as follows:

INSERT INTO TEMPTABLE(VDATE) VALUES ([COLOR=red]'[/color]2006-04-30[COLOR=red]'[/color])

Which will say to enter 2006-04-30 as a literal string. To accomplish this from your VB code:

strsql = " INSERT INTO TEMPTABLE(VDATE) VALUES ([COLOR=red]'[/color]"&strdate&"[COLOR=red]'[/color])"

Should do the trick, assuming that your data type in your SQL table can automatically convert a string to a date.

HTH

Bob

 

[BobRodes]

Oops, forgot I was in Access. I believe you need double quotes instead of single quotes then? If so, you need to put

strsql = " INSERT INTO TEMPTABLE(VDATE) VALUES ([COLOR=red]""[/color]"&strdate&"[COLOR=red]""[/color])"

instead. In VB or VBA, to include a literal quotation mark in a string, you have to double it. This can get a bit confusing when you have quote marks around the string as well:

dim x as string
x = "I said ""Hello Joe, how are you?"" and Joe responded ""I'm fine, and you?"""
debug.print x

would show

I said "Hello Joe, how are you?" and Joe responded "I'm fine, and you?"

in the debug window.

HTH

Bob

 

[ping99]

Thanks a lot BOB,

You are right because I forgot a single quote ('"&strdate&"').

It works fine.

 

[SuryaF]

Suggestion: use # instead of quotes and format the date as d-mmm-yyyy to avoid date vs month confusion.