Question mm/dd/yyyy to dd/mm/yyyy

[Mohamedamy]

i have a form search when type date as 2/1/2018
it show result for 1/2/2018
i try change format
the format changed but same result
when type 2/1/2018 dd/mm/yyyy show 2/1/2018 mm/dd/yyyy
i'm confused what can i do

 

[Cronk]

If your Windows region settings are default dd/mm/yyyy then if your date variable is 31/1/2018 ie yourDate=#31/1/2018#, then your search should be something like
... WHERE [myDate] = #" & format(yourDate,"mm/dd/yyyy") & "#"

 

[Mohamedamy]

this code work good
If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "[great].[begin] >= " & GetDateFilter(Me.OpenedDateFrom)
ElseIf Nz(Me.OpenedDateFrom) <> "" Then
strError = cInvalidDateError
End If
____________________________
Function GetDateFilter(dtDate As Date) As String
' Date filters must be in MM/DD/YYYY format
GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY hh:mm:ss AM/PM") & "#"
End Function


but i need repair this code to be real result

If Len(Me.OpenedDateFrom & vbNullString) Then
If Len(strSQLWhere) = 0 Then
strSQLWhere = "WHERE "
End If

If Len(Me.OpenedDateTo & vbNullString) Then
strSQLWhere = strSQLWhere & "[begin] Between #" & Me.OpenedDateFrom & "# AND #" & Me.OpenedDateTo & "#"
Else
strSQLWhere = strSQLWhere & "[begin] >= #" & Me.OpenedDateFrom & "#"
End If

strSQLWhere = strSQLWhere & strJoin

End If

 

[Cronk]

What's wrong with your code? I notice you have not used your function GetDateFilter

 

[Mohamedamy]

i mean i want second code as first code to show real date
but now second code when i press 1/2/2018 it show result 2/1/2018

 

[Cronk]

I don't understand what is 'first code', 'second code', 'real date'. I don't know whether you are reading 1/2/2018 as 1 Feb or 2 Jan.

I thought you had the answer in post #2

 

[Mark_]

Dates are stored as a number. How they are formatted is based on the user settings normally. This means if you type 1 Feb 18 ACCESS stores the value 43132. If you change the format of the entry field to DDMMYY and enter 010218 you would also get the number 43132.

What is very important though is that you pass SQL string "#MM/DD/YYYY#" for dates so that the SQL engine can understand the passed date. So long as you save your date in a Date/Time your only worry is setting the format for controls to local preference.

Now if you are storing dates in something OTHER than a Date/Time, you would be better served using the proper type of variable.

 

[Mark_]

@Pat,

Is it my imagination or are a lot of people posting about dates recently?