Hi all ~ help please!
I have four fields in a query which will allow a user to specify or just show all.
Field Names and Current Criteria:
Soc Security Num = Like [Forms]![ACT_RPT_OPT]![SSN_Select] & "*"
First Name = Like [Forms]![ACT_RPT_OPT]![FN_Select] & "*"
Last Name = Like [Forms]![ACT_RPT_OPT]![LN_Select] & "*"
Active Date = Between [Forms]![ACT_RPT_OPT]![FromDate] And [Forms]![ACT_RPT_OPT]![ToDate] Or Like "*"
Problem:
First, the Active Date criteria shows all records whether dates are entered or not. I need it to show records with Active Dates between the entered dates OR show all when no dates are entered.
Second, I need this to be able to work with the other criterion. For example if I enter Michael in the First Name field and then 4/1/04 in the From Date and 5/1/04 in the To Date fields, I need it to show all the Michaels between those Active Dates.
Any ideas?
Any help would be GREATLY appreciated ~ Thank you!
I have four fields in a query which will allow a user to specify or just show all.
Field Names and Current Criteria:
Soc Security Num = Like [Forms]![ACT_RPT_OPT]![SSN_Select] & "*"
First Name = Like [Forms]![ACT_RPT_OPT]![FN_Select] & "*"
Last Name = Like [Forms]![ACT_RPT_OPT]![LN_Select] & "*"
Active Date = Between [Forms]![ACT_RPT_OPT]![FromDate] And [Forms]![ACT_RPT_OPT]![ToDate] Or Like "*"
Problem:
First, the Active Date criteria shows all records whether dates are entered or not. I need it to show records with Active Dates between the entered dates OR show all when no dates are entered.
Second, I need this to be able to work with the other criterion. For example if I enter Michael in the First Name field and then 4/1/04 in the From Date and 5/1/04 in the To Date fields, I need it to show all the Michaels between those Active Dates.
Any ideas?

Any help would be GREATLY appreciated ~ Thank you!