Help With Date Calculation Query

[Ed Podowski]

I have not used access in awhile. I have dumped a lot of data into an access
database and I need to compare two date fields that would essentially need to
measure the number of days between the invoice date and the payment date. I
created the following expression, but cannot determind what I am doing wrong.

[AP-Payment History Report]![CheckDate] "Date ()" - [AP-Payment History
Report]![InvoiceDate] "Date ()" < "30"

I guess I want the query results to show the invoices that were paid in less
then thirty days. Am I saying that right.

I have tried to use the wizard, but was not successful. Thank you for your
help, Ed Podowski

 

[Douglas J. Steele]

Add a computed field to your query

DateDiff("d", [InvoiceDate], [CheckDate])

and put <30 as the criteria under that computed field.

 

[Ofer Cohen]

You can use the DateDiff function to return the different between two dates

As a new field in the query write:

DateDiff("d",[AP-Payment History Report]![CheckDate], [AP-Payment History
Report]![InvoiceDate])

And in the criteria section of that field write:
<30

 

[Ed Podowski]

Hello Ofer and Douglas,

Thank you both for your reply.

It took me awhile to figure out how to add a computed field. I found a help
topic named “Create a calculated field in a query (MDB)†which spelled out
that it goes in the query [as you both have stated] and not in the table as a
field.

In the total box I selected expression.

I tried both variations of your expression and the query actually runs, but
before it runs the results it ask me two questions.

Enter The Parameter Value AP-Payment History Report!CheckDate - I click ok

Enter The Parameter Value AP-Payment History Report!InvoiceDate - I click ok

I am not sure what to put here. I want all the invoices in the table which
were paid in less than thirty days from the invoice date.

If I put in nothing the query is empty. When I put in 1 it returns 63,000+
records which is all the records in the table for that query and when I
manually calculate the date difference some are greater than 30 days and some
are less than 30 days. So I know I still have something wrong.

I don’t think this is a conflict, but in the check date field in the query
under criteria I have - Between #1/1/2005# And #12/31/2005# so I will just
get the checks which have been dated in 2005. There may be a better way to
write this, but the results are accurate. I have checks in the table written
before 01/01/2005 and after 12/31/2005 which do not show in my query results.

On another note…can I assume the “d†in your expression is to return the
results in days?

Douglas, I visited your web site and saw where you have a “y†in your
DateDiff explanation. So I drew the conclusion that was to return results in
years.

Thank you both again for your help, Ed Podowski

Add a computed field to your query

DateDiff("d", [InvoiceDate], [CheckDate])

and put <30 as the criteria under that computed field.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have not used access in awhile. I have dumped a lot of data into an
access
database and I need to compare two date fields that would essentially need
to
measure the number of days between the invoice date and the payment date.
I
created the following expression, but cannot determind what I am doing
wrong.

[AP-Payment History Report]![CheckDate] "Date ()" - [AP-Payment History
Report]![InvoiceDate] "Date ()" < "30"

I guess I want the query results to show the invoices that were paid in
less
then thirty days. Am I saying that right.

I have tried to use the wizard, but was not successful. Thank you for
your
help, Ed Podowski

 

[Ed Podowski]

Hello,

I found the issue with the query returning zero results and the request for
parameters. I had simplified the field names when writing my original post.
I realized this just a few minutes ago [after a few hours of sleep]. The
query worked perfectly when I changed the field names in your expression to
the actual field names.

Thank you very much for you help. It was a learning experience.

Ed Podowski

Add a computed field to your query

DateDiff("d", [InvoiceDate], [CheckDate])

and put <30 as the criteria under that computed field.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have not used access in awhile. I have dumped a lot of data into an
access
database and I need to compare two date fields that would essentially need
to
measure the number of days between the invoice date and the payment date.
I
created the following expression, but cannot determind what I am doing
wrong.

[AP-Payment History Report]![CheckDate] "Date ()" - [AP-Payment History
Report]![InvoiceDate] "Date ()" < "30"

I guess I want the query results to show the invoices that were paid in
less
then thirty days. Am I saying that right.

I have tried to use the wizard, but was not successful. Thank you for
your
help, Ed Podowski