SQL Date Problem

[Captain_Drudge]

Hi,
I have report being called from a visual basic interface.
Now teh mechanism for it runs fine and as predicted.
The report has the following underlying SQL query controlling it:

SELECT [tblSwimmer].[FirstName], [tblSwimmer].[LastName],
[tblSwimmer].[DateOfBirth], [tblSwimmer].[Sex], [tblSwimmer].[Active],
[tlkpSquad].[SquadName], [Swim].[Distance], [Swim].[SwimMins],
[Swim].[SwimSecs], [Swim].[SwimDate], [Type].[SwimType], [Type].[TypeID],
([SwimMins]*60)+[SwimSecs] AS [Calculated Time], [Stroke].[StrokeName],
[Stroke].[StrokeID]
FROM tblSwimmer, tlkpSquad, Swim, Type, Stroke
WHERE ((([Stroke].[StrokeID])=[Swim].[StrokeID]) And
(([tblSwimmer].[SquadID])=[tlkpSquad].[SquadID]) And
(([tblSwimmer].[SwimmerID])=[Swim].[SwimmerID]) And
(([Type].[TypeID])=[Swim].[SwimType]))
ORDER BY (([SwimMins]*60)+[SwimSecs]);

This brings up loads of qualifying rows.

The visual basic interface passes a filter the typically is of the type:
(Stroke.StrokeID = 1) AND (Swim.Distance = 100) AND (Sex = -1) AND (Active =
0) AND (TypeID <> 3) AND (DateOfBirth BETWEEN #07/03/1992# AND #06/03/1994#)

This is a the filter created by the visual basic to narrow the report to
show data by a set of criteria chosen on screen.

The problem is that by my understanding this particular filter, it should
not return the row that contains the date of birth 09/03/1994. Is there
some SQL query Access issue I dont know about here...
Any suggestions gratefully received as this is driving me nuts...
Many Thanks
R Dutton

[Douglas J. Steele]

From your address, I assume you're in the UK. What date format do you use:
dd/mm/yyyy or mm/dd/yyyy? (in other words, is 09/03/1994 the 9th of March or
the 3rd of September?) Access uses mm/dd/yyyy regardless of what your
regional settings are.

You might find it worthwhile to read Allen Browne's "International Dates in
Access" at http://members.iinet.net.au/~allenbrowne/ser-36.html or what I
have at
http://members.rogers.com/douglas.j....artAccess.html
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Captain_Drudge" <rd**********@blueyonder.co.uk> wrote in message
news:L7****************@doctor.cableinet.net...

Hi,
I have report being called from a visual basic interface.
Now teh mechanism for it runs fine and as predicted.
The report has the following underlying SQL query controlling it:

SELECT [tblSwimmer].[FirstName], [tblSwimmer].[LastName],
[tblSwimmer].[DateOfBirth], [tblSwimmer].[Sex], [tblSwimmer].[Active],
[tlkpSquad].[SquadName], [Swim].[Distance], [Swim].[SwimMins],
[Swim].[SwimSecs], [Swim].[SwimDate], [Type].[SwimType], [Type].[TypeID],
([SwimMins]*60)+[SwimSecs] AS [Calculated Time], [Stroke].[StrokeName],
[Stroke].[StrokeID]
FROM tblSwimmer, tlkpSquad, Swim, Type, Stroke
WHERE ((([Stroke].[StrokeID])=[Swim].[StrokeID]) And
(([tblSwimmer].[SquadID])=[tlkpSquad].[SquadID]) And
(([tblSwimmer].[SwimmerID])=[Swim].[SwimmerID]) And
(([Type].[TypeID])=[Swim].[SwimType]))
ORDER BY (([SwimMins]*60)+[SwimSecs]);

This brings up loads of qualifying rows.

The visual basic interface passes a filter the typically is of the type:
(Stroke.StrokeID = 1) AND (Swim.Distance = 100) AND (Sex = -1) AND (Active = 0) AND (TypeID <> 3) AND (DateOfBirth BETWEEN #07/03/1992# AND #06/03/1994#)
This is a the filter created by the visual basic to narrow the report to
show data by a set of criteria chosen on screen.

The problem is that by my understanding this particular filter, it should
not return the row that contains the date of birth 09/03/1994. Is there
some SQL query Access issue I dont know about here...
Any suggestions gratefully received as this is driving me nuts...
Many Thanks
R Dutton