In my database I have a form with a series of option buttons to modify the contents of a report. One of the options is based on a combo box BusTypeLst which lists a series of business types. The idea is that when the report opens the selected item in the combo box will become the "criteria" for the openreport command. I am getting a syntax error in my statement. The Access syntax error lists the portion of the statement 'totals.Business_type=' as the problem.
This works (using one of the items in the combo box and inserting it into the statement):
Code:
Case Me.Option7.Value = True
rptname = "Contacts List"
prnttype = "Document"
DoCmd.OpenReport rptname, acViewPreview, "Contact_list_query", "totals.Business_Type ='Advertising'", acWindowNormal, sortype
Set rpt = Reports(rptname)
Set rpt.Printer = Application.Printers(Selprnt(prnttype))
rpt.Printer.Orientation = acPRORLandscape
DoCmd.Close acQuery, "Contact_List_query", acSaveNo
This does not:
Code:
Case Me.Option7.Value = True
rptname = "Contacts List"
prnttype = "Document"
DoCmd.OpenReport rptname, acViewPreview, "Contact_list_query", "totals.Business_Type = " & BusTypeLst.column(1), acWindowNormal, sortype
Set rpt = Reports(rptname)
Set rpt.Printer = Application.Printers(Selprnt(prnttype))
rpt.Printer.Orientation = acPRORLandscape
DoCmd.Close acQuery, "Contact_List_query", acSaveNo
The combobox has 2 columns, an ID field and the Business_type field.
I've tried multiple syntax possibilities including this one: "totals.Business_Type = '" & BusTypeLst.column(1)"'" as well as setting the value of BusTypeLst.column(1) to a string variable.
All my other option buttons on the form, which are based on simple strings, work fine and I can get the correct report generated.
I will appreciate any help you can give me.
Thanks