Criteria for Date AND Time. 1

[air0jmb]

For each record, I'm trying to get all previous records from the last 20 days where Tail and ATA are same. This SQL works but if there's two records for the same Date, I get 4 records which I understand. I have another field [ExTime] but I don't know how to set the criteria to return records where t47Ex_1.ExTime is < t47Ex.ExTime only if the Dates are same.

SELECT t47Ex.ExID, t47Ex_1.ExID AS ExHistID, t47Ex_1.ExDate AS ExHistDate
FROM t47Ex LEFT JOIN t47Ex AS t47Ex_1 ON (t47Ex.Tail = t47Ex_1.Tail) AND (t47Ex.ATA = t47Ex_1.ATA)
WHERE (((t47Ex_1.ExID)<>[t47Ex]![ExID]) AND ((t47Ex_1.ExDate)>=[t47Ex]![ExDate]-20 And (t47Ex_1.ExDate)<=[t47Ex]![ExDate]) AND ((t47Ex.ExDate)>=#1/1/2006#))
ORDER BY t47Ex.ExID, t47Ex_1.ExID;

Thanks for any help!!

Mike

 

[AlexCuse]

I take it when there is just one on the date, time will be equal?

If so, try this

AND t47Ex_1.ExTime <= t47Ex.ExTime

If this doesn't work, you will need to play with the where clause a bit.

In the future, when you post code, try using these tags around it so it's formatted as mine is above.

[ignore]

type some code here...

[/ignore]

Hope this helps,

ALex

Ignorance of certain subjects is a great part of wisdom

 

[lespaul]

[off topic]
Alex,
How do you get both the TGML and the NOT TGML in the same post? I've never been able to figure that out!
Les[/off topic]

 

[Golom]

Leslie

Check out the [ignore][ignore] ... [/ignore][/ignore] Tags

 

[air0jmb]

Alex, thanks for the tip.

I want every record from last 20 days that occurred before the current record. If current record Date is 1/20/06 and Time is 13:40, I want all records between 1/1/06 and 13:39 on 1/20/06.

If I set criteria to say t47Ex_1.ExTime < t47Ex.ExTime, then it will exclude all records from every date that has a time >= 13:40.

Mike

 

[air0jmb]

I think I need an IIf statement that says IIf t47Ex_1.ExDate = t47Ex.ExDate then t47Ex_1.ExTime < t47Ex.ExTime. But, nothing I try works.

Thanks,

Mike

 

[PHV]

First, why having TWO fields for a SINGLE DateTime value ?
Anyway, you may try this:
WHERE t47Ex_1.ExID<>t47Ex.ExID
AND ((t47Ex_1.ExDate+t47Ex_1.ExTime) Between (t47Ex.ExDate-20) And (t47Ex.ExDate+t47Ex.ExTime))
AND t47Ex.ExDate>=#1/1/2006#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[air0jmb]

PHV,

The data which I import into my database already has date and time as separate fields. Would you recommend concatenating them during the import update query? If so, I assume I'd use the CDte function.

Or, what about creating a new field in my query that concatenates the date and time, and then just have my criteria < current records new date/time field?

I'm asking so I know how to deal with this the next 500 times I need to. Regardless, your sql seems to do the trick though I'm having trouble understanding it. What is the (Date+Time)? Is that concatenating the two fields?

Thanks!

Mike

 

[PHV]

Is that concatenating the two fields
In fact it adds the time value to the date value.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886