Query/data parameters

[sk88]

I have a report and export function which is based on date parameters. The field name called "date". However, I added a new field called "followup-date" because we would like to track customers that are returning back.
Now, my problem is when I go to Export by date parameters or generate the report by certain date eg. July1st to July 28^th, it only pulls up the record based on “DATE” field.

Is it possible for the date parameters to look up july1st to july 28^th under ‘date’ field as well as ‘followup-date’ and pull up those records that are meet the date parameters?

The reason I have to do this is because some customers do not come back for followup so I have to look under 'date' field too.
while some customers can have more than one followup.

Sorry, I know this is confusing. I hope someone will be able to help.

Thank you

[uncletreetrunk]

Can you just set criteria for "followup-date" the same as you did for "DATE"?

ie. For "DATE" criteria you had: BETWEEN [MinDate] AND [MaxDate]

then "followup-date" would be the same in the criteria box?

Or do you want to look up only one? Like if you have a followup-date then search date range else use DATE

[jameslarsen]

If I understand correctly, you want to use the latest followup-date if it exists, but if it doesn't, to use the regular date instead? I would use an Nz() function, for example: Nz(followup-date, date). Nz will use the first value if it is available, but if it's null, it will use the second value. Also, do you want a full visit history for a given customer? Or just the latest date? If you want to track all followup dates for a customer, you will need a separate table to store them, then just do a max() function for a given customer. You could then use the result of the max() function along with the Nz() function for your date filter. Let me know if that's what you wanted to accomplish, or if you need to filter the two dates in a different way.

James A. Larsen
Database Architect, Data Analyst, and Business Intelligence Specialist
james.larsen42@gmail.com