Morning All,
‘I have an application where I need to show courses attended for a member for the member’s current training year. So if a member has a training year of 1st November 2003 to 31st October 2004, the app has to show the courses attended during that year.

‘Each member has a record showing the month and day his training year starts

‘I have assigned variables TrMth and TrDay for those integers.

‘To obtain the start date for the member’s training year, I use the following (courtesy Greg Serrano) by ‘initialising the variable dtmStartdate:

dtmStartDate = DateSerial(Year(Date), TrMth, TrDay)

‘then to obtain the end date, I use;

dtmEndDate = DateAdd("yyyy", 1, dtmStartDate - 1)

'I then use a routine to re initialise if appropriate, namely where the date the search is carried out is after ‘31st December in any one year

If dtmStartDate > Date Then
dtmStartDate = DateSerial(Year(Date) - 1, TrMth, TrDay)

dtmEndDate = DateAdd("yyyy", 1, dtmStartDate - 1)
End If

‘This is to ensure that the search always shows the correct year – otherwise a search in early 2004 would show a member’s course starting ist November 2004.

‘The variables are then incorporated into a query to find all the courses between dtmStartdate and dtmEnddate, and the routine works well, save for one problem, which is that dtmStartDate is wrongly formatted. If I run the routine for a member who has not attended any courses for his training year (1st November 2003 to 31st October 2004) the query shows he has attended courses, but is bringing them in from earlier dates. When I examine the query image the following appears in the Course date field:

‘ Between #11/01/2003# And #31/10/2004#
‘instead of
‘Between #01/11/2003# And #31/10/2004#

‘Curiously, the end date is formatted correctly.

‘I imagine this is a formatting problem – any ideas?

The problem appears to be related to the Regional & Language options in Control Panel. Initially I set the short date option to dd/MM/yyyy (in fact I think the default is that format). Customising the setting to dd/MMM/yyy has fixed the problem. Is this a bug in Windows?