OpenReport Syntax error

[dgmdvm]

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):

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:

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

[ssanfu]

You are missing an ampersand

"totals.Business_Type = '" & BusTypeLst.column(1) & "'"

In the query "Contact_list_query", how many columns are named "Business_Type"? I would expect to see

DoCmd.OpenReport rptname, acViewPreview, "Contact_list_query", "Business_Type = '" & BusTypeLst.column(1) & "'", acWindowNormal, sortype

[dgmdvm]

Never mind. This works:

"totals.Business_Type ='" & Me.BusTypeLst.Column(1) & "'"

I had to get up from the table and clear my head for a minute.

[dgmdvm]

In answer to your question contact_list _query has multiple alias' in it and "totals" is one of the the final tables in the query. I copied the expression from the SQL sheet figuring it would work ok.