>= Query Giving Unexpected Results

[razorking]

I have a weird one. I have a query that calculates days old (as in a number) of orders - that query look like this: DaysOld: Date()-[VORDDATE]

That part (above) works fine. Now I am trying to make a report to allow the user to specify a parameter for number of days old to view on the report. For example - only show orders older than 30 days or 100 days or whatever. I have made a form with a button to run the report and an unbound text box for the user to input a "days old" value. The report uses a query as the source and the query look to the text box on the form to get the value for the days old parameter the user keys. I have tried both > and >= but I am getting inconsistent results. If I key most numbers under 100 it seems to work fine, if I key 100 or greater I get results that included orders that are double digit in days old - or everything - I guess. The number 99 makes it unhappy as well.

Any ideas?

 

[jdraw]

Please post the sql of the actual query or a dumbed down(no private info) version of your database.

 

[razorking]

Ok - see attached - and thanks for looking.

 

[JHB]

Convert the value in control on the form to long.

>CLng([Forms]![Form1]![Text1])

 

[razorking]

JHB,

I changed the criteria on the underlying query - as per your instructions - and that does work. Thanks - that will save me some time.

 

[namliam]

Just FYI, your parameter is being treated as text, which is causing implicit conversions.... which as you are noticing is bad.

In text all numbers are ordered by text order and will go along the lines of
1
10
100
11
1100
12
2
3
45
5

Unless you explicitely convert your text to numbers or your numbers to text (remember to include preceding zeros like 0001, 0100 and 1100) to prevent problems.

Also instead of using a form control to filter your query I can strongly suggest you use "Filter by form" to make sure your queries are right.