access query date criteria issue

[tungy]

Hi,

I'm trying to run reports which basically looks between 2 dates, the user enter the dates from a form and then it takes it and places this in a query to look for the results which then gets outputted to a report.

The problem i have is that when i pass the SQL query to the query table it changes the date to US format rather than what i put in which is UK format, the following is moy code:

' Pointer to error handler
On Error GoTo cmdOK_Click_err
' Declare variables
Dim db As Database
Dim qdf As QueryDef
Dim strDueDate As String
Dim strSQL As String
' Identify the database and assign it to the variable
Set db = CurrentDb
' Check for the existence of the query, create it if not found,
' and assign it to the variable
If Not QueryExists("qryReportQuery") Then
Set qdf = db.CreateQueryDef("qryReportQuery")
Else
Set qdf = db.QueryDefs("qryReportQuery")
End If
' Get the values from the combo boxes


If (Me.txtMinDue.Value = "") And (Me.txtMaxDue.Value = "") Then
strDueDate = " Like '*' "
ElseIf (Me.txtMinDue.Value = "") And Not IsNull(Me.txtMaxDue.Value) Then
strDueDate = " <= " & Format(Me.txtMaxDue, "\#dd\/mm\/yyyy\#") & " "
ElseIf Not IsNull(Me.txtMinDue.Value) And (Me.txtMaxDue.Value = "") Then
strDueDate = " >= " & Format(Me.txtMinDue, "\#dd\/mm\/yyyy\#") & " "
ElseIf Not IsNull(Me.txtMinDue.Value) And Not IsNull(Me.txtMaxDue.Value) Then
strDueDate = " between " & Format(Me.txtMinDue, "\#dd\/mm\/yyyy\#") & " and " & Format(Me.txtMaxDue, "\#dd\/mm\/yyyy\#") & " "
End If
MsgBox (strDueDate)


' Build the SQL string
strSQL = "SELECT customer_details.* " & _
"FROM customer_details " & _
"WHERE customer_details.due_date_late" & strDueDate & _
"ORDER BY customer_details.reference;"
' Pass the SQL string to the query
qdf.SQL = strSQL
' Turn off screen updating
DoCmd.Echo False

' Open the query
DoCmd.OpenReport "report", acViewReport
cmdOK_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdOK_Click_err:
' Handle errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit

you can see i have put a msgbox to see what it actually outputs which is the correct format but when it actually goes into the criteria section it changes from uk to us date so for example 01/04/2012 changes to 04/01/2012.

Does anyone know what i need to do to keep the formatting?

Many thanks in advance

 

[MStef]

Look at "DemoDateReport2A2000.mdb (attachment, zip).
I think it can help you.
Look at table, query, form (VBA), report.
Open Form1 and try.

 

[tungy]

thanks i will take a look, i think i solved the issue. By changing the formatting to US after the person enters it, although the user inputs in uk format backend uses the US date format in the query but somewhere along the line it brings back results based on UK dates.

so if anyone wants to know what i did i just changed the following:

Format(Me.txtMaxDue, "\#dd\/mm\/yyyy\#")

to the following

Format(Me.txtMaxDue, "\#mm\/dd\/yyyy\#")