Format dates in code

c9k9h
c9k9h used Ask the Experts™
on
I was a little too quick to close my last question.  I have capricorn's code:
strSQL = "SELECT * FROM MIR.ReportData WHERE ReportingDate BETWEEN date() and DateSerial(Year(Date()), (Month(Date()) - 13), 1)
But I'm getting an 'ODBC call failed' error.  I think it's around the formatting of the date.  This is pulling from Teradata and the format has to be yyyy/mm/dd.  I tried this, but am still getting the error.
strSQL = "SELECT * FROM MIR.ReportData WHERE ReportingDate BETWEEN Format(Date(), 'yyyy/mm/dd')and Format(DateSerial(Year(Date()), (Month(Date()) - 13), 1), 'yyyy/mm/dd')"
 Any ideas?  Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006

Commented:
Build the string with dates outside


strSQL = "SELECT * FROM MIR.ReportData WHERE ReportingDate >= " & format(now(),"YYYYMMDD") & " AND ReportingDate <= " & format(DateSerial(Year(Now()), (Month(Now()) - 13, 1),"YYYYMMDD")



Is this a normal Access query being run on linked tables?  Or is it a passthrough query?
Top Expert 2006

Commented:
where is this table defined...a connection string?
MIR.ReportData
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Teradata appears to store dates as a string. If this is a passthrough:

strSQL="SELECT * FROM MIR.ReportData WHERE ReportingDate BETWEEN " & format$(DateAdd("m",-13,Date(),'yyyy\/mm\/dd') & " AND " & format$(Date(),'yyyy\/mm\/dd')


Author

Commented:
rockiroads:  I put your code in (just had to add a close paren after the -13).  

strSQL = "SELECT * FROM So_Mir_d.GSS_Report_Data_Weekly WHERE FromDate >= " & Format(Now(), "YYYYMMDD") & " AND FromDate <= " & Format(DateSerial(Year(Now()), (Month(Now()) - 13), 1), "YYYYMMDD")

I can step through the code without getting any errors - but I don't get any records back.  If I comment out everything from the WHERE and beyond, it runs fine and brings back all the records.  So it has someting to do with the dates.  

peter57:  It is a pass-through query.

jeff:  Here's the code - should look familiar to you.  (I'm using a different table name at this point.):
Private Sub cmdUpdateRecords_Click()

Dim db As DAO.Database
Dim qd As QueryDef
Dim strSQL As String
Set db = CurrentDb()

On Error Resume Next
'DoCmd.DeleteObject acQuery, "qry_GSS_Data_Weekly"
'DoCmd.DeleteObject acTable, "tbl_GSS_Data_Weekly"
On Error GoTo 0

strSQL = "SELECT * FROM So_Mir_d.GSS_Report_Data_Weekly WHERE FromDate >= " & Format(Now(), "YYYYMMDD") & " AND FromDate <= " & Format(DateSerial(Year(Now()), (Month(Now()) - 13), 1), "YYYYMMDD")

Set qd = db.CreateQueryDef("qry_GSS_Data_Weekly")
qd.Connect = "ODBC;DSN=TDDEV;MODE=SHARE;DBALIAS=TDDEV;"
qd.Properties("odbcTimeout") = 120
qd.SQL = strSQL
qd.ReturnsRecords = True
CurrentDb.Execute "qry_Build_GSS_Data_Weekly"

End Sub

Ray:  I get a compile error when I put in your code.  It stops on the single quote before the 'yyyy (Expected: expression).  I put in double quotes to see if it made a difference, then it stops at the end of the statement and gives a different compile error:  Expected: list separator or ).
Commented:
Sorry a few errors:

strSQL="SELECT * FROM MIR.ReportData WHERE ReportingDate BETWEEN '" & format$(DateAdd("m",-13,Date()),"yyyy/mm/dd") & "' AND '" & format$(Date(),"yyyy/mm/dd") & "'"

Author

Commented:
rockiroads:  I meant to give the result I get in the immediate window (I changed the signs):
? strSQL
SELECT * FROM So_Mir_d.GSS_Report_Data_Weekly WHERE FromDate <= 20070531 AND FromDate >= 20060401
I tried putting in '/' to see if that makes a difference, but no, I still get no records back:
? strSQL
SELECT * FROM So_Mir_d.GSS_Report_Data_Weekly WHERE FromDate <= 2007/05/31 AND FromDate >=2006/04/01
Looks beautiful to me!?!  Don't know what it's problem is.

Author

Commented:
Ray:  You got it!  You're right - it needed the tic marks.  I was so busy writing the response to rockiroads, I didn't see your comment until I hit the send button.   Thanks all!

Commented:
Thanks, glad to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial