Results are early 1800s

[Guest]

I am using the following script to populate a table with dates. However, the
dates that are populating are early 1800s instead of today's dates.

strDate = Format(Date, "mm/dd/yyyy")

Select Case fraDept.Value 'Determine which dept has been selected from
the list

Case 1

sIdList = ""

With Me.lstItems

For Each vItem In .ItemsSelected

sIdList = sIdList & .ItemData(vItem) & ","

Next vItem

End With



sIdList = Left(sIdList, Len(sIdList) - 1)

sSql = "Update Billing SET Closed = '-1', CloseDate = " &
strDate _

& " Where ID in ( " & sIdList & ");"

CurrentDb.Execute sSql, dbFailOnError


Any thoughts?

Thank you in advance.

 

[Guest]

Figured it out. ACCESS was reading it as time instead of a date. Needed to
put in # signs for ACCESS to recognize it as a date.

 

[Graham Mandeno]

Hi Gina

Your update query (shortened) looks like this:

SET CloseDate = 06/28/2006

SQL sees this as an arithmetic expression "6 divided by 28 divided by 2006",
which is a Very Small Number!

Since "Date Zero" is 30 Dec 1899, all your CloseDates will be on that day.

To tell SQL to interpret this as a date, you must enclose it in # signs.
The easiest way to do this is to include them in the format:

strDate = Format(Date, "\#mm/dd/yyyy\#")

SQL also understands the Date() function, so if you are always setting the
CloseDate to today you can just say:

SET CloseDate = Date()

Also, if Closed is a boolean (yes/no) field, lose the quotes around the -1:

SET Closed = -1

or better:

SET Closed = True