date error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have the following code and have tried several variations to get the
correct syntax, but to no avail. The section that has the problem is where
the date variable is, because when I take it out, the code works fine. I
tried putting the date into a variable and then using the variable but then
get other errors.

sqlstmt = "SELECT Sum(Storage.Quantity) AS SumOfQuantity " _
& "FROM Storage WHERE ((Storage.Item='" & Me![cboItem] & "' AND " _
& "((Storage.Type)='Input') AND ((Storage.myDate)>= #' &
Me![userDate]& '#)));"
Set Totals = CurrentDb.OpenRecordset(sqlstmt)

Putting double quotes by itself in the middle of the line also gives errors,
hence the single quote by the date field.

I am using Access 2003, XP Pro, SP2 and wrote the function in vba

Any assistance much appreciated.

CathyZ
 
Have you tried it this way? I thinks this is correct.

sqlstmt = "SELECT Sum(Storage.Quantity) AS SumOfQuantity " _
& "FROM Storage WHERE ((Storage.Item='" & Me![cboItem] & "' AND " _
& "((Storage.Type)='Input') AND ((Storage.myDate)>= #" &
Me![userDate] & "#)));"
 
sqlstmt = "SELECT Sum(Storage.Quantity) AS SumOfQuantity " _
& "FROM Storage WHERE ((Storage.Item='" & Me![cboItem] & "'
AND " _ & "((Storage.Type)='Input') AND ((Storage.myDate)>= #'
&
Me![userDate]& '#)));"
Click to expand...

jetDateFormat = " \#yyyy\-mm\-dd\# "

sqlStmt = "SELECT etc etc " & _
"WHERE myDate >=" & Format(me!userDate, jetDateFormat) & _
"etc etc"

There is a moutain of postings here and elsewhere on the internet about
how to format dates in SQL --- essentially trusting to VBA's built-in
date-to-text conversion will not work because it's regionally-sensitive
and SQL is not. You Have To Use The International Or USAian format and
not european or australian or indonesian or russian or arabic or
venezualan or anything else.

Hope that helps


Tim F
 
Back
Top