Calendar problem for British user

[saylindara]

I have added calendars to my reports to select date ranges because it was supposed to be a way of solving the USA/British date problem (so the Access tip said). Unfortunately the dates I select on the calendar give me USA results on the report. Deep despair. Is there anything I can do about this? Please bear in mind that I'm new to Access and a simpleton. I've read Allen Browne's international dates advice but I'm not sure what goes where.

[RuralGuy]

Why not show us what code you have created and maybe we'll be able to offer some suggestions.

[saylindara]

On the unboun form there is a FromDate combobox , a ToDate combobox, a FindName combobox and a command button to open the report. They date boxes are comboboxes rather than text boxes so people know they have to click on them.

The MouseDown Event on cboFromDate (and similar for cboToDate) is:

Ocx.Calendar.Visible = True
ocxCalendar.SetFocus
If Not IsNull (cboFromDate) Then
ocxCalendar.Value = cboFromDate (the instructions said this should be cboFromDate.Value but it didn’t work like this)
Else
ocxCalendar.Value = Date
End If

The Calendar:

The Visible property is set to No.
The Click Event:
cboFromDate = ocxCalendar.Value
cboFromDate.Set Focus
ocxCalendar.Visible = False

The command button Click Event:

Refresh
If IsNull (cboFindName) Then
DoCmd.OpenReport “r_EventsAttended”, acViewPreview,, & “ AND “ & “EventDate >= #” & Me.cboFromDate & “ # AND “ & “EventDate <= #” & Me.cboToDate & “#”
Else
DoCmd.OpenReport “r_EventsAttended”, acViewPreview,, “EmployeeID = “ & Me.cboFindName & “AND” & “EventDate >= #” & Me.cboFromDate & “ # AND “ & “EventDate <= #” & Me.cboToDate & “#”
End If

[RuralGuy]

You should really Copy and Paste any code so we don't have to wade through the typo's. Maybe you want to use this Microsoft function on your [EventDate] field in the query.

[saylindara]

Thank you for your reply.

I created a new module as below:

Function MakeUSDate(x As Variant)
If Not IsDate(x) Then Exit Function
MakeUSDate = "#" & Month(x) & "/" & Day(x) & "/" & Year(x) & "#"
End Function

It results in the date being shown as #mm/dd/yyy#. Unfortunately all I get then is a blank report using the following command click event.

Private Sub Command4_Click()
Refresh
If IsNull(cboFindName) Then
DoCmd.OpenReport "r_EventsAttended", acViewPreview, , "USEventDate >= #" & Me.cboFromDate.Value & " # AND " & "USEventDate <= #" & Me.cboToDate.Value & "#"
Else
DoCmd.OpenReport "r_EventsAttended", acViewPreview, , "EmployeeID = " & Me.cboFindName & " AND " & "USEventDate >= #" & Me.cboFromDate.Value & " # AND " & "USEventDate <= #" & Me.cboToDate.Value & "#"
End If
End Sub

I suppose using this method the USEventDate field could never match the combobox date. Any further help would be appreciated.

[RuralGuy]

Where did you use the new function?

[saylindara]

I used it in the report query

[RuralGuy]

Could you post the SQL for the report query please?

[saylindara]

I hope you can make sense of this amateur effort.

SELECT DISTINCT t_EmployeeType.EmployeeType, t_Employees.EmployeeID, q_EmployeeEventLocation.Concat2, q_EmployeeEventLocation.EventDay, q_EventsAll.EventName, q_EmployeeEventLocation.EventDate, q_EmployeeEventLocation.LocationName, q_EventsAll.EventDurationDays, q_EmployeeEventLocation.DurationDepartment, q_EmployeeEventLocation.DurationOwnTime, IIf([DurationDepartment]=0,[EventDurationDays],[DurationDepartment]) AS ActualDuration, Format([EventDate],"mmmm") AS EventMonth, MakeUSDate([EventDate]) AS USEventDate
FROM t_EmployeeType INNER JOIN (((q_EmployeeJobTitleTeam INNER JOIN (q_EmployeeEventLocation INNER JOIN q_EventsAll ON q_EmployeeEventLocation.EventID = q_EventsAll.EventID) ON q_EmployeeJobTitleTeam.Concat1 = q_EmployeeEventLocation.Concat1) INNER JOIN t_Team ON q_EmployeeJobTitleTeam.TeamID = t_Team.TeamID) INNER JOIN t_Employees ON q_EmployeeJobTitleTeam.EmployeeID = t_Employees.EmployeeID) ON t_EmployeeType.EmployeeTypeID = t_Employees.EmployeeTypeID
WHERE (((t_EmployeeType.EmployeeType) Like "pod*"))
ORDER BY q_EmployeeEventLocation.EventDate;

[RuralGuy]

Have you looked at the query in datasheet view to see what your dates look like?

[saylindara]

The dates look the same in datasheet view, ie in the format #mm/dd/yyyy# including the hash marks.

[saylindara]

I take it that as I have received no further responses that no one on this forum can assist me further. I would like to thank Rural Guy for his kind help. I am much further forward than I was before.

[RuralGuy]

I haven't given up yet, I just could not think of another suggestion. I wonder if it has something to do with Vista?

[RuralGuy]

I've heard that using the Format() function with a "Short Date" works.

[saylindara]

I'll try Format() at work tomorrow where I'm using Access 2003 but it didn't work on the copy of the database I've got at home on Vista. I also tried using that with long date but that didn't work either. It may well be I suppose that the amateurish, makeshift and mend way I set up the database (my one and only) is a contributory factor.

I'm sure if you don't know then neither does anyone else but if I don't have any luck tomorrow I'll try posting on another forum just in case.

Thanks again for your kind and patient help.