Problem inserting date into field 1

[cruford]

I have a simple dropdown box with account status's (shipped, cancelled, etc..). Once a status is selected I have some code fire to update dates/fields in other tables. My problem is the "Date" expression is writing a time to my table..see below:


Private Sub Status_AfterUpdate()
Dim sSQL$, iCustID As Double
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
iCustID = Val(CustomerID)
Select Case UCase(Status)
Case "SHIPPED"
sSQL = "UPDATE ztblPhysicianInfo SET ShippedDate = " & Date & " WHERE CustomerID = " & iCustID
Case "INACTIVE"
sSQL = "UPDATE ztblRespInfo SET TRACK = 0, FINISHED = -1 WHERE CustomerID = " & iCustID
End Select
MsgBox sSQL
rs.Open sSQL, CurrentProject.Connection
Form_Current
End Sub


The msgbox shows SQL statement with the correct date displayed (ie. 7/16/2004). But, once the statement writes it is writing this value to my date field: 12:00:19 AM. It doesn't matter what time or day it is that is the only value it writes to the table.

Now I can replace the Date variable and hard code a date in the update statement and that works fine. What is going on?

Database Info:
MS Access 2k
ShippedDate field has a data type of Date/Time

 

[smedvid]

Can't you simply wrap the Date in a Format().... Format(Date,"mm/dd/yyyy"). Perhaps that would work? htwh,

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[cruford]

I tried that just now with no luck. This is strange..I can display the date() variable in a message box, I even displayed my entire sSQL variable and it reads out the date. I also can push the date() variable to a field on my form with no trouble. As soon as I try to write the date() variable to the table it writes 12:00:19 AM to my table.

I have tried Format(), cdate(), now().. I have even went so far as to add this code:

Dim sSQL$, iCustID As Double, intPos As Integer, intLength As Integer
Dim sDate as Date
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
iCustID = Val(CustomerID)
intPos = InStr(Now(), " ")
intLength = Len(Now())
sDate = Left(Now(), (intLength - intPos))
MsgBox sDate

Which return just the date in a msgbox..Again when it writes to the access table it writes 12:00:19 AM.

 

[smedvid]

U did check to see if there are any formats on the field in that table correct? make sure u compact/repair. I've had bizzare behavior before that compact/repair magically fixed.

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[PHV]

And something like this ?
... SET ShippedDate = " & CLng(Date()) & " ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cruford]

PHV,

Thank you, that worked great.

Just out of curiosity why in Access would you have to do this to write date() to a date/time field?