excel style formulas in query criteria

**surfengine** · 01-13-2010, 03:51 PM

I have a query with 500k rows and use access and excel 2003.
I want to move the data into excel but am limited with the amount of rows.

To reduce the query amount below 65k rows I want to key on some criteria in 1 field.

Anyway, I want to put a formula into the criteria to only select rows within a range of data. In excel I would sort using this simple formula:
=IF(MID(P2,4,2)="45",MID(P2,4,2),"")
Basically, pulling out 2 characters in the field and if they equal 45 then include that row.

Is there some way in access to put similar formulas?

I realize I could key on date or maybe some other criteria, but if I could get this work then it would satisfy some other needs as well.

**Ron Coderre** · 01-13-2010, 04:16 PM

There are a few ways you can approach your criteria.

Try playing with one of these:
WHERE MID([PartNum],4,2)="45"

WHERE [PartNum] LIKE "???45*"

WHERE LEFT([PartNum],4) LIKE "???45"

Does that help?

**surfengine** · 01-13-2010, 04:33 PM

Originally Posted by Ron Coderre

There are a few ways you can approach your criteria.

Try playing with one of these:
WHERE MID([PartNum],4,2)="45"

WHERE [PartNum] LIKE "???45*"

WHERE LEFT([PartNum],4) LIKE "???45"

Does that help?

Thanks, I am getting error "invalid syntax" when entering as above. If there something missing?
I am entering it into the design view - criteria field.

**ConneXionLost** · 01-13-2010, 05:11 PM

Hi surfengine,

It may be best to enter it in as a new field.

In query design view, Field row:

DataChop: Mid([PartNum],4,2)

then in Criteria:

45

Cheers,