Return data in the last 90 days only

[chally3]

Good afternoon all,

I have a database which collates data from various sources , 1 particluar field is a date filed called NCRDATG.
I am trying to filter out all data older than 90 days prior to todays dates usisng critera Between Date() And Date()-90 but this is not filtering any data.

Any suggestions would be appreciated.

NCRDATG field properties are 'medium date'

Kind regards
Mark

 

[Joe4]

If your date field is really stored as Date and not Text, that should work.
Please post the SQL code of your query, and some data samples that should be excluded, but aren't.

 

[strive4peace]

Hi Mark,

also, please tell us the date format in your localized settings ... are you using an international format?

do you have other criteria? (we will see that if you post your SQL statement as Joe suggested)

when you are looking at a query, you can click the View dropdown on the HOME ribbon to get to the SQL view

 

[chally3]

Hi Mark,

Hi,

The date format is date/time - I did change it at source to short date xx/xx/xxxx, but it has defaulted back overnight to xx/xx/xxxx HH/MM/SS which I assume is the default from where we export from.

The only over criteria is on a text field , which is: like "PURCH" and not like "PURCHSWJ"

 

[chally3]

SQL view of my query


SELECT Stock.STOLOC, Part.PTMDESC, Stock.STOPTNO, NCR.NCRPTNO, Supplier.SUPNAME, Stock.STOPHYQ, Part.PTMCTLC, NCR.NCRCORR, NCR.NCRCOMM, NCR.NCRDATG
FROM (Part INNER JOIN (NCR INNER JOIN Stock ON NCR.NCRNO = Stock.STOLOC) ON Part.PTMPTNO = Stock.STOPTNO) LEFT JOIN Supplier ON Part.PTMSUPC = Supplier.SUPCODE
WHERE (((NCR.NCRDATG) Between Date() And Date()-90) AND ((NCR.NCRAREF) Like "Purch")) OR (((NCR.NCRAREF) Not Like "PURCHSWJ"))
ORDER BY Stock.STOLOC;

 

[anvg]

Hi
Between syntax is Between FromMinValue And ToMaxValue

((NCR.NCRDATG) Between Date()-90 And Date())

Regards,

 

[chally3]

Hi still not working

SQL IS

SELECT Stock.STOLOC, Part.PTMDESC, Stock.STOPTNO, NCR.NCRPTNO, Supplier.SUPNAME, Stock.STOPHYQ, Part.PTMCTLC, NCR.NCRCORR, NCR.NCRCOMM, NCR.NCRDATG
FROM (Part INNER JOIN (NCR INNER JOIN Stock ON NCR.NCRNO = Stock.STOLOC) ON Part.PTMPTNO = Stock.STOPTNO) LEFT JOIN Supplier ON Part.PTMSUPC = Supplier.SUPCODE
WHERE ((NCR.NCRDATG) Between Date()-90 And Date()) AND (((NCR.NCRAREF) Like "Purch")) OR (((NCR.NCRAREF) Not Like "PURCHSWJ"))
ORDER BY Stock.STOLOC;

 

[Joe4]

If you look at your query in Design View, you will probably notice that this criteria:

[COLOR=#333333](((NCR.NCRAREF) Not Like "PURCHSWJ"))[/COLOR]

is on a different line than your date range criteria (because the way it is structured as an OR and the parentheses used).

That means that part of the criteria is NOT considering your date range at all! If you would to include the date range with that piece too, copy down the data range criteria to the second criteria row so it exists on ALL rows that show any criteria.

 

[chally3]

If you look at your query in Design View, you will probably notice that this criteria:

[COLOR=#333333](((NCR.NCRAREF) Not Like "PURCHSWJ"))[/COLOR]

is on a different line than your date range criteria (because the way it is structured as an OR and the parentheses used).

That means that part of the criteria is NOT considering your date range at all! If you would to include the date range with that piece too, copy down the data range criteria to the second criteria row so it exists on ALL rows that show any criteria.

works perfect now, thank you

 

[strive4peace]

glad you got it!

just a couple comments though ...

> "like "PURCH" and not like "PURCHSWJ""

because there are no wildcards (for instance, *), this will behave the same as

= "PURCH" and <> "PURCHSWJ"
which will also give you better performance

If you want, for instance, to truly make it a mask, use:
like "*PURCH*" and not like "*PURCHSWJ*"

~~~
also the BETWEEN operator does not care if the minimum value is first or last -- so ok to do BETWEEN max and min