update date to Access DB

[kyon]

Hi, I have this sql statement in the VB application as follow:
Dim sql As String
Set rsForm = Nothing
Set rsForm = New ADODB.Recordset

sql = " update StaffItem" & _
" set Measure_Date = '" & "'" & _
" where Cust_Code = '" & modVariable.custCode & "'" & _
" and Branch_Code = '916'" & _
" and Staff_Code = '24'" & _
" and (isnull(DN_No) or DN_No = '')"

' rsForm.Open sql, cnnForm, adOpenDynamic, adLockOptimistic, adCmdText
cnnForm.Execute sql

When I try to execute, it said wrong type. I think the problem is in the Measure_Date format. I don't know how to set the date in the access db to as '' or null. My access db date format is "dd/mm/yyyy". What I should to? Thank you very much!

kyon

 

[AndyWatt]

If you're using a date put in between "#"s and format the date in mm/dd/yyyy format:

dim strX as String
strX = Format(Date, "mm/dd/yyyy")
conX.Execute "UPDATE SomeTable SET DateField = #" & strX & "#"

If Cust_Code, Branch_Code and Staff_Code are numeric they should not be enclosed in apostrophes. To check if something is NULL in SQL you should use

AND (DN_No IS NULL OR DN_No = '')

Andy
"Logic is invincible because in order to combat logic it is necessary to use logic." -- Pierre Boutroux
"A computer program does what you tell it to do, not what you want it to do." -- Greer's Third Law

 

[CCLINT]

Using #" & strX & "#" is correct, and the reason for the error message.
But it may not be the only problem with this sql statement.

>strX = Format(Date, "mm/dd/yyyy&quot

will not always work, or always return correct results if the date format in the control panel's reginal settings is set to dd/mm/yyyy (or any non-US format)

Format("01/06/2003", "mm/dd/yyyy&quot for 1 June 2003, will still return 1 June 2003 if the system is set to dd/mm/yyyy, and therefore will be interpeted by JET as 6 January 2003. The results will be wrong based on the date criteria.

If the date is 30/01/2003 (30 Jan 2003) then of course everything will work because JET is smart enough to figure out that there is no such thing as a 30th month, so this figure must be the 30th day.

Use:

strX = Format(Date, "mm\/dd\/yyyy&quot
or
strX = Format(Date, "yyyy-MM-dd&quot

This makes sure the the date, which is inputed by the user in the same format as set in the Date region settings, is formated to US format, or the ISO standard date format, which can be then correctly interpeted.

>My access db date format is "dd/mm/yyyy".

The ACCESS db date is not stored in any particular date format, regardless of date format set in the control panel, or in MS ACCESS, but is stored as a numeric representation of the date, which, when displayed to the user, is then converted to the format set in the Windows Regional Settings Short Date Format, or the format set in the Db Field properties.