Problem with Date Parameter in SQL statement

[LDG1234]

Hi guys, I have this really frustrating problem and I have spent almost 5 hours trying to sort it out. Hope this makes sense...

Here is the SQL:

Public Function DuplicationCheck()

Dim db As Database
Dim rst As Recordset
Dim strSQL As String


Dim datQueryDate As Date

Set db = CurrentDb

datQueryDate = [Forms]![Frm_AddNewClaim]![DateOfLoss]

strSQL = "SELECT Tbl_Claim.ClaimReferenceNumber, Tbl_Claim.ClaimCategory, Tbl_Customer.CustomerName, Tbl_Claim.DateOfLoss, Tbl_Claim.ValueOfClaim, Tbl_Claim.SiteName, Tbl_Claim.ClaimStatus" _
& " FROM Tbl_Customer INNER JOIN Tbl_Claim ON Tbl_Customer.CustomerID = Tbl_Claim.CustomerID" _
& " WHERE (((Tbl_Customer.CustomerName)= '" & [Forms]![Frm_AddNewClaim]![CustomerName] & "') AND ((Tbl_Claim.DateOfLoss)= #" & datQueryDate & "#) AND " _
& "((Tbl_Claim.ValueOfClaim)=" & [Forms]![Frm_AddNewClaim]![ValueOfClaim] & &quot)"

Set rst = db.OpenRecordset(strSQL)

If rst.RecordCount > 0 Then

MsgBox "There are duplicates" & vbCrLf & rst("ClaimReferenceNumber&quot


Else

Exit Function

End If

rst.Close

Set db = Nothing

End Function


........okay here is the issue

The field [Forms]![Frm_AddNewClaim]![DateOfLoss] is a Date Format dd/mm/yy.

It would seem that run the code the date format changes to mm/dd/yy. To counteract this I changed the format of the variable datQuery to mm/dd/yy to counteract this.

This seemed to work fine for dates such as 01/05/01 and 02/08/01 but as soon at the day of the month was greater than 12 it failed to return values when I know that it should have done. This is presumabley because somewhere in the system between the 2 date formats it decides that there can't be more than 12 months.

This is really causing me a headache now and I have tried everything that I know to rectify this.

If anyone could offer any assistance I would be extremely grateful.

Thanks in advance




Lloyd Gozzett
Process Developer

 

[Guest_imported]

I assume the field DateofLoss is entered through user interaction (typing in or a calendar) or something to that effect.

After obtaining that value and before sending the SQL statement, convert the format of the date to ANSI standard, which is format yyyy-mm-dd.

Use a function along the lines of

datQueryDate = format(DateofLoss, "yyyy-mm-dd&quot

Note if the code is outside the form or in a module you'll need to reference the actual field by name ie:

[Forms]![Frm_AddNewClaim]![DateOfLoss]

By passing dates in ANSI standard format, SQL Server will always interpret them correctly, thus removing the need to worry about internationalization or local machine settings when dealing with dates.

Cheers

Chris Cheatle