Problem with query, criteria, & Like "*" &...&"*"

[Richard M]

I have a query to show all data for a set of many criterias. As long as I do not compound them like ( 1., and 2. and so on), the below query (1.) works great.

This works great:
1.

like "*" &  [forms]![frmfindTransaction]![text32] & "*"

I want to mix the above with the following so I can find data in many different ways.

2.

Like "*"<="& [forms]![frmfindTransaction]![cbofromMonth]" & "*" And Like "*"<="& [forms]![frmfindTransaction]![cbotoMonth]" & "*"

This did not work. So I tried:

3.

Like "*" & <= [forms]![frmfindTransaction]![cbofromMonth& "*"  And Like "*" &<= [forms]![frmfindTransaction]![cbotoMonth] & "*"

This did not work. So I tried:

4.

Like "*" & <= [forms]![frmfindTransaction]![cbofromMonth And <= [forms]![frmfindTransaction]![cbotoMonth] & "*"

[/PHP]

This did not work.

Thanks for the help

 

[Beetle]

You appear to attempting to do two opposing things. You're asking your application if value x is both Like value y And Less Than Or Equal To value y. Logically, that's not possible. Also, you appear to want to return a vlaue that is Less Than Or Equal To the FromMonth And Less Than Or Equal To the ToMonth, which , logically, would only return values that are Less Than Or Equal To the FromMonth (because those are the only values that would meet both criteria).

So, maybe you could clarify what it is you are after here.

 

[Galaxiom]

Using Like to find parts of dates is inefficient. Using the Day(), Month() and Year() functions is also very inefficient because the funtions must be applied to every record.

Dates should be searched by building start and end dates then applying them as Between criteria to return dates within the range. Thistechnique only applies functions to generate the criteria then uses the index to find the records. It can easily be 100 times faster than the other techniques.

 

[Richard M]

Thanks,

I started to feel that "Like" did not work with numbers or dates. Thanks for clearing that up for me. I use the "Like" for the reason you said plus when there is no data in the text box, the query would show all the data.

My problem started when I wanted to find a range in months and range in years. When there was no months selected, I would get all months and the same with years.

I am looking at doing it another way and seems to be working.

Thanks again for clearing the cobwebs out of my brain.

Richard