Need help to compare two date/time fields 2

[tubbers]

I have a little trouble when working with date and/or times in queries and need some help with the following query:

I have two fields FirstDetect and LastDetect) in a table which are date/time types formatted in like so "5/11/2004 4:54:02 PM". I need to run a query which will select the records where the LastDetect field is a time/date earlier than FirstDetect field. Like:

SELECT Location, TagCode, FirstDetect, LastDetect
FROM MasterData
WHERE LastDetect < FirstDetect

So if FirstDetect had a value of 5/19/2004 3:45:58 PM and LastDetect had a value of 5/19/2004 1:15:48 PM, that record would be selected.

Any suggestions on how to go about doing this?

Thanks for your help.

 

[SkipVought]

tubbers,

What's wrong with EXACTLY what you posted...

SELECT Location, TagCode, FirstDetect, LastDetect 
FROM MasterData 
WHERE LastDetect < FirstDetect

???

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[tubbers]

When I try and run the query with the following:

(WHERE LastDetect < FirstDetect)

I get prompted to enter a parameter value for FirstDetect.

Do I need some sort of formatting or something?

 

[lespaul]

I think you need to join back into the same table:

SELECT Location, TagCode, FirstDetect, LastDetect
FROM MasterData M1
INNER JOIN MasterData M2 on M1.Location = M2.Location AND M1.TagCode = M2.TagCode
WHERE M1.LastDetect < M2.FirstDetect

Leslie

 

[SkipVought]

I set up a test table and PASTED in the SQL as posted and ran it sucessfully!

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[tubbers]

Thanks Leslie,

I tried what you suggested and I when I run the query, I get prompted to enter a parameter value for Location.

Any ideas?

 

[SkipVought]

try

SELECT M1.Location, M1.TagCode, M1.FirstDetect, M1.LastDetect 
FROM MasterData M1
INNER JOIN MasterData M2 on M1.Location = M2.Location AND M1.TagCode = M2.TagCode
WHERE M1.LastDetect < M2.FirstDetect

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[SkipVought]

...or even...

SELECT M.Location, M.TagCode, M.FirstDetect, M.LastDetect 
FROM MasterData M
WHERE M.LastDetect < M.FirstDetect

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[lespaul]

I'm with you Skip, I don't know why the original isn't working! You're right, the simple

WHERE LastDetect < FirstDetect

should work!!

tubbers, is there more to the query than you are showing us?

Leslie

 

[SkipVought]

There's something that we don't know.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[tubbers]

Skip,

I used your first suggestion and it worked like a charm. Thanks for your help.

Leslie, thanks to you as well.

I gave you both a star for your help. Saved me a lot of time and teeth grinding.

Cheers.