Include column in query?

[mikelev]

I have a query that looks up checks returned.

check #
Date Returned (criteria = DATE RETURNED <> 0)
Amount
Amount collected


I would like to prompt the user "Include collected checks"
A YES response will include checks where AMOUNT = AMOUNT COLLECTED.
A NO response will return all checks where AMOUNT is greater than Amount collected. Excluding checks that have a collection amount = or greater than the Amount.


Many thanks in advance

 

[Frink]

Hallo,

The way to do this would be to put the expression in the WHERE clause, using a Parameter. Something like
[Amount=Amount Collected] like '[YyTt1-]*'

I didn't understand what you wanted for the NO option as you state:
NO to return records WHERE Amount>AmountCollected AND Amount < AmountCollected
which makes no sense

A better way may be to use something like:
MsgBox('Amount=Amount Collected?',vbYesNo)=vbYes
but you may have to replace the vbYesNo and vbYes with their numerical equivalents as detailed in the MsgBox help.

- Frink

 

[lupins46]

Change the table and field names to your own

SELECT *
FROM checks
WHERE (((DateReturned) Is Not Null) AND ((Amount)=[amountcollected]) AND (([include yes no])="Yes")) OR (((DateReturned) Is Not Null) AND ((Amount)>[amountcollected]) AND (([include yes no])="No"));

 

[PHV]

SELECT * FROM tblChecks
WHERE [Date Returned] Is Not Null AND
( ([Include collected checks]="YES" And Amount=[Amount collected])
Or ([Include collected checks]<>"YES" And Amount>[Amount collected]))

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