Prompt box on where statement for docmd.openreport

[chris.sommerville]

Hi All,

I have a form with a command button that has 2 combo boxes beside it.

The 2 comboboxes and named cmbYear (number field from a query) and
cmbMonth( text field from a month table tblMonth).

The coding for the command button is

Private Sub Command5_Click()

DoCmd.OpenReport "2007_SWR Query", acViewPreview, , "[Costing Month]
='" & Me.cmbMonth & "' AND [Year] = " & Me.cmbYear

End Sub

Issue I am having is that it will accept the cmbmonth value but
prompts me for the year value.

If I cancel the prompt and debug the line, on mouse over the correct
value for me.cmbyear is displayed.

If I don't cancel the prompt and put in the correct year the
docmd.openreport works fine.

Any ideas of why it isn't accepting the Year value in the WHERE?

Regards

Chris Sommerville

 

[Allen Browne]

Try something like this:

Dim strWhere As String
If IsNull(Me.cmbMonth) Or Not IsNumeric(Me.cmbYear) Then
MsgBox "Enter a valid month and year"
Else
strWhere = "([Costing Month] =""" & Me.cmbMonth & _
""") AND ([Table1].[Year] = " & Me.cmbYear & ")"
'Debug.Print strWhere
DoCmd.OpenReport "2007_SWR Query", acViewPreview, , strWhere
End If

If the Year field is an alias, try a different name, e.g. TheYear. Access
can get confused with the name Year.

If you still have problems, remove the single quote from the debug.print
line, and see what comes out in the Immediate Window (Ctrl+G.)