Full Version: Issues older than 90 days
UtterAccess Forums > Microsoft® Access > Access Forms
Stealth_2222
I am trying to run a report that returns all issues that are older than 90 days since they were submitted
Can anyone help me with the code that would be required to do this?
The field I'm dealing with is "Date Submitted"
And the table is called "Tooling Requisitions"
Thanks!
SerranoG
Base the report on a query that returns all where the criterion under Date Issued is

>DateAdd("d",90, [Date Submitted])

Edited by: SerranoG on Wed Jul 16 11:15:29 EDT 2008.
Stealth_2222
I'd rather do this thru VB code if I could. Does anyone have a way in which this would be completed through VB code?
If I can get the report to open thru a strWhere clause that would run a filter of the "Date Submitted' that is older than 90 days?
Any suggestions?
SerranoG
CODE
DoCmd.OpenReport "Report Name Here", acPreview, , "[Date Submitted] > #" & DateAdd("d", 90, Me.[Date Issued]) & "#"

Change "Report Name Here" and [Date Issued] to their real names.
Stealth_2222
I have another report in which I'd like to exclude a certain criteria of a field
The field I'm dealing with here is "Reason Code"
I want to exclude a certain text that is under this field : 2
FOr If i can just include 1,3,4,5,6,7 that would work to
Whichever is easiest to code would work for me
How would the VB code for this look?
Thanks so much!
SerranoG
If Reason Code is a numeric type, you can use
!--c1-->
CODE
DoCmd.OpenReport "Report Name Here", acPreview, , "[Reason Code] <> 2"

If it's a string, then
CODE
DoCmd.OpenReport "Report Name Here", acPreview, , "[Reason Code] <> '2'"
Stealth_2222
It's a string.
Thanks, that will work!
Stealth_2222
Sorry, my button opens multiple reports, whichever report is selected from a listbox
Therefore, I can't apply the code you specified to the docmd.openreport
Is there a way I can apply the filter when the report opens? I need something that will be written in the reports VB code.
Otried something like
docmd.applyfilter, , "[Date Submitted] > #" & DateAdd("d", 90, Me.[Date Submitted]) & "#"
But it won't work
Any suggestions?
Stealth_2222
OK...I changed my code to this:
oCmd.ApplyFilter , "[Date Submitted] > #" & DateAdd("d", 90, Me.[Date Submitted]) & "#"
Oget an error: "You entered an expression that has no value"
What does this mean?
SerranoG
Well, you're mixing up two things. The code I gave you is written on the button's ON CLICK event, not in the report's ON OPEN event.
lso, your criterion was that the date submitted is more than 90 days from the date issued, therefore, you cannot have [Date Submitted] twice in your WHERE statement.
To apply a filter upon the report opening based on a textbox in the form, you'd need something like.
Me.Filter = "[Date Submitted] > #" & DateAdd("d", 90, Forms.[Your Form Name Here].Form.[Date Issued]) & "#"
Me.FilterOn = True
Stealth_2222
Ok, I need it to run on the report open
However, I need to filter the records where the Date Submitted is older than 90 days
Odon't have a Date Issued field...so it would have to be something doing with the now date
Like if Now - minus Date Submiited is > 90 days, then those records would show
o you see what I'm trying to get at?
Your code is probly just a lil off of what I'm trying to do.
I appreciate the help!
accesshawaii
When you say older than 90 days, I'm taking it that you're doing that comparison on the current date, so if you wanted to do it in code with a filter on the report, you would do something like this.
CODE
Dim strFilter As String
Dim varEval As Variant
varEval = "#" & DateAdd("d", -90, Date) & "#"
strFilter = "[DateSubmitted] < " & varEval
MsgBox strFilter
DoCmd.OpenReport "MyReport", acViewPreview, , strFilter
Stealth_2222
OK, but I need to do it when the report is opened - or loaded
Not on a command button that opens the report
Is the code similar?
Do you know what I'm trying to do?
Stealth_2222
Ok, this is what I tried and it appears to be working
Does this look good?
CODE

Private Sub Report_Open(Cancel As Integer)
DoCmd.ApplyFilter , "[Date Submitted]" < "#" & DateAdd("d", -90, Date) & "#"
End Sub
Stealth_2222
[censored], nvm
This code doesn't work
Got anything else for me?
SerranoG
Watch your quotation marks. Too many.
!--c1-->
CODE
Me.Filter = "[Date Submitted] < #" & DateAdd("d", -90, Date) & "#"
Me.FilterOn = True
accesshawaii
Yea, try this.
CODE
Private Sub Report_Open(Cancel As Integer)
Dim strFilter As String
Me.Report.FilterOn = True
Me.Report.Filter = "[Date Submitted] < #" & DateAdd("d", -90, Date) & "#"
End Sub
accesshawaii
I see Greg and I posted the same code...Great minds think alike sad.gif LOL
Stealth_2222
Thanks guys
Appreciate it
Stealth_2222
It's not working??
Odon't know what this is doing...it filters out some of the records, but ones that aren't older than 90 days
That would be wrong with it?
I need the records to be filtered out based on today's date...so is there something I need to change in the code to make it work for that?
Please help...
Stealth_2222
I tried this, but it didn't work either
CODE

Me.Filter = "[Date Submitted] > Day(Now())-90"
Stealth_2222
Tried this, it does the same thing your code did
Seems to be filtering something, but I can't figure out what?
It's acting really strangely
CODE
Me.Filter = "[Date Submitted] > DateAdd ('D',-90,Date())"
Stealth_2222
haha, I wasn't thinking straight
it is working
Oalso did this code, which would work to, using the past 3 months instead of past 90 days
CODE

Me.Filter = "[Date Submitted] < DateAdd ('M',-3,Date())"
jurotek
Hi,
This should work
SELECT ToolingRequisitions.[ToolRequisitionID], ToolingRequisitions.DateSubmited
FROM ToolingRequisitions
WHERE (((ToolingRequisitions.DateSubmited) Is Not Null And (ToolingRequisitions.DateSubmited)
Between DateAdd("d",-90,Date()) And Date()));
Stealth_2222
Cool thanks!
jurotek
YW,
Glad we could all help
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.