Easy (?) Access SQL question

[shedlord]

Scenario: Someone at work has an Access database she fills in daily. We need to design a query that filters on a number field called 'Week_Number' so that records are only shown if they are between 2 weeks that the user is prompted to give.

A query was built which has a WHERE clause that goes...

WHERE ((([blah].Week_Number)>[Week greater than] And ([blah].Week_Number)<[Week less than])

But it doesn't seem to work properly and I don't get why. Eg. If you put 2 in for 'Week greater than' and 8 in for [Week less than] it will still show you results with 'week_number' values outside that range.

What am I doing wrong here?

Thanks.

 

[Golom]

Some things that you can try

- Set a PARAMETERS clause to define what the input parms are
PARAMETERS [Week Greater Than] Integer, [Week Less Than] Integer;

- Check that [blah].Week_Number is really a numeric data type. If it's text then you can get the results that you're seeing.

 

[PHV]

Due the missing closing parens I guess the WHERE clause you posted is incomplete.
No Or operator ?
If numeric vs text issue:
WHERE Int([blah].Week_Number)>Int([Week greater than]) AND Int([blah].Week_Number)<Int([Week less than])

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

 

[shedlord]

Ok, sorry folks but this turned out to be a basic error on my part. The SQL statement above was taken from an earlier version of the database. Extra criteria had been incorrectly applied to the one that was going wrong - a case of me misunderstanding how the Query design view worked.

I'll stick to writing them out in SQL view in future!