Dates

[nickellis]

I am having major trouble with a simple date, and it's driving me insane.

I am working on a database to store user, asset, software and license info for an IT company. There is a requirement for a table to record every transaction, an audit trail in effect.

I am working in an Access 2000 adp project connecting to an MS-SQL 2000 database.

This:

On Error GoTo Err_cmdSaveClose_Click
Dim concAction As String
Dim thisUser As String
Dim analyst As String
Dim myDate As Date
    
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    concAction = "Added asset " & Me.assetNumber.Value
    thisUser = Forms!frmMain!ctlHiddenUser
    myDate = Date
    
    strSQL = "INSERT INTO tblJournal (journalType, analystID, concaction, recordDate) " & _
             "VALUES (1,'" & thisUser & "', '" & concAction & "', 15-10-2004);"
    DoCmd.RunSQL (strSQL)

Exit_cmdSaveClose_Click:
    Exit Sub

Err_cmdSaveClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveClose_Click

produces the date 01/01/1900 even though I have actually hard coded today's date into it. I reached this stage after both the Date and Now functions throw an error connecting to a method or property of the OLE object. If I go like this:

strSQL = "INSERT INTO tblJournal (journalType, analystID, concaction, recordDate) " & _
             "VALUES (1,'" & thisUser & "', '" & concAction & "', '" & Date "');"

I get the connection error. If I don't include the concatenated ' either side of the Date, it enters 1900 (or perhaps the other way round).

Anyway, it doesn't work. The field is formatted to datetime. What am I doing wrong?

Nick

 

[KenHigg]

When you get something like 01/01/1900, it sometimes means the system is doing some math like '10 minus 15 minus 2004' when you use 10-15-2004.

Maybe you need do place quotes or pound signs around it?

kh

 

[nickellis]

Nope. Thanks though.

Tried this

myDate = Day(Now) & "/" & Month(Now) & "/" & Year(Now)

and then

...cAction & "', " & myDate & ");"

This returns 01/01/1900. If I do the same but with - signs it returns 12th July 1894, bizarrely. If I wrap it in '' it just errors out as described above (which I guess is because I am trying to insert a string into a datetime field)

It's all very frustrating, but I appreciate your help.

Thanks

Nick

 

[KenHigg]

I'm kind of stabbing in the dark here, but what about:

"VALUES (1,'" & thisUser & "', '" & concAction & "', '#15-10-2004#');"

or

"VALUES (1,'" & thisUser & "', '" & concAction & "', '15-10-2004');"

???
kh

 

[Rich]

Have you tried & Date() ? and DoMenuItem has been redundant for years, replace it with
DoCmd.RunCommand acCmdSaveRecord

 

[ghudson]

If you are just trying to insert todays date in the table field and it is for a new record then I would set the Default Value for that table field to...

=Date()

You can then set the format to whatever you want like...

MM/DD/YYYY

If you only want the date. =Now() will give you the date and time.

 

[smercer]

Can I just state the obvious? Try checking the system clock, as the system may have been reset and so will the time (and Date).

Hope this helped.

 

[nickellis]

Many thanks to all for the help here. I am very grateful.

The solution was the deafult value on the field, and why I didn't think of that I don't know!

Since I am running an adp to SQL2000, the default was in fact CURRENT_TIMESTAMP, which dumps in both date and time, which is probably more useful in the long run

Thanks again

Nick