Simple Query

[Pauly78]

Hi, I'm trying to make my query return all entries with a value in status field and a value in status date. The problem is my status field, i have about 5 different status's and i want it to return an entry that is any of these status with status date more than 7 days ago. Ive attached an image to show how i have done it, but this is only giving me entries which have the first status criteria, non of the 'or' status's seem to be working. Can anyone help?

 

[arnelgp]

Select * From tblMaintenance Where DateDiff("d", [StatusDate], Date()) > 7;

 

[Brianwarnock]

There is no need to incur the cost of a function call to DateDiff as your date test is correct, on the other hand why check on status if you want them all.
I cannot see anythink wrong with what you coded, remove the first line of the criteria and see what happens, maybe none of the others meet the criteria, you could test each one separately as a check.

Brian

 

[Pauly78]

Select * From tblMaintenance Where DateDiff("d", [StatusDate], Date()) > 7;

I don't know what this means... do i have to place this entire thread in my criteria box?

@brianwarnock those are not the only status's there is a few more which i don't want to be in the results. i think i have about 9 different status's such as estimate required, estimate given etc

Thanks

 

[Brianwarnock]

The code shown by arnelgp is SQL code, you can view the SQL generated by the design grid by selecting the option from the drop down menu.
The * means show all columns.

Did you try each criteria separately in a query to see if any records met the date criteria?

Brian

 

[Pauly78]

Hi Brian, yes I've tried them all, I have an entry for each status with a date for longer than 90 days ago but the only thing that shows is whatever is first, I'm trying to generate a report for anything older than 90 days and has any of those status. Im baffled.

 

[obeylele]

I don't know what this means... do i have to place this entire thread in my criteria box?

try with below code in the criteria for StatusDate instead of Date()-7

DateAdd ("d", -7, Date() )

you dont have to add this criteria multiple times, once is enough

below link explains DateAdd function
http://www.techonthenet.com/access/functions/date/dateadd.php

 

[Pauly78]

Hi Thanks for that, ive tried it and it gives me a different result but still not working :banghead:

 

[Pauly78]

yay ive done it! i placed <=Date()-7 only in the first criteria line, works fine. Thanks for your help.

 

[Brianwarnock]

yay ive done it! i placed <=Date()-7 only in the first criteria line, works fine. Thanks for your help.

That does not make sense unless Access has changed since I last used it as there would be no date check for the OR criterials . Any chance you could copy and paste the SQL view into the thread just to satisfy my curiosity.

Brian

 

[Pauly78]

My Apologies, it was showing all entries with that status regardless of the time frame, ive now changed it so my criteria is in each box and it works fine. My problem was, i realised i had this <Date()-7 instead of <=Date()-7, i was missing the equals sign. now ive changed it all seems good.

 

[Brianwarnock]

Thank you for coming back, I don't understand all that has happened but I am glad that it is working as it should.

Brian