Parameter Query

[reneelacks]

When setting the criteria for the following expression to: >30

to view any Work Orders that have been open for 30 or more days, the results display perfectly; however I want the user to be able to set the Min # of Days.. when I set the following Parameter: >[Enter Min # of Days Open]
...and enter 30, it returns WOs with that have been open for 4 or more days.... I don't want the user to have to enter the greater than (>) sign, just the #of Days

Help please!!!

#ofDaysOpen: IIf(Nz([whdateout],0)<>0,DateDiff("d",CDate(Mid([whdatein],5,2)+"/"+Right([whdatein],2)+"/"+Left([whdatein],4)),CDate(Mid([whdateout],5,2)+"/"+Right([whdateout],2)+"/"+Left([whdateout],4))),DateDiff("d",CDate(Mid([whdatein],5,2)+"/"+Right([whdatein],2)+"/"+Left([whdatein],4)),Now()))

 

[CJ_London]

Looks horribly complex for a simple datediff calculation. Can you explain why it is so complicated and where either the 30 or [Enter Min # of Days Open] fits into this.

Also, generally a bad idea to use # in field names and aliases - it can confuse access

 

[reneelacks]

The expression says if the WO has not closed (=0), then count the number of days it has been open thru today, otherwise count the difference between when it was open and when it closed. I then used >30 as the criteria as I only wanted to see Work Orders that had been open longer than 30 days.
I also want the user to be able to the age of the work orders they choose, I.e. WOs older than 60 days, 90 days, etc.

 

[namliam]

The reason why >30 will return 4 is because your field isnt being treated numeric, it is being treated as a character.
In character fields you will get an order by character instead of the whole which is what numbers does.
Numbers ordered as chars go
1,10,11,100,1001001,2,20,3,30654, 4, etc

 

[CJ_London]

From your coding
CDate(Mid([whdatein],5,2)+"/"+Right([whdatein],2)+"/"+Left([whdatein],4))

implies that whdatein is a text field rather than a date field - is this correct?

 

[namliam]

yeap seems like it and in the format of YYYYMMDD

 

[reneelacks]

its an integer in the format YYYYMMDD.

 

[reneelacks]

@ Namliam - so why does it work when I leave out the parameter?

 

[namliam]

Because you are probably asking the user for the parameter without further definition of the parameter...
The default for any parameter is Text or Char, which leads to access doing an implicit conversion text to number or number to text.... Implicit conversions are always bad because you are relying on a program making choices for you. In this case causing your issue.

See this link:
http://msdn.microsoft.com/en-us/library/dd638587(v=office.12).aspx#UsingParameters
which is for 2007 but you can see the idea and addapt / find it for your version... Define the parameter as a number and you should be fine....

Alternatively you can wrap your parameter in a INT([YOUR PARAMETER]), to convert your text to number explicitely and fix it that way... but the former is the better way.

 

[reneelacks]

Worked like a charm!