Problem with query, criteria, & Like "*" &...&"*" (1 Viewer)

[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.

 

[Pat Hartman]

Like is used when you want to search for a record and you have only part of the search value. Typically this would be a name search. Bear in mind that "Like" searches cannot use an index and so can be very slow on a large table. Never use "Like" when you have a complete value as you would when selecting from a combo or listbox. "Like" is ONLY used with text values. It CANNOT be used with dates or numberic values because they are NOT strings. If you want to compare part of a date, you'll need to use one of the date functions to get what you want out of the date - Day(), Month(), Year(), or Format(yourdate,?????). When comparing numbers, use the relational operators - =, >, <, <>.

Changing relational operators in queries is not possible unless you are building SQL strings in code. So if you have a query - Select * from tbl1 Where dt1 < [SomeDate]; "[SomeDate]" is an argument and can provide a value at Runtime. The "<" is part of the structure of the query and cannot be changed at Runtime.

Becareful with dates. It looks like you are trying to use only month in your criteria but you'll need to use year also once the tables contain data for multiple years.

 

[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