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?
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?
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?
can you upload a copy of your db?
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
The reason why you can not get the records with Feb dates from your query
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 "));
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
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
ASKER
OK, you're my hero! But why did removing the criteria make the difference? Can I put it back in?
ASKER
oops, I meant removing criteria in the status field.
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..
what are the values in the Status field?
you can always put back the criteria in the Status field..
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.
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
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
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.
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?
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.
ASKER
open quotes written in the last 3 months.
I already posted the query. at #a42000853
ASKER
but when I tried that it only pulled record from Jan & Feb.
ASKER
there are 76 quotes written Dec 9 - 31 (not sold/null) that are pulled up.
ASKER
oops ... there are 76 quotes written Dec 9 - 31 (not sold/null) that are NOT pulled up
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.
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.
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)
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)
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.
But thank you for your time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
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!!!
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
so you can get the records with status that are not "sold" and the records with blank/null status
to accept a comment see this link
http://support.experts-exchange.com/customer/portal/articles/608621-how-do-i-accept-a-comment-as-my-solution-
http://support.experts-exchange.com/customer/portal/articles/608621-how-do-i-accept-a-comment-as-my-solution-
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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";
/gustav
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.
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.
is your date field really DateTime type field?