Query result with strange missing dates

[palros77]

Hi,

I have a table which has the date of all premiership football matches played in the 02/03 season called [Matches]. It has two fields, [Date] and [Team], with the primary key defined as a combination of these two fields.

I am trying to create a query that for each match will also give me the date of that teams previous match, and this is what I have so far:

SELECT
Matches.Date,
Matches.Team,
DMax("[Date]","Matches","[Team] = ' " & [Team] & " ' AND [Date] < # " & [Date] & " # ") AS [D-1]
FROM Matches;

When I run this query the expression for [D-1] works fine for most of the records and returns a blank for the first matches as expected. However it returns a blank for quite few other records, and returns the wrong date for some.

Can anybody tell me why this doesn't work, as I am baffled by it!?

Cheers,
Paul.

 

[Uncle Gizmo]

Date is a reserved word, I would suggest you change all occurrences of date to teamDate. Try that first.

 

[palros77]

Thanks for that, but it didn't work.

I changed the field name to [GameDate] but have exactly the same results.

 

[namliam]

Perhaps if you post a sample on here, i cannot see anything wrong from this point, depending on the data filled....

Regards

 

[palros77]

I'm still getting nowhere, so here is a copy of the offending database if anyone can help me.

Cheers,
Paul.

 

[namliam]

I found your problem, its the usual date conversion thing.
01-09-2002 allthough we see it as 01 sept 2002 Access will convert it to 09 jan 2002. Using a format will clear your problem
Like so:

SELECT Matches.GameDate, Matches.Team, 
DMax("[GameDate]","Matches","[Team] = '" & [Team] & "' AND  [GameDate] < #" & Format([GameDate],"dd/mmm/yyyy") & "#") AS [D-1]
FROM Matches;

 

[palros77]

Thank you very much, that solved it!

That was something I initially looked into, but thought Access used the date format from the windows settings throughout.

Cheers,
Paul.

 

[namliam]

actually internally access uses the "makers"(M$ -US-) date format (mm/dd/yyyy)

This often runs ppl into trouble and really i should have thought of it straight away. But i knew the second i saw the data (a picture paints a thousand words).

In queries use the US format or something like dd-mmm-yyyy which will allways convert properlike.....

Regards