asked on

Bad Date Value

Hello,

I'm writing a date value into a date/time field in an Access database, but not getting the right results. Here's the code:

gsSQL = "INSERT INTO WhereIsIt (Item,Location,Date_Created) "
gsSQL = gsSQL + "VALUES(" + sSQLString(txtItem) + "," + sSQLString(txtLocation) & "," & Format(Now, "mm/dd/yy") & ")"
gdbKnowledgeTracker.Execute gsSQL

The database field contains:

1:22:17 AM

It's 5:30 PM here, and ALL records are getting the same time. What gives?

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ldunscombe

You need to specifiy now as a function ie Format(Now(), "mm/dd/yy")

Leigh

GrahamSkan

If it is a Date-time field, there is no need to format it. Formatting converts to a string, and Access will just have to re-interpret it back to a Date-time.

gsSQL = gsSQL + "VALUES(" + sSQLString(txtItem) + "," + sSQLString(txtLocation) & "," & Now()

GrahamSkan

Re "pound" signs.

If you specify the date as a string enclosed by hashes (#), e.g. #10/25/07#, Access will interpret that as a date. If it is ambiguous, e.g. #10/12/07#, it will assume that it is the American format.

Pound signs (£) don't work for me in that context.

clarkscott

You've formatted the date "mm/dd/yyyy" but didn't request anything about TIME.
Maybe - format(now(), "mm/dd/yyyy hh:nn) would work for you since now you're including the TIME.
Scott C