Pass more than 1 value in strWhere

[Jorge]

Hello,
I'm using the code from
http://www.mvps.org/access/reports/rpt0002.htm to print
only the part of the report related with a record in a
form.
It works great but I need to print some records where I
have to pass more than one field to the report to
identify the record.
Could anybody help me with the syntax? I tried a couple
of things and it doesn't work
Thanks!!!
Jorge

 

[Marshall Barton]

Hello,
I'm using the code from
http://www.mvps.org/access/reports/rpt0002.htm to print
only the part of the report related with a record in a
form.
It works great but I need to print some records where I
have to pass more than one field to the report to
identify the record.

Use AND to combine the two conditions:

strWhere = "[RunID]=" & me!RunID & " And [fld2]=" & Me.val2

 

[Guest]

Thanks Marsh,
I've already tried the same idea with a syntax more
complicated but I get an error.

What I tried:

Dim strWhere1 As String
Dim strWhere2 As String
Dim strWhere As String

strDocName = "House Styles MAIN"
strWhere1 = "[LotNumber]=" & Me!LotNumber
strWhere2 = "[SubdivisionName]=" & Me!
SubdivisionName
strWhere = strWhere1 + " AND " + strWhere2


But with either expressions I get exactly the same error
when I click on the command button from the Form:

Syntax error (missing operator) in query
expression '([LotNumber]=1 And [SubdivisionName]=Pines
Island)'.

If I open the report, both fields are there and have
valid values.
I don't understand what is the "missing operator" access
is asking me about. Should I include any INNER JOIN since
both fields are comming from different tables? The report
is based on a query that include both fields, and all the
other values depend on these 2 fields...

Thanks,
Jorge

-----Original Message-----

Hello,
I'm using the code from
http://www.mvps.org/access/reports/rpt0002.htm to print
only the part of the report related with a record in a
form.
It works great but I need to print some records where I
have to pass more than one field to the report to
identify the record.

Use AND to combine the two conditions:

strWhere = "[RunID]=" & me!RunID & " And [fld2]=" & Me.val2

 

[Marshall Barton]

I've already tried the same idea with a syntax more
complicated but I get an error.

What I tried:
Dim strWhere1 As String
Dim strWhere2 As String
Dim strWhere As String

strDocName = "House Styles MAIN"
strWhere1 = "[LotNumber]=" & Me!LotNumber
strWhere2 = "[SubdivisionName]=" & Me!
SubdivisionName
strWhere = strWhere1 + " AND " + strWhere2


But with either expressions I get exactly the same error
when I click on the command button from the Form:

Syntax error (missing operator) in query
expression '([LotNumber]=1 And [SubdivisionName]=Pines
Island)'.

If I open the report, both fields are there and have
valid values.
I don't understand what is the "missing operator" access
is asking me about. Should I include any INNER JOIN since
both fields are comming from different tables? The report
is based on a query that include both fields, and all the
other values depend on these 2 fields...

You'll get that error if SubdivisionName is a text field.
If that's the case, then its value must be in quotes:

strWhere2 = "[SubdivisionName]=""" _
& Me!SubdivisionName & """"

 

[Guest]

Like a magic recipe!! GREAT!! THANK YOU!!!
If you don't mind I would like to mention your help in my
paper. I'm building the database for my project at
college.
Have a wonderful weekend
Jorge.

-----Original Message-----

I've already tried the same idea with a syntax more
complicated but I get an error.

What I tried:
Dim strWhere1 As String
Dim strWhere2 As String
Dim strWhere As String

strDocName = "House Styles MAIN"
strWhere1 = "[LotNumber]=" & Me!LotNumber
strWhere2 = "[SubdivisionName]=" & Me!
SubdivisionName
strWhere = strWhere1 + " AND " + strWhere2


But with either expressions I get exactly the same error
when I click on the command button from the Form:

Syntax error (missing operator) in query
expression '([LotNumber]=1 And [SubdivisionName]=Pines
Island)'.

If I open the report, both fields are there and have
valid values.
I don't understand what is the "missing operator" access
is asking me about. Should I include any INNER JOIN since
both fields are comming from different tables? The report
is based on a query that include both fields, and all the
other values depend on these 2 fields...

You'll get that error if SubdivisionName is a text field.
If that's the case, then its value must be in quotes:

strWhere2 = "[SubdivisionName]=""" _
& Me!SubdivisionName & """"

 

[Marshall Barton]

You're welcome. And you're welcome to mention it, but a
little syntax nudge hardly seems like it's worth any credit.
--
Marsh
MVP [MS Access]

Like a magic recipe!! GREAT!! THANK YOU!!!
If you don't mind I would like to mention your help in my
paper. I'm building the database for my project at
college.
Have a wonderful weekend
Jorge.

-----Original Message-----

I've already tried the same idea with a syntax more
complicated but I get an error.

What I tried:
Dim strWhere1 As String
Dim strWhere2 As String
Dim strWhere As String

strDocName = "House Styles MAIN"
strWhere1 = "[LotNumber]=" & Me!LotNumber
strWhere2 = "[SubdivisionName]=" & Me!
SubdivisionName
strWhere = strWhere1 + " AND " + strWhere2


But with either expressions I get exactly the same error
when I click on the command button from the Form:

Syntax error (missing operator) in query
expression '([LotNumber]=1 And [SubdivisionName]=Pines
Island)'.

If I open the report, both fields are there and have
valid values.
I don't understand what is the "missing operator" access
is asking me about. Should I include any INNER JOIN since
both fields are comming from different tables? The report
is based on a query that include both fields, and all the
other values depend on these 2 fields...

You'll get that error if SubdivisionName is a text field.
If that's the case, then its value must be in quotes:

strWhere2 = "[SubdivisionName]=""" _
& Me!SubdivisionName & """"