Like and Between in Forms/Queries

[Denyial]

Hi, absolutely brand new to Access.

I am trying to set up a query by form with multiple criteria.

For the majority of the criteria I'm using the format:

Like [Form]![formName].[txtInputboxName] &"*" .

I copied the Like (...) &"*" from someone else in order to allow for multiple, optional criteria, which does do the trick, however I don't understand why.

I now want to set up a criteria on my query to search for values between two input values to gives the records in the range e.g between 50 and 100

Following the above format, I want to put something that achieves this:

Between Like [Form]![formName].[txtInputboxName1] &"*" AND Like [Form]![formName].[txtInputboxName2] &"*"

with Inputbox1 being 50 and Inputbox2 being 100, however that code doesn't work and I don't know how to get around it as it keeps giving the 'incorrect syntax' error.

Any help would be very much appreciated!!

 

[Ranman256]

LIKE if for a fuzzy search where you use the *

BETWEEN is for getting all values between val1 and val2
you cannot use like here
make the like a separate criteria

 

[Denyial]

So I changed the Criteria to:

Criteria: Between [Forms]![frmProject Query].[txtProjectValue1] And [Forms]![frmProject Query].[txtProjectValue2]
or: Like [Forms]![frmProject Query].[txtProjectValue1]&"*"
or: Like [Forms]![frmProject Query].[txtProjectValue2]&"*"


however I still got an error... What did I do wrong?

 

[namliam]

It doesnt work?

Well obviously your dog must be in need of a walk
After that try wattering the plants
stand on your head for 30 seconds straight
Do the moonwalk for 30 seconds
return and try again, I am sure it will now work...

Try giving us the exact error message or something "real" to go on.

Is this field a number field by chance?

 

[Denyial]

Yeah so on the form there are two input number fields for a price range.

I want these input fields to be optional criteria for the query, so by inputting e.g 50 and 100 I get all the values inbetween.

However with the criteria I posted earlier, it doesn't take the values i inputted in the form, and instead prompts me to put in new parameters, before not giving me the range, but instead returning all records with 50 and 100 in it (e.g 504 or 10045).

What else can I give to help? I'm really struggling!

 

[arnelgp]

Between [Forms]![formName]![txtInputboxName1] AND [Forms]![formName]![txtInputboxName2]

note the s on the [Forms] and the bang(!) symbol.