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? :confused:

Any help would be GREATLY appreciated ~ Thank you!

the syntax is incorrect. The following compares the form field, FromDate since I'm assuming that's what you want. You cannot reliably compare numeric data (including dates) using "Like". The Like operator is intended for use with strings. It should work agains the form field since form fields are variants.

Active Date = Between [Forms]![ACT_RPT_OPT]![FromDate] And [Forms]![ACT_RPT_OPT]![ToDate] Or [Forms]![ACT_RPT_OPT]![FromDate] Like "*"

Thank you for your input, however, it does not work.

If I enter nothing in the FromDate and ToDate, it returns no records (I would like it to return all). If I enter dates in these fields, it returns all records, not just those within the requested date range.

I appreciate your help! I am willing to try anything right now!

For these 3

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] & "*"

You should only need Is Not Null in the query criteria

For Active Date your criteria of Between [Forms]![ACT_RPT_OPT]![FromDate] And [Forms]![ACT_RPT_OPT]![ToDate] Or Like "*" is suggesting you want ALL the records retrieved (becauze of Or Like "*") that have met the first 3 criteria.

I am not clear on what you want retrieved.

Could post a couple of example rows such as

Name____SSecrityNo____FromDate____ToDate

with some various entries and then which records you would want retrieved.

Pat Hatman is correct about having your Like "*" as Or [Forms]![ACT_RPT_OPT]![FromDate] Like "*"

A similar thing is needed in Macro conditions. That is, you cant have [Field1] Like "A" and Like "B". It has to be [Field1] Lile "A" and [Field1] Like "B"

Mike

Hi, thanks for your post!

To be more clear, I have a form that allows the user to select All or Specify for each of the criteria (SSN, First Name, Last Name and/or Active Date Range).

Some examples of selections and return would be:
Example #1
User selects All for SSN, All for First Name, "Smith" for Last Name, and All for Active Date Range. Then it should show all records with Last Name beginning Smith.

Example #2
Or the user could select All for SSN, All for First Name, All for Last Name and Active Date Range between 1/1/04 and 4/1/04. Then it should show all the records that have dates between 1/1/04 and 4/1/04.

Example #3
Or the user could select All for SSN, ''Jen" for First Name, All for Last Name, and Active Date Range from 5/1/04 to 5/20/04. Then it should show all of the records with a First Name begining with Jen AND have Active Dates between 5/1/04 and 5/20/04.

Example #4
Or the user could select All for all four fields and All records in the underlying table would display.

I have tried many different criteria expressions for the four different fields the ' Like [Forms]![ACT_RPT_OPT]![Field Name] & "*" ' for the SSN, Last Name and First Name work great. It is the Active Date criteria I am having trouble with. I have also tried "IIf " statements in the Active Date criteria and it returns "expression to complex" error. I tried Pat's suggestion and it returned records specified in the other fields great, but it returned no records when I added an Active Date range selection.

I really appreciate everyone posting, I am sure the answer it out there...I hope... :(

Firstly, my comment on my previous post about you only need Is Not Null on the first criteria was wrong. I did not notice you had & Like "*". I was thinkling you had or Like "*"


To be more clear, I have a form that allows the user to select All or Specify for each of the criteria (SSN, First Name, Last Name and/or Active Date Range).

Some examples of selections and return would be:
Example #1
User selects All for SSN, All for First Name, "Smith" for Last Name, and All for Active Date Range. Then it should show all records with Last Name beginning Smith.

That would just require "Simth*" since you are saying ALL records with Last Name beginning with Smith

Example #2
Or the user could select All for SSN, All for First Name, All for Last Name and Active Date Range between 1/1/04 and 4/1/04. Then it should show all the records that have dates between 1/1/04 and 4/1/04.

That will be >= Your form reference and <= Your form reference.

Example #3
Or the user could select All for SSN, ''Jen" for First Name, All for Last Name, and Active Date Range from 5/1/04 to 5/20/04. Then it should show all of the records with a First Name begining with Jen AND have Active Dates between 5/1/04 and 5/20/04.

"Jen*" and >= Your form reference and <= Your form reference

Example #4
Or the user could select All for all four fields and All records in the underlying table would display.

Well that one does not any criteria and therein lies the problem (I Think?) You appear to be trying to do everything from one criteria but the problem is that your critetia are including opposites in the sense that one part of the criteria is selecting records on some basis but the other part is selecting all records.

If I am reading you right you will need to have a label or command button you click on to run 2 or 3 versions of your query. That is you might have 3 queries that all identical except for your criteria.

Alternatively you would need to go something like an IIf function in your criteria. I sometimes find the best way to approach some of thse type of problems is to create a new field (or fields) with an IIf function and then apply criteria to that field or fields.

With this sort of thing

[Forms]![ACT_RPT_OPT]![Field Name] & "*"

You need to have [Forms]![ACT_RPT_OPT]![Field Name] And Form/field reference Like "*"

Mike

Hi, I have tried several things with adding additional conditional statments to my query, however, have yet to make it work. I will revisit it tomorrow.

Just wanted to say thanks for all of the input. I will get it to work eventually!

If you post your exact SQL we'll have a better shot at figuring out what is wrong with your syntax.

Maybe this'll help... hope so!

Beware the function call in the query, beware the code behind the frmSearchMain form, and good luck!

You can put the criteria for the date field in a new column in the query grid like this:-

Field: [ActiveDate] Between [Forms]![ACT_RPT_OPT]![FromDate] And [Forms]![ACT_RPT_OPT]![ToDate] Or [Forms]![ACT_RPT_OPT]![FromDate] Is Null

Show: uncheck

Criteria: <>False

See the query and the form in the database attached.

You can enter a date range on the form or just leave the FromDate and ToDate blank. When FromDate is left blank, all records will be returned.


Note: I do not use spaces in field names.