Generating report including all new entries the last 90 days!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello again!

I was wondering how to create a report that displays all new entries the
last 90 days! All entries has a date field, so should be possible.
Do i have to make a query, or can i just have it activated by a button on a
form??

The date format is short date
 
The example below assumes a command button named "cmdLast90", and a date
field named "MyDate":

Private cmdLast90_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'Save any edits.
strWhere = "[MyDate] >= " & Format(Date - 90, "\#mm\/dd\/yyyy\#")
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End Sub

If you want to use a query, the Critieria to put under the date field would
be:
 
Jorgen said:
I was wondering how to create a report that displays all new entries the
last 90 days! All entries has a date field, so should be possible.
Do i have to make a query, or can i just have it activated by a button on a
form??
Click to expand...


You can use the OpenReport method's WhereCondition argument
to filter the data from the table you are using as the
report's record source.

I fyou look at the code generated by the button's print a
report wizard, just change the DoCmd line to look like:

DoCmd.OpenReport "name of report". _
WhereCondition:= "DateDiff("d", [date field], Date())<90"
 
Hi thanks again!

I tried this method!
and this is the code i used!

DoCmd.OpenReport "present report", acViewPreview
WhereCondition = DateDiff("d", [Date], Date) < 10

I had to remove the Quotation marks to get the wherecondition to work. the
problem however is that it can't find the [Date] which is the name on the
field in the table. Do i have to specify the location more?
(i have changed the range to ten just because this base is a bit too new to
sort on 90 days yet...)
--
Thanks, Jorgen


Marshall Barton said:
Jorgen said:
I was wondering how to create a report that displays all new entries the
last 90 days! All entries has a date field, so should be possible.
Do i have to make a query, or can i just have it activated by a button on a
form??
Click to expand...


You can use the OpenReport method's WhereCondition argument
to filter the data from the table you are using as the
report's record source.

I fyou look at the code generated by the button's print a
report wizard, just change the DoCmd line to look like:

DoCmd.OpenReport "name of report". _
WhereCondition:= "DateDiff("d", [date field], Date())<90"
Click to expand...
 
I got it sorted out!
Did a very simple between function in the query! worked like a charm!!

Thanks for all help!
--
Thanks, Jorgen


Jorgen said:
Hi thanks again!

I tried this method!
and this is the code i used!

DoCmd.OpenReport "present report", acViewPreview
WhereCondition = DateDiff("d", [Date], Date) < 10

I had to remove the Quotation marks to get the wherecondition to work. the
problem however is that it can't find the [Date] which is the name on the
field in the table. Do i have to specify the location more?
(i have changed the range to ten just because this base is a bit too new to
sort on 90 days yet...)
--
Thanks, Jorgen


Marshall Barton said:
Jorgen said:
I was wondering how to create a report that displays all new entries the
last 90 days! All entries has a date field, so should be possible.
Do i have to make a query, or can i just have it activated by a button on a
form??
Click to expand...


You can use the OpenReport method's WhereCondition argument
to filter the data from the table you are using as the
report's record source.

I fyou look at the code generated by the button's print a
report wizard, just change the DoCmd line to look like:

DoCmd.OpenReport "name of report". _
WhereCondition:= "DateDiff("d", [date field], Date())<90"
Click to expand...
Click to expand...
 
Jorgen said:
I got it sorted out!
Did a very simple between function in the query! worked like a charm!!
Click to expand...


Good.

If you want to remove the dependency from the query, then we
should unravel the issues in the WhereCondition argument.
The outside quotes are mandatory, but I used the wrong
interior quotes. Also, you should not have a field named
the same as a built-in function and the Date function needs
the parenthesis. I think this might work until you fix the
naming conflict:

WhereCondition = "DateDiff('d', [Date], Date()) < 10"

But after all this hassle, I like Allen's way better.

You could also use the Between approach in the
WhereCondition argument.
 
Back
Top