Query criteria

[Jaye7]

I have a query criteria where the data in the query field are numbers.

the following works as long as I have a value in the textbox otherwise I get an error message stating, this expression is typed incorrectly blah blah.

Like [FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null

also tried

[FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null

yet I use the following script for another column which has identical data and it works fine either way and I can use it in the other column and it works with or without data in the textbox

Like [Forms]![FONMain]![txtFact1] Or [Forms]![FONMain]![txtFact2] Or [Forms]![FONMain]![txtFact3] Or [Forms]![FONMain]![txtFact4] Or [Forms]![FONMain]![txtFact5] Or [Forms]![FONMain]![txtFact6] Or [Forms]![FONMain]![txtFact7] Or [Forms]![FONMain]![txtFact8] Or [Forms]![FONMain]![txtFact9] Or [Forms]![FONMain]![txtFact10] Or [Forms]![FONMain]![txtFact11] Or [Forms]![FONMain]![txtFact1] Is Null Or [Forms]![FONMain]![txtFact2] Is Null Or [Forms]![FONMain]![txtFact3] Is Null Or [Forms]![FONMain]![txtFact4] Is Null Or [Forms]![FONMain]![txtFact5] Is Null Or [Forms]![FONMain]![txtFact6] Is Null Or [Forms]![FONMain]![txtFact7] Is Null Or [Forms]![FONMain]![txtFact8] Is Null Or [Forms]![FONMain]![txtFact9] Is Null Or [Forms]![FONMain]![txtFact10] Is Null Or [Forms]![FONMain]![txtFact11] Is Null

 

[smig]

What are you trying to do ?

 

[Jaye7]

I am trying to run a query based on a value in a textbox, the field in the query is a number, if there is no value then return all records, if there is a value then return that value. There are actually around 10 other query parameters but this is the only one that fails with or without the others.

If there is no value then the query brings up the error message, which is very weird as I use this sort of statement all the time, but the fields are usually text.

 

[smig]

Create a query (use the query builder).
Put the field you want to filter and in it's criteria put [Form Name].[TxtBoxName].
Create another field in the query Exp1: Null and put the same criteria as befor, only this time put the criteria in a new line. this will make the criteria to work as or.

 

[spikepl]

Your criterion is not correct, because you require the assignment using the left-hand side to be checked even in cases where it in fact is Null.

[FORMS]![FONMain]![txtTest15] AND   NOT [FORMS]![FONMain]![txtTest15] Is Null OR  [FORMS]![FONMain]![txtTest15]  Is Null

Alos, it is meaningless to use Like without some wildcard.

 

[Jaye7]

Thanks for your help Spikepl and Smig, that works well Spikepl and will come in very useful in the future..