VBA Where condition when the field itself is a variable (1 Viewer)

rodski

New member
Local time
Yesterday, 23:48
Joined
Feb 4, 2013
Messages
15
This one always gets me in a bind & I can't seem to find the answer anywhere... I would like to open a report where the field in the record source is a variable. The following works fine where the field is boolean:

DoCmd.OpenReport "rptReport", acViewPreview, , "[StringFieldName] = -1"

I would like the [StringFieldName] part of the where condition to be selected from a combobox on the same form. For example, Me.cboComboBox.Column(1). I never get the syntax right to get this to work, so would greatly appreciate any help! (I've tried many combinations but I am obviously misunderstanding the correct syntax).
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 23:48
Joined
Aug 30, 2003
Messages
35,685
Untested, but try

DoCmd.OpenReport "rptReport", acViewPreview, , Me.cboComboBox.Column(1) & " = -1"
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:48
Joined
Feb 19, 2002
Messages
35,925
If the table field is a string, it needs to be delimited with single or double quotes. if the table field is a date, it is delimited with the pound sign AND the string date MUST be in US date format - mm/dd/yyyy or the unambiguous - yyyy-mm-dd format. Numeric values are not delimited.

Typically you will get a long integer from a combo so that would be:

DoCmd.OpenReport "rptReport", acViewPreview, , "[StringFieldName] =" & Me.MyCombo ( unless you want something other than the bound column, in which case, you use the .Column property. Don't forget this is a zero based array so .Column(0) is the bound column, and .Column(1) is the second column of the RowSource and .Column(2) is the third column, etc.

for a date:

DoCmd.OpenReport "rptReport", acViewPreview, , "[StringFieldName] = " & Me.MyDate (If the control is bound to a date data type or has its date property set to some valid date format. Otherwise:
DoCmd.OpenReport "rptReport", acViewPreview, , "[StringFieldName] = #" & Format(Me.MyDate, "mm/dd/yyyy" & "#"

For a string:

DoCmd.OpenReport "rptReport", acViewPreview, , "[StringFieldName] = """ & Me.Sometextstring& """"

So put this together with pBaldy's suggestion if StringFieldName is actually a variable from a combo. Not sure why it would be the second column in the RowSource but whatever.
 

Users who are viewing this thread

Top Bottom