UK Date problems

[J-F]

I'm trying to open a report using a date field taken from a form to build the sqltxt. The date on the form and in the table is in UK format (dd/mm/yyyy). I have read that access, when in vba, reads it in US format (mm/dd/yyyy). What can I do to the function below to convert the date from UK to US for the code and then Back to UK for the displayed report?

Private Sub Report_Open(Cancel As Integer)

Dim db As Database
Dim rec As Recordset
Dim ReportDate As Date
Dim sqlTxt As String

Set db = CurrentDb
ReportDate = [Forms]![MainEntryForm]![Duty_Date] - ([Forms]![MainEntryForm]![DutyCycle] - 1)
sqlTxt = "SELECT * FROM Duties "
sqlTxt = sqlTxt & "WHERE Duties.Duty_Date >= #" & ReportDate & "# "
sqlTxt = sqlTxt & "AND Duties.Duty_Date <= (#" & ReportDate & "# + 7) "
sqlTxt = sqlTxt & "Order By Duties.Duty_Date;"
Set rec = db.OpenRecordset("Duties", sqlTxt)

Do While Not rec.EOF
'Do things
rec.MoveNext
Loop

rec.Close

End Sub

 

[Rich]

Why can't you just use a query with Between And ?

 

[J-F]

I need to do it this way because the information has to be viewed in columns as opposed to rows. Once I have the recordset open, I would move through the records and feed the fields into collumns on the report.

 

[Jack Cowley]

This article will solve your problem.

http://support.microsoft.com/default.aspx?scid=kb;en-us;q210069

 

[SC]

J-F,

If you use the following wherever your variable ReportDate is used in the SQL string it should work.
#" & Format(ReportDate, "mm/dd/yyyy") & "#

You shouldn't have to do anything to the data for your report as it is in UK format in the table.

Wasn't it nice of Microsoft to make it difficult for us in the UK.

Cheers,

SC.

 

[J-F]

Thanks SC. I've actually done it the way Microsoft suggested but your way would have been so much easier. Thanks again.

J-F