Parameter query problem 1

[snub]

I have a query that does a calculation on how long a product has been in inventory (=DATE ()-[Received]). I want to set this up as a Parameter query so that I can look at only products over a certain number of days. As a select query it runs fine but when I add my Parameter statement [You're looking for ... days old?] my data comes back incorrect. For example: If I say I'm looking for products over 4 days old all products 10, 20, 30, 100, 200 days old do not show , but products 40-90 days old show. It's as if the query is looking at only the first digit and making the decision based on that alone. I'm a newbie and doing this in design view and the grid. I'm sure there's an easy fix, but I don't know it.

 

[lespaul]

switch to the SQL view and post your query.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

In the query grid:[tt]
Field cell NumberOfDays: Int(Date()-Received)
Criteria cell >=Val([You're looking for ... days old?])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snub]

Leslie,

Here is the SQL view of the query. Again when I take the parameter function out it runs properly, but with the parameter there I get different results.

SELECT tblProducts.ProductNumber, tblProducts.Make, tblProducts.Model, tblProducts.Cost, tblProducts.Received, Date()-[Received] AS AGING, tblProducts.Type, tblProducts.Sold
FROM tblProducts
WHERE (((Date()-[Received])>=[You're looking for products greater than how many days?]) AND ((tblProducts.SOLD) Is Null))
ORDER BY Date()-[Received] DESC;


PHV - This was my original statement:
AGING: Date()-[RECEIVED]
>=[You're looking for products greater than how many days?]
What does the Val do and is it needed? With it in the query won't run and if I take that out it doesn't ask me for a value.

Thanks

 

[PHV]

PARAMETERS [You're looking for products greater than how many days?] INTEGER;
SELECT tblProducts.ProductNumber, tblProducts.Make, tblProducts.Model, tblProducts.Cost, tblProducts.Received, Date()-[Received] AS AGING, tblProducts.Type, tblProducts.Sold
FROM tblProducts
WHERE Int(Date()-[Received])>=[You're looking for products greater than how many days?] AND tblProducts.SOLD Is Null
ORDER BY [Received] ASC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snub]

PHV,
It works, but you probably knew that. I don't fully understand why yours works and mine didn't but thank you anyway.