Silly question on query criteria...

[Guest]

Hello,
I feel dumb for posting this,but I have tried pretty much everything I know
right now (which didn't take too long).

I have a query that uses a calculated field from one of the other fields in
the table.
It works fine, however when I put in a criteria in the criteria field, say:
<=[Enter date:] I get only one record back.

Here's the working SQL without the criteria:
SELECT tblstudents.[Employee Number], tblstudents.Last, tblstudents.First,
tblstudents.FingerprintIssued, DateAdd("yyyy",6,[FingerprintIssued]) AS
[Expiry Date], tblstudents.FingerprintStatus, tblstudents.Type
FROM tblstudents
WHERE (((tblstudents.Type) Like "*" & "DSE" & "*"))
ORDER BY tblstudents.Last;

Thanks,

John.

 

[Guest]

The levels of my ignorance are devastating.

Thanks everyone.

 

[John Spencer (MVP)]

OK, I'm lost here. Did you solve your problem?

If not, what are you applying the criteria against - Expiry Date?

SELECT tblstudents.[Employee Number], tblstudents.Last, tblstudents.First,
tblstudents.FingerprintIssued, DateAdd("yyyy",6,[FingerprintIssued]) AS
[Expiry Date], tblstudents.FingerprintStatus, tblstudents.Type
FROM tblstudents
WHERE (((tblstudents.Type) Like "*DSE*"))
AND DateAdd("yyyy",6,[FingerprintIssued]) <= [Enter Date]
ORDER BY tblstudents.Last;

It might be faster to use

SELECT tblstudents.[Employee Number], tblstudents.Last, tblstudents.First,
tblstudents.FingerprintIssued, DateAdd("yyyy",6,[FingerprintIssued]) AS
[Expiry Date], tblstudents.FingerprintStatus, tblstudents.Type
FROM tblstudents
WHERE (((tblstudents.Type) Like "*DSE*"))
AND [FingerprintIssued] <= DateAdd("yyyy",-6,[Enter Date])
ORDER BY tblstudents.Last;

The levels of my ignorance are devastating.

Thanks everyone.

Hello,
I feel dumb for posting this,but I have tried pretty much everything I know
right now (which didn't take too long).

I have a query that uses a calculated field from one of the other fields in
the table.
It works fine, however when I put in a criteria in the criteria field, say:
<=[Enter date:] I get only one record back.

Here's the working SQL without the criteria:
SELECT tblstudents.[Employee Number], tblstudents.Last, tblstudents.First,
tblstudents.FingerprintIssued, DateAdd("yyyy",6,[FingerprintIssued]) AS
[Expiry Date], tblstudents.FingerprintStatus, tblstudents.Type
FROM tblstudents
WHERE (((tblstudents.Type) Like "*" & "DSE" & "*"))
ORDER BY tblstudents.Last;

Thanks,

John.

 

[Guest]

Hey John,

Thanks for your reply...yes I was able to sort it out.
I simply put <date() under the calculated field criteria...it gave me what I
was looking for. Funndy, when I put <[Enter date:] there, I didn't get the
right solutions.
In any case, I'm glad it worked. I will try your solution also.

Thanks for your help...I can hear you now...you have much to learn, young
padwan...

Have a good night,

John.

OK, I'm lost here. Did you solve your problem?

If not, what are you applying the criteria against - Expiry Date?

SELECT tblstudents.[Employee Number], tblstudents.Last, tblstudents.First,
tblstudents.FingerprintIssued, DateAdd("yyyy",6,[FingerprintIssued]) AS
[Expiry Date], tblstudents.FingerprintStatus, tblstudents.Type
FROM tblstudents
WHERE (((tblstudents.Type) Like "*DSE*"))
AND DateAdd("yyyy",6,[FingerprintIssued]) <= [Enter Date]
ORDER BY tblstudents.Last;

It might be faster to use

SELECT tblstudents.[Employee Number], tblstudents.Last, tblstudents.First,
tblstudents.FingerprintIssued, DateAdd("yyyy",6,[FingerprintIssued]) AS
[Expiry Date], tblstudents.FingerprintStatus, tblstudents.Type
FROM tblstudents
WHERE (((tblstudents.Type) Like "*DSE*"))
AND [FingerprintIssued] <= DateAdd("yyyy",-6,[Enter Date])
ORDER BY tblstudents.Last;

The levels of my ignorance are devastating.

Thanks everyone.

Hello,
I feel dumb for posting this,but I have tried pretty much everything I know
right now (which didn't take too long).

I have a query that uses a calculated field from one of the other fields in
the table.
It works fine, however when I put in a criteria in the criteria field, say:
<=[Enter date:] I get only one record back.

Here's the working SQL without the criteria:
SELECT tblstudents.[Employee Number], tblstudents.Last, tblstudents.First,
tblstudents.FingerprintIssued, DateAdd("yyyy",6,[FingerprintIssued]) AS
[Expiry Date], tblstudents.FingerprintStatus, tblstudents.Type
FROM tblstudents
WHERE (((tblstudents.Type) Like "*" & "DSE" & "*"))
ORDER BY tblstudents.Last;

Thanks,

John.