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; "

 

[Pat Hartman]

SQL requires that text dates be in US format mdy rather than the dmy format used by much of the rest of the world. There are many different ways to get around this problem so here's one:

(((Format(tblCResults.RDate,"Medium Date")) = Format( # " & rptDate & " #, "Medium Date")))

What this does is to convert the date/time data type of the table field to the text format of the parameter field. You could of course convert the parameter field to a date/time data type or a text string in mdy sequence as you please.

BTW, any field order works for an = comparison as long as both strings are the same. However if you want to use other operators such as Between or > and <, both dates will either have to be converted to date/time data types so that Access can do a numerical comparison or the field components need to be in ymd order for a text comparison to work.

 

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

 

[Pat Hartman]

Try getting rid of the extra spaces:

(((Format(tblCResults.RDate,"Medium Date")) = Format(#" & rptDate & "#, "Medium Date")))

Also try:

(((Format(tblCResults.RDate,"Medium Date")) = Format(" & rptDate & ", "Medium Date")))

 

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