I have 2 unbound check boxes, one for a start date and one for an end date.
The problem I have found is that one seems to be formatting time in the US format (mm/dd/yyyy) and one in the UK format (dd/mm/yyyy), any ideas on how to have both in UK format.
It should be noted when I enter the dates in the date fields they both appear in UK format.
Im using them to filter periods of time using some code
Code:
If Me.txtStartDate > "" Then
varWhere = varWhere & "[DatRec] >= #" & Me.txtStartDate & "# AND "
End If
If Me.txtEndDate > "" Then
varWhere = varWhere & "[DatRec] <= #" & Me.txtEndDate & "# AND "
End If
Ive also put in the follwing to try and force the format
Format(CDate(Me.txtStartDate), "dd/mm/yyyy")
The immediate window in VBA shows the query which looks ok
Code:
WHERE [DatRec] >= #01/04/2010# AND [DatRec] <= #30/04/2010#
The results are flawed unless I use the US format in the first text box
tried your solution but was having the same problem so I tried a workaround with this code
Code:
Private Function Buildfilter() As Variant
Dim varWhere As Variant
Dim MJMStart As Date
Dim MJMEnd As Date
MJMStart = Me.txtStartDate
MJMEnd = Me.txtEndDate
varWhere = Null
If Me.txtClientID > "" Then
varWhere = varWhere & "[ClientID] LIKE """ & Me.txtClientID & "*"" AND "
End If
If Me.txtGender > "" Then
varWhere = varWhere & "[Gender] LIKE """ & Me.txtGender & "*"" AND "
End If
If Me.cmbRefSrc > "" Then
varWhere = varWhere & "[RefSrc] LIKE """ & Me.cmbRefSrc & "*"" AND "
End If
If Me.txtPostCode > "" Then
varWhere = varWhere & "[Postcode] LIKE """ & Me.txtPostCode & "*"" AND "
End If
'If Me.txtStartDate > "" Then
' varWhere = varWhere & "[Client]![DatRec] >= #" & Format(CDate(Me.txtStartDate), "dd/mm/yyyy") & "# AND "
'End If
If Me.txtStartDate > "" Then
varWhere = varWhere & "[DatRec] >= #" & Format(MJMStart, "dd mmm yyyy") & "# AND "
End If
If Me.txtEndDate > "" Then
varWhere = varWhere & "[DatRec] <= #" & Format(MJMEnd, "dd mmm yyyy") & "# AND "
End If
Debug.Print
Now this code works but I must have date values in the form (cant leave the date fields blank) or I get a type mismatch error. Ive been trying to dig up some code to assign default dates to the text boxes but Im coming up with nothing.
forgive me for being thick, but Allen Browns SQL date function code (the wrapper), is this code inserted into the same form or do I need to create a module for his code?
The time I saw it used, I was helping a poster debug a different problem, the poster used it inline but with a thanks to allen something like
Code:
'The following code was acquired from Allen Browne
If IsDate(StartDate) Then
If DateValue(StartDate) = StartDate Then
SQLStartDate = Format$(startDate, "\#mm\/dd\/yyyy\#")
Else
SQLstartDate = Format$(startDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
repeat if block for enddate. Of course if no time is involved the If can be modified to cut out that part.
SQLDate is a Function created by Allen Browne. Go to his site and copy his function into a standard module.
When that is done use the syntax I supplied.
If you pass the function a Null you will get back a zero length string and the SQL statement will fail.
Therefore you should test for both dates being valid dates before trying to use them in the where clause.
Hi Chris, Ive given it a go, but getting the "Expected Variable or Procedure not module" error
Here's how my code looks now
Code:
Private Function Buildfilter() As Variant
Dim varWhere As Variant
varWhere = Null
If Me.txtStartDate > "" Then
varWhere = varWhere & "[DatRec] >= #" & SQLDate(Me.txtStartDate) & "# AND "
End If
If Me.txtEndDate > "" Then
varWhere = varWhere & "[DatRec] <= #" & SQLDate(Me.txtEndDate) & "# AND "
End If
I have pasted A.Brown's Codes into a module and called it SQLDate.
In your first post you said: -
>>I have 2 unbound check boxes, one for a start date and one for an end date.<<
Are they really check boxes or are they text boxes? I’ll assume they are text boxes.
I’ll also assume the string is being used as a WHERE clause and as a FILTER.
Code:
Private Function Buildfilter() As String
Dim strWhere As String
strWhere = " WHERE 1 = 1"
If IsDate(Me.txtStartDate) Then
strWhere = strWhere & " AND [DatRec] >= " & SQLDate(Me.txtStartDate)
End If
If IsDate(Me.txtEndDate) Then
strWhere = strWhere & " AND [DatRec] <= " & SQLDate(Me.txtEndDate)
End If
Buildfilter = strWhere
End Function
This then leads to a problem with Allen Browne’s code…strange as that may seem.
If we enter a ‘Date’ in unbound text box it is not a Date, it’s a String.
We can use the IsDate Function to test to see if it can be converted to a Date, but it does not convert it to a Date, it remains a String.
When the String is passed to the SQLDate Function it is received as a Variant.
The IsDate Function tests the Variant and finds that it could be converted to a Date but does not convert it.
The Fix Function then raises a Type Mismatch error while trying to Fix a String.
I don’t like modifying his code but will suggest the following addition: -
Code:
Function SQLDate(varDate As Variant) As String
[color=green]'Purpose: Return a delimited string in the date format used natively by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time component,
' or a date/time format if it does.
'Author: Allen Browne. allen@allenbrowne.com, June 2006.
' Modified by ChrisO to force conversion, April 2010.[/color]
If IsDate(varDate) Then
[color=green]' Required to force conversion before the Fix.[/color]
varDate = CDate(varDate)
If Fix(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function
If Allen wants to he can incorporate the addition but only after he tests it.
So back to the thread…
Break down the WHERE clause to its minimum and test each addition one at a time.
In the case of the date section a text box, which cannot be converted to a Date, should leave the WHERE condition open-ended.
This should mean that if the start date is invalid then select all records prior to or equal to the end date.
Similarly, if the end date is invalid then select all records equal to or after the start date. If both are invalid, select all records.
Also, change the name of the module to mdlSQLDate.
Private Function Buildfilter() As Variant
Dim varWhere As Variant
varWhere = Null
If Me.txtStartDate > "" Then
varWhere = varWhere & "[DatRec] >= #" & SQLDate(Me.txtStartDate) & "# AND "
End If
If Me.txtEndDate > "" Then
varWhere = varWhere & "[DatRec] <= #" & SQLDate(Me.txtEndDate) & "# AND "
End If
It is reporting that there is a missing operator in the query expression '[DateRec] >= ##01/04/2010## AND [DateRec] <= ##30/04/2010##'