Query Using Like NZ ( Between , *) 2

[hext2003]

I have a Search form that gathers Parameters to pass to a query.

When the Search button is clicked it opens up a query with the Values from the Form.

Below is what I have in the Criteria for field (YrBuilt) in the Query. I have tested and tested and I keep getting a blank query.


Like nz(([Sales].[YrBuilt])>[Forms]![Search]![YrBltToInput] And ([Sales].[YrBuilt])<[Forms]![Search]![YrBltFromInput],"*")

Or

Like nz(([Sales].[YrBuilt])Between [Forms]![Search]![YrBltToInput] And [Forms]![Search]![YrBltFromInput],"*")

and a few other Similiar attempts

If I change it to

Like nz([Forms]![Search]![YrBltInput,"*")

This works find But I only get a single year I want a Range. What am I doing wrong???

TIA

 

[lespaul]

Why are you using LIKE? LIKE is usually used when you know a substring and want to find others LIKE it....for instance, your inventory system tracks Circuit boards and all circuit boards have an ID field that starts with "CB-", so you have records like:

CB-100
CB-205
CB-316

and to find ALL circuit boards in your inventory you would search for:

WHERE IDField LIKE "CB-*"

so the query would return all three records from above because they all start with "CB-".

Now, the NZ function replaces a field that is NULL with the second argument. So, if you have:

SELECT NZ(SomeField, 'IsNull') FROM TableName

and the value of SomeField is Null then your result set will have "IsNull" for SomeField instead of a blank/null in the record.

Now if you have a field that contains a year (is it really a date field or is it a text field that contains the year?) then you probably want something like this:

SELECT * FROM TableName WHERE YrBuilt BETWEEN  [Forms]![Search]![YrBltToInput] AND [Forms]![Search]![YrBltFromInput]

if you really DO want to use the LIKE function (which again, on a YEAR really doesn't make much sense unless you are looking for all the ones that start with 19 or end with 80), then you need to add the * to EACH of your years.

If it's a date field you'll need the date delimiters (#10/28/2008#).

If you'll provide some information about the fields and what you are trying to do, maybe I can be a little more helpful.

Leslie

Have you met Hardy Heron?

 

[hext2003]

Because I am a newbie and I copied the Like Statement from the City Field which worked and handle What happened if nothing was select from the combo box - I wanted ALL records to be returned instead of No Records.

I have a text box on the [Search] form. That is formatted to General Number with 0 decimal places.

The Year is a field unto itself. It is a number field.

Thanks for the input. I am still learning. Self Taught so it takes a bit. But I am getting better by the day!

 

[hext2003]

GOT IT!!!

I kept tring and eventually got the syntax correct!

Thanks for the Lesson it helped my train of though!


This in the Criteria of the Query for Field YrBuilt did it!
Between nz([Forms]![Search]![YrBltToInput],"*") And nz([Forms]![Search]![YrBltFromInput],"*")

 

[lespaul]

Newbies are allowed! Glad I was able to point you in the right direction!

Leslie

 

[PHV]

As YrBuilt is supposed to be numeric I'd avoid to compare it with "*":
Between Nz([Forms]![Search]![YrBltToInput],0) And Nz([Forms]![Search]![YrBltFromInput],9999)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hext2003]

Thanks PHV That helped I was getting some goofey results with the "*"