Strange date field problem

[rodmc]

Hi

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.

 

[ghudson]

Did you set the format property of the text box to dd/mm/yyyy?

 

[rodmc]

Did you set the format property of the text box to dd/mm/yyyy?

do you mean like this,

Format(CDate(Me.txtStartDate), "dd/mm/yyyy")

The strange thing is about this as I said is that the EndDate box works perfectly with the exact same properties, if you get my meaning

The format in the text box properties is set to short date

 

[ghudson]

do you mean like this,

The format in the text box properties is set to short date

That is what I was asking.

Try copying the end text box that is working the way you want, rename it for the start date text box. Access sometimes does strange things.

 

[rodmc]

yeah, gave that a shot, still formatting in US format

a real puzzler!

 

[ChrisO]

It may be to do with how you are using the two dates.

If you are using them in a string then Access might be converting an ambiguous date to US format.

Can you post how you are using both dates?

 

[rodmc]

It may be to do with how you are using the two dates.

If you are using them in a string then Access might be converting an ambiguous date to US format.

Can you post how you are using both dates?

Im using them to filter periods of time using some 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

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

 

[ChrisO]

The problem with dates in Access is the formatting.

http://allenbrowne.com/ser-36.html

so…
varWhere = varWhere & "[DatRec] >= #" & Me.txtStartDate & "# AND "

would become…
varWhere = varWhere & "[DatRec] >= " & SQLDate(Me.txtStartDate) & " AND "

 

[rodmc]

Hi Chris,

tried your solution but was having the same problem so I tried a workaround with this 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.

 

[Brianwarnock]

The immediate window in VBA shows the query which looks ok


Code:
WHERE [DatRec] >= #01/04/2010# AND [DatRec] <= #30/04/2010#

The startdate will be interpreted as 4th of January, the end date cannot be switched to us format so will be 30th April.

In Sql and VBA hardcoded dates default to US format if able, you must use the Alllen Browne technique pointed to by Chriso if using short date format.

Brian

 

[rodmc]

Thanks Brian

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?

 

[Brianwarnock]

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

'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.

Brian

 

[rodmc]

so do I need to declare SQLDate as a Variable/variant?

 

[Brianwarnock]

If using the code in your function as I suggested you are not using SQLdate.

but the SQLStartDate and SQLEndDAte will be strings due to the use of Format. You could drfine them as Variant .

Brian

 

[rodmc]

Now Im totally lost!

you'll have to forgive me, my background is in Network Engineering

 

[ChrisO]

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.

 

[rodmc]

Hi Chris, Ive given it a go, but getting the "Expected Variable or Procedure not module" error

Here's how my code looks now

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.

Thanks for you're patience guys

 

[ChrisO]

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.

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: -

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.

See how that goes and post back any problems.

Chris.

 

[rodmc]

Sorry, getting a syntax error now

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##'

 

[ChrisO]

Sorry but are you even reading what has been written?