Microsoft Access Query Question

mmcgillo88
mmcgillo88 used Ask the Experts™
on
I need a query that will select information from a table (say Table A is the data table name). I first need the query to add a datediff column/calculation. I then need the query to pull out three columns (adjamt, netamt, invoicetotal). For the third column invoice total I need the query to only select information that is less than 100 dollars and also less than 15 days (which will be calculated in the datediff column).
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
select adjamt, netamt, invoicetotal, datediff("d",[yourdatefield], date())
from tableA
where [invoicetotal]< 100 and datediff("d",[yourdatefield], date()) <15
I'm not sure what you would be finding difficult with this requirement- on the basis of what you have posted it looks quite straightforward. However I can't tell what dates you are referring to - you don't say.

Select adjamt, netamt, invoicetotal from tableA
where  invoicetotal<100 and datedif("d", firstdate, seconddate) <15

Author

Commented:
SELECT inv.InvoiceNumber, inv.VendorName, inv.DateSubmitted, inv.DateCheckRequested, inv.IM_Check_Confirmed_Date, inv.InvoiceTotal, DateDiff("d",[DateSubmitted],[DateCheckRequested]) AS PaymentTime
FROM tblInvoicesSubmitted AS inv
WHERE  InvoiceTotal<=100 and PaymentTime<=15
ORDER BY [PaymentTime] DESC;

Author

Commented:
That is my query but when I try to run it the error message of "show parameter value" appears

Commented:
Hello,

First, if you want less than $100 and less than 15 days, take out the = signs in your where clause (those are going to return less than or equal to).

Second, I don't beleive you can ORDER BY an alias (AS ___).  Put the actual column item in there:

ORDER BY DateDiff("d",[DateSubmitted],[DateCheckRequested])

Let us know if it gives another error.

Thanks,
Chris
Top Expert 2016
Commented:
SELECT inv.InvoiceNumber, inv.VendorName, inv.DateSubmitted, inv.DateCheckRequested, inv.IM_Check_Confirmed_Date, inv.InvoiceTotal, DateDiff("d",[DateSubmitted],[DateCheckRequested]) AS PaymentTime
FROM tblInvoicesSubmitted AS inv
WHERE  InvoiceTotal<=100 and  DateDiff("d",[DateSubmitted],[DateCheckRequested])<=15
ORDER BY  DateDiff("d",[DateSubmitted],[DateCheckRequested]) DESC;

or this

SELECT inv.InvoiceNumber, inv.VendorName, inv.DateSubmitted, inv.DateCheckRequested, inv.IM_Check_Confirmed_Date, inv.InvoiceTotal, DateDiff("d",[DateSubmitted],[DateCheckRequested]) AS PaymentTime
FROM tblInvoicesSubmitted AS inv
WHERE  InvoiceTotal<100 and  DateDiff("d",[DateSubmitted],[DateCheckRequested])<15
ORDER BY  DateDiff("d",[DateSubmitted],[DateCheckRequested]) DESC;

Author

Commented:
Needs to be less than or equal to 100 or 15, please advise on updated SQL syntax. Thank you.

Author

Commented:
Nevermind jsut added an equal sign and it worked. Thank you!!

Author

Commented:
What if I need the dates to be between 15 and 30, what would the syntax be? Same for the invoice amount to be between 100 and 1,000. Please advise. Thank you.
Top Expert 2016

Commented:
SELECT inv.InvoiceNumber, inv.VendorName, inv.DateSubmitted, inv.DateCheckRequested, inv.IM_Check_Confirmed_Date, inv.InvoiceTotal, DateDiff("d",[DateSubmitted],[DateCheckRequested]) AS PaymentTime
FROM tblInvoicesSubmitted AS inv
WHERE  (InvoiceTotal between 100 And 1000) and  (DateDiff("d",[DateSubmitted],[DateCheckRequested]) Between 15 And 30)
ORDER BY  DateDiff("d",[DateSubmitted],[DateCheckRequested]) DESC;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial