Run-Time error '3709'

[jscriptor09]

Hi folks

I am using access 2010 and running into this error

Run-Time error '3709';


The search key was not found in any record

when applying a Filter which exceute this statement
Me.Filter = "[LOB_Region] = 'NORTH AMERICA' AND [YR_FW] = 2009.38 AND [LOB_Entity] = 'Engineering' AND [LOB_ActivityStatus] = 'Complete'"

it seems like whenever there is a space like here 'NORTH AMERICA' the error pops up. If I use something like 'Europe' I don't see it.

I did a lot of web searches and tried all the tricks ... restart DB, make sure fields name don't have spaces, table is not too large and so forth. Nothing works except not haveing a space in the record search string.

Does anyone see a proble with the wary this query is structured?

Many thanks for your help
Mike

[June7]

Run Compact & Repair. If this a split db, run the C&R on the backend.

I just tested a query with criteria having multiple words and not an issue. However, I have not tested the Filter property in 2010. If the C&R doesn't fix maybe there is a bug in 2010 you will have to deal with. Either remove spaces from the actual data so NORTH AMERICA becomes NorthAmerica or format the search string to disregard spaces. Try:
Replace([LOB_Region]," ", "") = 'NORTHAMERICA'

If the value NORTH AMERICA is from a form control, then apply the Replace function on that input as well.

[boblarson]

It can also be a sign of data corruption.