Help with query - want to search on 2/3's of a field.

[MMichaels]

Hello All!

I have a very simple query in Access 2000 that I run a search form off of, with the end result being a report. The form searches for From Date, To Date and GL#. The GL# is entered into the database by the users as 0000-0000-000 (except with numbers of course). But, now they only want to report on the first 8 numbers without the 3 last numbers. So, for example: If they had the following table and they searched from 06-01-07 to 06-30-07 and only wanted to report on 1234-1234, and they don't care what the last 3 numbers are:

Date GL
06-01-07 1234-1234-123
06-05-07 1234-1234-223
06-10-07 5555-5555-555
06-15-07 1234-1234-123
06-20-07 6666-5666-666
06-25-07 1234-1234-111

They want it to return:

06-01-07 1234-1234-123
06-05-07 1234-1234-223
06-15-07 1234-1234-123
06-25-07 1234-1234-111

How can I specify this in the query for them to only input "1234-1234" on the search form?

I put a formula in a GL field and put, GL:Left([GL],9), it returns what I want, but can't search on it, if that makes sense.

I hope I make sense to someone!!! =)

Thanks,
Melissa

 

[PHV]

A starting point (SQL code snippet):
WHERE GL Like '1234-1234-*'

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

 

[jeff3448]

i am having a similar problem but i want to use Like operators as the return expressions in a SQL iff expression

my code looks like

iif(forms![runreport]![A] = -1 , (info.module) like "1756-I*" , (info.module) like "1756-O*" )

Where A is a check box in a form that runs a report
when I use this code the report runs, but is empty

If I replace the wildcard statements with actual values in my table (ie. like "1756-IA16" instead of like "1756-I*"), my report runs and has all the appropriate records in it

Am I using the wildcard wrong in this case or is there something else I could be doing wrong?

thanks for any help

 

[dhookom]

You can't use the "Like" or any other comparison operator inside the IIf().

Try:
Info.Module Like IIf(forms![runreport]![A] = -1 , (info.module), "1756-I*" , "1756-O*" )

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Perhaps this ?
Info.Module Like '1756-' & IIf(Forms![runreport]![A]=True, 'I' , 'O') & '*'

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

 

[jeff3448]

that works!

thanks for the help