Simple String Help....

[robercs]

I am a new programmer and have been wrestling with a problem and need a little help. I am building a simple String the allows the user to search a database based on 3 parameters. The user selects the parameter they need (via coombo box) and then enters start and end dates into test boxes. Once the user hits "Generate Report" the data matching the criteria is print previewed in a simple report. The code is shown below.

When I hit the "Generate Report" button a text box pops up asking the user to Enter a Parameter Value and it defaults to Portable (one of my combo pox selections). I don't understand why this box pops up. I don't ask for a text box in the code. The date fields sort OK and the report pops up fine as well. If I actually type in the criteria "portable" it will work as needed. Where does this popup box come from???


Sub cmdGenerateReport_Click()
Dim StrWhere As String
Dim strDates As String
Dim StrDocName As String
'Dim blnTrim As Boolean


If Me.cboTestType = "Portable" Then
StrWhere = StrWhere & "[Test Type]=" & Me.cboTestType & " And "
'blnTrim = True

ElseIf Me.cboTestType = "Van" Then
StrWhere = StrWhere & "[Test Type]=" & Me.cboTestType & " And "

ElseIf Me.cboTestType = "none" Then
StrWhere = StrWhere & "[Test Type]=" & Me.cboTestType & " And "
End If

If IsNull(Me.txtStartDAte) And Me.txtStartDAte = "" Then
If Not IsNull(Me.txtEndDate) And Me.txtEndDate <> "" Then
StrWhere = StrWhere & "[finish (date)] <=" & Me.txtStartDAte & "#"
'blnTrim = False
End If

Else
If (Not IsNull(Me.txtStartDAte) And Me.txtStartDAte <> "") And (Not IsNull(Me.txtStartDAte) Or Me.txtStartDAte <> "") Then
StrWhere = StrWhere & "[Finish (date)] Between #" & Me.txtEndDate & "# AND #" & Me.txtStartDAte & "#"
'blnTrim = False

End If
'If blnTrim Then
'StrWhere = Left(StrWhere, Len(StrWhere) - 5)
'End If
StrDocName = "General Report"
DoCmd.OpenReport StrDocName, acViewPreview, , StrWhere
End If
End Sub


Thanks in advance for any help. Much appreciated!!

[geekgirlau]

Just a quick correction - use nz to streamline your test, and I think one of these was supposed to test the end date ...


Else
If nz(Me.txtStartDAte,"") <> "") And nz(Me.txtEndDAte,"") <> "" Then
StrWhere = StrWhere & "[Finish (date)] Between #" & Me.txtEndDate & "# AND #" & Me.txtStartDAte & "#"
'blnTrim = False

End If


With the pop-up it sounds like your "Where" is not quite correct. If you add

Debug.Print StrWhere

before you open the report, you should be able to see whether there's a syntax error in there somewhere (it might be something as simple as a typo in a field name).

[robercs]

Thank you geekgirlau the nz works great! When you say, "before you open the report, you should be able to see whether there's a syntax error in there somewhere (it might be something as simple as a typo in a field name)." how do I check for errors? What am I looking for?

Sorry, I'm a newbie....Thanks again.

[geekgirlau]

After you add the line

Debug.Print StrWhere

set a breakpoint. You do this by pressing [F9] while on the Debug line. When you run your code it will stop at this point.

Then you can step through the code one line at a time by pressing [F8]. To see what text has been stored in the variable StrWhere, press [Ctrl-G] to open the Immediate window. The Debug command will print out whatever is in your string variable in this window.

This can be very handy when you are building SQL strings in code. You can copy text from the immediate window and paste into the filter of your report.

[robercs]

Thanks geekgirlau! I put the Debug line at the top of the code. Once I exectute the code I can see what is stored in the the String in the Immediate window just as you mentioned. I can F8 through the lines to see what is going on. Each line is highlighted yellow as it is stepped through as you know. Once I get to the end of the code the pop up box appears but there is no lines highlighted that tell it to do so. This pop up box is the only thing holding me up at this point. I didn't write any code to tell it to pop up so I don't understand where it is coming from!

[geekgirlau]

This just means that something is amiss with the report.

First, try opening the report manually. If you see a popup, there is a missing or invalid field somewhere.

If there is no popup when you open the report, the problem is the filter that your code constructs. Copy the filter text from the immediate window and try to filter the report manually using that text. This should show you where the problem is in your filter.