Only show between today and 14 days.

[kevinr1]

Hello,

How do get my report to only show queries that are between today and 14 days.
It has to get the information from:

Table: Klachten
Field: Op te lossen voor ( is a date querie )

The field: "Op te lossen voor" has certain dates stored in every cell.

 

[stopher]

You could use the following in your criteria:

Between Date() and Date()+14

hth
Chris

 

[kevinr1]

Thx for reply:

Putted it in: On Load


Private Sub Report_Load()

Between Date And Date + 14

End Sub

Got an error when I got back to the report:
Sub or Function not defined

 

[stopher]

No, don't put it in the On Load event.

You have a couple of choices....

You could put it in the query for your report i.e. open the query for your report and add it as the criteria for the date you are interested in.

Or you could put it in the report's filter:

[Op te lossen voor] Between Date And Date + 14

Post your database if you are stuck

hth
Chris

 

[kevinr1]

Well, my access file is in Dutch.
Have tried your method but it doesn't filter

Database report in attachment.

 

[stopher]

Could you save it as Access 2003 please as I don't have access to 2007+ at the moment
Thanks
Chris

 

[kevinr1]

It says I can't :S
Already tried but a message keeps popping-up saying:
The given sort order for this database is not supported by this end meaning for this database
( in dutch, but i tried to translate in english )

 

[stopher]

I've added the filter I mentioned to you report (see attached). It now shows only current-current date+14

hth
Chris

 

[kevinr1]

Very nice!!!
Helps alot. Thx!

//Close thread or Thread solved!

 

[McSwifty]

While this post is still open, I thought I would ask.

Why use date() + 14 instead of dateadd("d",14,date()) ??

Is it the same or does it evaluate differently depending on where you use it?

Thanks.

 

[stopher]

Good question.

Date/time is really just a decimal number taken from a specific point in time (beginning of the last century I think).

Try this:
debug.Print cdbl(now())

It will give you somthing similar to this:
40718.5724305556

The part before the decimal point is the number of whole days since day zero. And the bit after the decimal point is part of a day. In the above, .5724.. is just over 12 hours.

Anyway, adding days is easy. Just add the number of whole days. However, adding hours is trickier (as is months years etc) because some conversion is necessary.

So, to answer your question,I was being lazy when adding the days. DateAdd would work just as well. But if I were doing hours or other time measure I would definately use dateAdd and let the function do the work.

hth
Chris

 

[McSwifty]

Anyway, adding days is easy. Just add the number of whole days. However, adding hours is trickier (as is months years etc) because some conversion is necessary

I take it using the "m" in Dateadd does this then to save the tricky calculations with month lengths and the annoying February length every 4 years.

Thanks for the answer. Clarifies it nicely.

 

[boblarson]

I take it using the "m" in Dateadd does this then to save the tricky calculations with month lengths and the annoying February length every 4 years.

Yes, unless you have a very specific number of DAYS to add, for example to add months and years, you would want to use DateAdd so it will take into account Leap Years.

But I use DateAdd (or try to) instead of the Date+x because it then becomes CONSISTENT usage throughout my database.

 

[McSwifty]

Thanks Bob, I have been using dateadd in most of my queries and even had to concatenate some fields due to having my date format in dd/mm/yyyy format instead of the Access expected mm/dd/yyyy. This I found especially true when building SQL queries in VBA, the date format does not automatically switch like it does in a query.

Appreciate the confirmation I have done good.

 

[boblarson]

Thanks Bob, I have been using dateadd in most of my queries and even had to concatenate some fields due to having my date format in dd/mm/yyyy format instead of the Access expected mm/dd/yyyy. This I found especially true when building SQL queries in VBA, the date format does not automatically switch like it does in a query.

Appreciate the confirmation I have done good.

In VBA you can use format like this:

Format(Me.txtDate, "\#mm\/dd\/yyyy\#")

and that should overcome all of the regional settings and pass what Access is looking for when doing comparisons.

 

[Joebates26]

Hi all,

I have built a database for my work and I have been trying to figure out how to write a formula to tell me how many days an Invoice is outstanding? The formula will be written inside a Report Field After I have this formula I will be creating a report based on all Invoices that are Current - 30 days, 30-60, and 60-90. Help on this would be greatly appreciated also. I have uploaded my database.