Builing a query from selection criteria on a Form

  • Thread starter Thread starter Craig Ferrier
  • Start date Start date
C

Craig Ferrier

I have 3 form fields
FromDate
ToDate
PaymentType

In the query I have fields
Date
Payment Type

In the criteria for the date I have:
Between [Forms]![frmReportDateType]![FromDate] And
[Forms]![frmReportDateType]![ToDate]
this allows me to input a date into the form and view a report based on the
query of all items within the two dates.

I want to go a step further and select payment types, I have a list of
payments types, ie cash, visa, mastercard, check.

I want to be able to select 1 or select all into the query. Any clues on
how to build this.
[Forms]![frmReportDateType]![PaymentType] as criteria within the query
allows me to select 1 item, but there i times I want all items in the list
to be used. (in other word no selection criteria for this field)

i tried this in the query
IIf([Forms]![frmReportDateType]![PaymentType]="","",[Forms]![frmReportDateTy
pe]![PaymentType])

but it didnt work.

Any clues?

Thanks
Craig
 
Try

Like Nz([Forms]![frmReportDateType]![PaymentType],"*")

That will work as long as the field PaymentType ALWAYS has a value. If it is
ever null, the record with null value will not be returned. In that case, try
the following as the criteria if you need all the records returned including
those with null in the paymenttype field:

[Forms]![frmReportDateType]![PaymentType] OR
[Forms]![frmReportDateType]![PaymentType] Is Null

BE WARNED. ACCESS will rearrange this query when you close and save it.
Sometimes the newly structured query will be too complex and will fail.
 
Back
Top