Q. Why do my dates appear as 30/12/1899 ?

[Dave]

Hi,

I'm using Excel to access an Access DB. I have set up my fields as
Date/Time and my command string looks like this:

sSQL = "INSERT INTO barcode_dtls "
sSQL = sSQL & "(bcd_barcode,bcd_ref_no,bcd_date_created,"
sSQL = sSQL & "bcd_date_cleared,bcd_created_by) "
sSQL = sSQL & "VALUES ("
sSQL = sSQL & p_barcode & ","
sSQL = sSQL & p_ref_no & ","
sSQL = sSQL & Date & ","
sSQL = sSQL & Date & ","
'sSQL = sSQL & Application.UserName & ");"
sSQL = sSQL & "'xxxx'" & ");"

oCmd.CommandText = sSQL
oCmd.Execute

Apologies for making this long winded by including all the source code
but, when I look at my rows in Access they always have 30/12/1899 in
them. I'm now very confused. This should be straight forward but it's
now hair-pulling time - what little I have left!

Also, I wanted to store Application.Username in a Access Text field -
but it refuses to run the line which is commented out (above). I can
only get it to insert the 'xxxx' bit - any ideas? Do I need to run
some functions on these first?

Any help would be great,
Thanks.

Dave

 

[Stan Scott]

Dave,

This means that nothing is going into the date fields. Empty dates are set
to the date equivalent of zero, which happens to be 30/12/1899.

Okay, that's the problem, so here's what I think. Where are your Excel
dates coming from? Do you have a "Date" range? That won't fly -- it's
reserved. Setting up valid date references could be the whole issue.

To further investigate the problem, have your code send the SQL string to
the Debug window, instead of updating the Access DB. That way, you can see
exactly what the SQL string is, and how you have to fix it.

Hope this helps,

Stan Scott
New York City

 

[Dave]

On Mon, 2 Aug 2004 18:47:52 -0400, "Stan Scott"

Hi Stan,

Thanks for the information. You were indeed right: I wasn't passing
the dates into Access properly - everything is fine now.

I have included the working code below - incase anyone else is
interested. The solution was all in my use of the single and double
quotes.

Again, many thanks,
Dave

sSQL = "INSERT INTO barcode_dtls "
sSQL = sSQL & "(bcd_barcode,bcd_ref_no,bcd_date_created,"
sSQL = sSQL & "bcd_date_cleared,bcd_created_by) "
sSQL = sSQL & "VALUES ("
sSQL = sSQL & p_barcode & ","
sSQL = sSQL & p_ref_no & ","
sSQL = sSQL & "'" & Date & "'" & ","
sSQL = sSQL & "'01/01/1900'" & ","
sSQL = sSQL & "'" & Application.UserName & "'" & ");"