Find matching fields in different records (1 Viewer)

[Andy Mc]

Hi.
I have an unmatched query based on 2 tables. The query results are:

I now need to filter this to those records where StartTime matches with any Expr1. Expr1 is calculated from StartTime using dateadd function.

For example, in the above, the first 2 records would be ignored because there's no StartTime equal to Expr1.
The first record to be returned would be the 3rd (StartTime = 10:50:00), because its Exp1 matches with StartTime for 5th record (StartTime =11:10:00).
Hope that makes sense.

I think a query solution would be too messy (and all my attempts didn't give the correct results), so I've tried using a module:

, but this also omits some records.

I've searched and searched but I can't find a solution. Any suggestions would be much appreciated.

(PS, I've left the name Expr1 as is for now to highlight it's a calculated field)

 

[Gasman]

I do not see 10:50:00 in any record for Expr1?

 

[theDBguy]

Let's say the name of the above query is Query1, what do you get if you tried something like?

SELECT Q1.* FROM Query1 Q1 INNER JOIN Query1 Q2 ON Q1.StartTime=Q2.Expr1

(untested)

 

[Pat Hartman]

Matching on times is problematic. They are not strings. They are double precision numbers and the decimal places can be numerous so you will be hard pressed to get exact matches since the time is almost always rounded.

If you convert both dates to strings using the Format() function, @theDBguy 's solution will work.

 

[Andy Mc]

Let's say the name of the above query is Query1, what do you get if you tried something like?

SELECT Q1.* FROM Query1 Q1 INNER JOIN Query1 Q2 ON Q1.StartTime=Q2.Expr1

(untested)

Thanks for the suggestion. I tried that initially, but it didn't return any results.

 

[Andy Mc]

Matching on times is problematic. They are not strings. They are double precision numbers and the decimal places can be numerous so you will be hard pressed to get exact matches since the time is almost always rounded.

If you convert both dates to strings using the Format() function, @theDBguy 's solution will work.

Thanks Pat. That's interesting. Perhaps it might be better to convert the time to a string. I'll give that a go

 

[Andy Mc]

I do not see 10:50:00 in any record for Expr1?

Hi Gasman. The match would be on 11:10:00 and the result returned would be 10:50:00

 

[MajP]

Since that expression is based off a floating point number calculation, I would think you need to round or convert to string to ever get a match.

 

[Pat Hartman]

The first record to be returned would be the 3rd (StartTime = 10:50:00), because its Exp1 matches with StartTime for 5th record (StartTime =11:10:00).
Hope that makes sense.

No. The two values are not equal. You have to specify an actual rule, not an example. the first two non matches were also 20 minutes apart. What makes the third record different?

 

[Andy Mc]

Thanks Pat. That's interesting. Perhaps it might be better to convert the time to a string. I'll give that a go

Hi Pat. Thanks again. That worked with a bit of tweeking. I converted the query times to strings and modified the code to:

Much obliged.

 

[Pat Hartman]

You're welcome but that code doesn't do what you said you wanted. Why use a code loop anyway when a query will probably suffice?

 

[Andy Mc]

You're welcome but that code doesn't do what you said you wanted. Why use a code loop anyway when a query will probably suffice?

True, but I'll be developing that for further use later.