Using CDate - what did I do wrong?

[Ivkosky]

Hi,


I tried to use CDate for the change of time format from US: MM/DD/YYYY to DD/MM/YYYY in one form, where I state start date and end date in order to get all the records between these two days. I don't know, what I did wrong but it's still not working properly and I get records also out of the predefined interval So I suppose the CDate conversion doesn't work for me

If Me.Starting_date_of_occurrence.Value > "" And Me.Finishing_date_of_occurrence.Value > "" Then
    If Not (WhereString = "WHERE ") Then
        WhereString = WhereString & " AND "
    End If
    start_date = CDate(Me.Starting_date_of_occurrence.Value)
    end_date = CDate(Me.Finishing_date_of_occurrence.Value)
    WhereString = WhereString & "(((MORs.[Date of occurrence])>=#" & start_date & "#) AND ((MORs.[Date of occurrence])<=#" & end_date & "#))"
End If

Any help would be highly appreciated! Thank you very much!


Ivan

 

[DCrake]

Take out the .Value code from your syntax and retry.

 

[Ivkosky]

Thank you for your prompt answer! I put away all the .Value, but nothing changed. I still get the records with dates before as well as after the interval

 

[DCrake]

The sql may still be Americanising the dates. Do a Debug.Print Wherestring after you have generated the code to look at the date range it has created.

 

[Ivkosky]

Thanks a lot, I did a Debug.Print and found out, that my real problem was, that it actually didnt change the format into US (from UK) - so other way round. Problem solved now. Thank you very much!

 

[gemma-the-husky]

i dont understand this

cdate is just a function to change something that isnt a date into a date.

a date is a date

3/12/09 (US is March 12th)

3/12/09 (UK is 3rd December)
12/3/09 (UK is March 12th)

but this is just a display/formatting thing

the date is still stored in the base datetime storage variable, which counts days from a given starting point.
ie 3/12/09 (US) and 12/3/09 (Uk) are the same date - 12th March
3/12/09 (Uk) and 12/3/09 (US) is a differrent date - 3rd December

but a real catch is that where the date is not ambiguous, access resolves it itself so
3/17/09 or 17/3/09 are always 17th March 2009, irrespective of the regional setting

------------------------

displaying a date differently just needs

format(mydate,"long date") - displays as 12th March 2009, according to regional format

but

format(mydate,"dd/mm/yyyy") - coerces to a particular format

----------
be careful when using sql though , because vba/access in many case uses US dates, rather than obeying the regional format - so you have ot be aware of this possibility