Problem adding/filtering in query 1

[Fubear]

I have a query which is being used as a recordsource on a form.

What I want to be able to to is to have the user enter two integer values into two text boxes and have the resultset of the next for only display the values between what the user entered.

My WHERE criteria is set to:
Between NZ([Forms]![FRM Main Switchboard]![text64],0) And NZ([Forms]![FRM Main Switchboard]![text84],9999999)

This seems to work fine, so the user can enter one, or both of the boxes, and get a result between, greater than, or less than the values they entered.

The table the number is pulled from is linked to the main table, and not every record has a matching record, so I have used NZ on the field so every record has at least 0 instead of Null.

My problem is that the number is the total of three fields on the table, each field is set to Long Integer data type. When the query is run the results are being treated as text, so if i say select between 20 and 25, i am also returning records with values of 210, 220, 230, etc

It looks soemhow that the value is being sorted as text, how can I change this so that it is being sorted as an integer value?


Currently the field def is:
FieldName: CInt(CInt(NZ([Field1],0))+CInt(nz([Field2],0))+CInt(nz([Field3],0)))

The totals add up correctly, and the WHERE statement works fine, I just cant get the results to filter correctly (

 

[RickSpr]

I think you need to put CInt() (or CLng()) around the expressions in your BETWEEN predicate.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Fubear]

Thanks a lot - a CLng around the NZ of my criteria has solved my problem.