US/UK Date formats in RecordSource SQL query

[Nyanko]

Hi,

I'm adapting the search form example posted here : http://www.access-programmers.co.uk/forums/showthread.php?t=99353

Instead of a Min/Max age, I'm attempting to use dates and have run into a bit of an issue. This code uses the form inputs to build a recordsource query and I'm wondering if there is a US/UK dates issue that I am running into.

Private Sub btn_Search_Click()
    
    ' Update the record source
    Me.frm_ClientSearchSub.Form.RecordSource = "SELECT * FROM Qry_ClientSearch " & BuildFilter
    
    ' Requery the subform
    Me.frm_ClientSearchSub.Requery
End Sub



Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varStatus As Variant
    Dim varItem As Variant
    Dim intIndex As Integer

    varWhere = Null  ' Main filter
    varStatus = Null  ' Subfilter used for Status'
    
    ' Check for LIKE First Name
    If Me.txtProjectCode > "" Then
        varWhere = varWhere & "[ProjectCode] LIKE """ & Me.txtProjectCode & "*"" AND "
    End If
   
' Check Dates Greater Than
    If Me.txtGreaterThan > "" Then
        varWhere = varWhere & "[ScheduledDate] > #" & Me.txtGreaterThan & "# AND "
    End If

    ' Check Dates Less Than
    If Me.txtLessThan > "" Then
        varWhere = varWhere & "[ScheduledDate] < #" & Me.txtLessThan & "# AND "
    End If

    ' Check for Third Paty
    If Me.cmbThirdParty <> "<ALL>" Then
        varWhere = varWhere & "[ToBeInvoiced] = """ & Me.cmbThirdParty & """ AND "
    End If

    ' Check for Account
    If Me.cmbAccount <> "<ALL>" Then
        varWhere = varWhere & "[AccountName] = """ & Me.cmbAccount & """ AND "
    End If

    ' Check for Status in multiselect list
    For Each varItem In Me.lstStatus.ItemsSelected
        varStatus = varStatus & "[Status] = """ & Me.lstStatus.ItemData(varItem) & """ OR "
    Next

    ' Check for Transaction Type in multiselect list
    For Each varItem In Me.lstType.ItemsSelected
        varStatus = varStatus & "[Type] = """ & Me.lstType.ItemData(varItem) & """ OR "
    Next

    'BUILD THE VARIABLE, ADJUSTING ANY FILTERS AS NEEDED

    ' Test to see if we have subfilter for Status...
    If IsNull(varStatus) Then
        ' do nothing
    Else
        ' strip off last "OR" in the filter
        If Right(varStatus, 4) = " OR " Then
            varStatus = Left(varStatus, Len(varStatus) - 4)
        End If

        ' Add some parentheses around the subfilter
        varWhere = varWhere & "( " & varStatus & " )"
    End If

    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere

        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    'Passes the SQL Select Statement back to the BuildFilter variable
    BuildFilter = varWhere
    
End Function

The date options will not work for the 01-12 of the month i.e. 05/01/2016 (5th January 2016) so I'm guessing there is somewhere that is converting the dates back to US format.

Is there some way to force the code above to keep the UK format when running the recordsource query ?
I've checked the table, query, sub form and main form formats and they are all dd/mm/yyyy

Help !!!!

 

[Ranman256]

dates are the same no matter how you format it , BUT inputting data, the text box may not know what you entered and confuse month with day.
you may try either:
unbound boxes for each : month,day,year , then assemble them all together into an invisible box with US format. and use that box.
or
a single unbound text box in UK format, and the same invisible text box to reformat to US format Access understands
or
a single bound text box with the text box formatted to UK and/or the table formatted to UK.

 

[arnelgp]

yes you are right you have to convert it to US date format:

varWhere = varWhere & "[ScheduledDate] > #" & Format(Me.txtGreaterThan, "mm/dd/yyyy") & "# AND "

do this with all your date criteria.

 

[Nyanko]

Hi,

Thanks for your reply.

I'm using calendar controls on unbound text boxes for the input of txtGreaterThan and txtLessThan to avoid user input error.

I also tried creating txtUSGreaterThan with the control source as =Format([txtGreaterThan],"mm/dd/yyyy") and running the query on that but this still didn't solve the problem.

 

[Nyanko]

yes you are right you have to convert it to US date format:

varWhere = varWhere & "[ScheduledDate] > #" & Format(Me.txtGreaterThan, "mm/dd/yyyy") & "# AND "

do this with all your date criteria.

Wonderful. That did it ! I couldn't work out where I had to change the format !

 

[gemma-the-husky]

it's quite spooky. if you enter a date directly into a constant -

Const mydate = #21/02/2016#

vba immediately changes it to US format.

Const mydate = #02/21/2016#