Link to home
Start Free TrialLog in
Avatar of M M
M M

asked on

date criteria to pull up records for the last 3 months

I have a tblQuotes and want a qry to pull up quotes no older than 90 days.  I used: Between Date() And Date()-90

I then entered addl quotes written this month (Feb) into the table, but the query pulls records stopping at 1/30 even though I entered 20 or so quotes written in Feb.

Any ideas on trouble shooting this?
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

post the SQL of your query.
is your date field really DateTime type field?
Avatar of M M
M M

ASKER

Yes, it really is a date time field.
where is the SQL statement of your query?
can you upload a copy of your db?
Avatar of M M

ASKER

I do not know how to upload it, and don't necessarily want this db public.  What exactly are you looking for, I'll gladly answer the best I can.
<What exactly are you looking for>
The reason why you can not get the records with Feb dates from your query
Avatar of M M

ASKER

oops, here's the SQL:  SELECT tblQuotes.LNameRep, tblQuotes.QNo, tblQuotes.DeptName, tblQuotes.QDate, tblQuotes.Customer, tblQuotes.Qamt, tblQuotes.Items, tblQuotes.Prob, tblQuotes.[QtoS_Job#], tblQuotes.Status, tblQuotes.FUDate
FROM tblQuotes
WHERE (((tblQuotes.QDate) Between Date() And Date()-90) AND ((tblQuotes.Status)<>"Sold"));
test this query first, removing the criteria from Status field

