report filter help

[anewuser]

Hi All,

Firstly I am working in access 2010. I am trying to create an event (activated by clicking a button on a form) where by a filtered report will be opened. The form in question is bound to the table where the results will be coming from and contains an option group with three options: 1 month (value = 1), 2 months (value = 2) and 3 months (value = 3).

So we're clear the report I am trying to generate will show renewal dates within 1, 2, or 3 months from the current date.

I have the current code:

Option Compare Database
 
Private Sub Find_record_Click()
Dim strDate As Date
 
Select Case Me.Frame15.Value
Case Is = 1
strDate = DateAdd("d", 30, Now())
If Me.Check24 = "0" Then
Call DoCmd.OpenReport("SFIND report", acViewReport, , "Renewal Date <" & strDate)
ElseIf Me.Check24 = "-1" Then
Call DoCmd.OpenReport("SFIND Report2", acViewReport, , "Renewal Date <" & strDate)
End If
Case Is = 2
strDate = DateAdd("d", 60, Now())
If Me.Check24 = "0" Then
Call DoCmd.OpenReport("SFIND report", acViewReport, , "Renewal Date <" & strDate)
ElseIf Me.Check24 = "-1" Then
Call DoCmd.OpenReport("SFIND Report2", acViewReport, , "Renewal Date <" & strDate)
End If
Case Is = 3
strDate = DateAdd("d", 90, Now())
If Me.Check24 = "0" Then
Call DoCmd.OpenReport("SFIND report", acViewReport, , "Renewal Date <" & strDate)
ElseIf Me.Check24 = "-1" Then
Call DoCmd.OpenReport("SFIND Report2", acViewReport, , "Renewal Date <" & strDate)
End If
End Select
End Sub

It is compiled and does work if I take out the report filter. I have also done a debug.print and it is selecting the correct case depending on which option is selected. But as soon as I put the filter in the report won't open. I feel that the answer is fairly simple but I have tried a number of times with various strings and I can't get it to work.

Thanks in advance

[TheSmileyCoder]

Replace

"Renewal Date <" & strDate)

with

"Renewal Date <#" & strDate & "#")

Just like " is used to indicate start and end of a string, the # is used to indicate start and end of date.

Otherwise the date:
2013-02-07 will be conceived as 2013 minus 2 minus 7.

[zmbd]

DoCmd.OpenReport("SFIND report", acViewReport, , "Renewal Date <" & strDate)

See this last part "Renewal Date <" & strDate
this is a petpeeve of mine as it is not straight forward to troubleshoot. What you need is a string expression that's a valid SQL WHERE clause without the word WHERE. With that space in there between "Renewal" and "Date", you might be causing yourself heartburn... try
"[Renewal Date] < #" & strDate & "#"
This is one reason I never ever use spaces or anything except the underscore and alphanumerics in my field, file, and other names. You will also need the "#" for date delimination/.

[Seth Schrock]

It would be nice to have the error message along with the number. However, I'm going to make a guess that you need brackets around the field name in your filter. So line 10 would be:

Call DoCmd.OpenReport("SFIND report", acViewReport, , "[Renewal Date] <" & strDate)

I know that in a query, fields with spaces must be inside brackets and I'm almost positive that WHERE arguments to open forms or reports follows the same requirement.

For future reference, if you get an error message, always provide that in your original question along with the error number. It provides some very necessary information for those trying to help you.

Edit: And I missed (again) the # delimiter for dates. Smiley and Z probably have the answer.

[zmbd]

hey... everyone go back to sleep here... :)
Opps... there's my timer, back to the lab!

[NeoPa]

See these links :
How to ask "good" questions -- READ BEFORE SUBMITTING A QUESTION!.
Before Posting (VBA or SQL) Code.
Literal DateTimes and Their Delimiters (#).

Using the default format of a date is not at all recommended when putting it into a SQL string. Not only are the delimiters required, but the format is also very important.