Format dates in code

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?

where is this table defined...a connection string?
MIR.ReportData

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')

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 ).

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.

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!

Thanks, glad to help.