SELECT tblQuotes.LNameRep, tblQuotes.QNo, tblQuotes.DeptName, tblQuotes.QDate, tblQuotes.Customer, tblQuotes.Qamt, tblQuotes.Items, tblQuotes.Prob, tblQuotes.[QtoS_Job#], tblQuotes.Status, tblQuotes.FUDate
 FROM tblQuotes
 WHERE tblQuotes.QDate Between Date() And Date()-90
Avatar of M M

ASKER

OK, you're my hero!  But why did removing the criteria make the difference?  Can I put it back in?
Avatar of M M

ASKER

oops, I meant removing criteria in the status field.
Avatar of M M

ASKER

while I was waiting for an answer, I tried to put <>"sold" back into criteria for status field, and it doesn't work, ANY idea why?
the records you have for Feb does not have "Sold" Status.

what are the values in the Status field?

you can always put back the criteria in the Status field..
Avatar of M M

ASKER

the Feb quotes are not sold (yet) so the field is blank.
no particular values in status field, only use it to flag sold quotes - to remove them from the qry results (bc they are no longer quotes, they're sales).
BUT it would not let me add <>"sold" back into criteria of status field, the results stop at 1/30/2017.
so why do you need to put a criteria in the Status field?

test this

SELECT tblQuotes.LNameRep, tblQuotes.QNo, tblQuotes.DeptName, tblQuotes.QDate, tblQuotes.Customer, tblQuotes.Qamt, tblQuotes.Items, tblQuotes.Prob, tblQuotes.[QtoS_Job#], tblQuotes.Status, tblQuotes.FUDate
  FROM tblQuotes
  WHERE tblQuotes.QDate Between Date() And Date()-90  And tblQuotes.Status Is Null
Avatar of M M

ASKER

OK, so that only pulled records for Jan & Feb?  There should be quotes from Dec 8 and newer, right?
< There should be quotes from Dec 8 and newer, right? > yes, if their Status field is blank.
Avatar of M M

ASKER

yep, they are blank.
What made you suggest removing the criteria in the status field?
what now?
I can't have criteria in both the QDate field and Status field?
ok. what is it that you really want to show as result of the query.
Avatar of M M

ASKER

open quotes written in the last 3 months.
I already posted the query. at #a42000853
Avatar of M M

ASKER

but when I tried that it only pulled record from Jan & Feb.
Avatar of M M

ASKER

there are 76 quotes written Dec 9 - 31 (not sold/null) that are pulled up.
Avatar of M M

ASKER

oops ... there are 76 quotes written Dec 9 - 31 (not sold/null) that are NOT pulled up
Avatar of M M

ASKER

are you still here?  sorry I don't know what protocol is on this website.
that is why I wanted to see your table so I can see the problem.
Avatar of M M

ASKER

OK ... as I said I don't know how to upload, thank you for your effort anyway.  Have a good evening.
when you write comment, there is a link at the left bottom of the box where you are typing your comment that says "Attach File"
just click on it and locate the file you want to attach.
try this query

SELECT tblQuotes.LNameRep, tblQuotes.QNo, tblQuotes.DeptName, tblQuotes.QDate, tblQuotes.Customer, tblQuotes.Qamt, tblQuotes.Items, tblQuotes.Prob, tblQuotes.[QtoS_Job#], tblQuotes.Status, tblQuotes.FUDate
   FROM tblQuotes
   WHERE tblQuotes.QDate Between Date() And Date()-90  And (tblQuotes.Status <> "Sold" Or tblQuotes.Status Is Null)
Avatar of M M

ASKER

Thank you Rey.  I will try to modify a dummy database for upload and try this website again.  I can't upload company info into cyberspace.

But thank you for your time.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M M

ASKER

OMG, at first glance, THAT looks like it worked.  (I mistakenly was thinking 3 months was Dec, but it's actually Nov), and this pulled up a new number/count of records, and looking at dates it appears to be correct.  Initially it pulled up 341, then 406 (for just Jan & Feb), but NOW it's 392!

I'll have to study your SQL (?) and process how it's different to understand why it works and what I was doing wrong.

I don't know how YOU can help someone (like me) that may not be able to convey their confusion.

I saw some place where you guys get points?  If this really worked (sorry, am bleary-eyed tonight!), how do  I give you points?
Avatar of M M

ASKER

I mean why do you have to add the not "sold" Or is null to status field criteria?  Why did my original incorrect SQL have all those sets of parentheses in the SQL view and yours not?

Troubleshooting is soooo daunting!!!
<I mean why do you have to add the not "sold" Or is null to status field criteria? >

so you can get the records with status that are not "sold" and the records with blank/null status
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M M

ASKER

Thank you PatHartman for the addl clarification!
You're welcome
90 days is not three months, so you should use DateAdd:

SELECT 
    tblQuotes.LNameRep, 
    tblQuotes.QNo, 
    tblQuotes.DeptName, 
    tblQuotes.QDate, 
    tblQuotes.Customer, 
    tblQuotes.Qamt, 
    tblQuotes.Items, 
    tblQuotes.Prob, 
    tblQuotes.[QtoS_Job#], 
    tblQuotes.Status, 
    tblQuotes.FUDate
FROM 
    tblQuotes
WHERE 
    tblQuotes.QDate Between Date() And DateAdd("m", -3, Date()) 
    AND 
    Nz(tblQuotes.Status) <> "Sold"; 

Open in new window

/gustav
Avatar of M M

ASKER

Many thanks Rey, your solution worked!  And Pat, thank you your add'l clarification as to why it works!
You're welcome.  I believe in the "teach a man to fish" method which is why you will rarely if ever see a "try this" answer from me.  One thing I forgot to mention (probably because I had already written at least one long answer explaining it so far this week), Whenever you combine different relational operators in an expression, you should always use parentheses to control the order of evaluation.  Boolean logic dictates a specific order of operation starting with NOT, AND, OR.  But most people don't remember what they learned in basic algebra in 8th or 9th grade so they can't understand why their string of AND and OR operations are not working as they expected.
a AND b OR c is evaluated as (a AND b) OR c so if you expected a AND (b OR c) you will be disappointed.  Using parentheses not only ensures the expression is evaluated as you want it to be, it makes it clearer to other people who have also forgotten all they ever learned in Algebra I.