Date Format Command in SQL

[dynamictiger]

I am trying to pass a calculated date to the SQL below. rptDate is the paramater I am passing. I have tried various arrangemnts of # " & to no avail. I am stuck on this.

Me.RecordSource = "SELECT tblCResults.CID, tblCResults.RDate, " & _
"tblSalutation.Salutation, tblClient.FirstName, tblClient.LastName, " & _
"tblClient.CAddress, tblClient.CSuburb, tblClient.CState, tblClient.CPostCode " & _
"FROM (tblCResults INNER JOIN tblClient ON tblCResults.CID = tblClient.CID) " & _
"INNER JOIN tblSalutation ON tblClient.SalutationID = tblSalutation.SalutationID " & _
"WHERE (((tblCResults.RDate) = Format( # " & rptDate & " #, "Medium Date"))) " & _
"WITH OWNERACCESS OPTION; "

 

[dynamictiger]

Thanks Pat

I tried modifying my Where clause and that caused a syntax error, so I copied and pasted the posted code and that is giving a syntax error. I am still new to SQL so I am sure it is simple, but I can't see what is giving this error. I have broken the statement down line by line and it is only the where line that causes a syntax error.

 

[dynamictiger]

I finally got it to work like:

rptDate = Format(Date()-10,"Medium Date")

"WHERE (tblCResults.RDate)= # " & rptDate & " # "

The date()-10 is going to be dynamic so this is only a testing phase to make the idea work first.

Appreciate your input though, it helped me walk through this.