SQL Record Set Combo Box Problem

[Winger]

I'm using an SQL statement to define a RecordSet which is based on the value
of a Combo Box.
The code works fine when I use an absolute value for the Researcher Name
i.e. "Dave"

" WHERE ((([tblResearchers].[ResName])='Dave'))"

But when I change this to look at the value of the Combo Box..

" WHERE ((([tblResearchers].[ResName])=Forms![Form2]![ComboResName]))"

I think I'm referencing the value of the combo box incorreclty.

Any pointers / help would be greatly appreciated.

Many thanks

Winger

 

[Douglas J. Steele]

You want the value of the combo box to be in your WHERE clause, not its
name. That means you need to put it outside of the quotes. However, since
it's a text value, you need to enclose its value in quotes:

" WHERE ((([tblResearchers].[ResName])=" & Chr$(34) &
Forms![Form2]![ComboResName] & Chr$(34) & "))"

(Chr$(34) is a double quote)

 

[Winger]

Doug,

I posted my question late last night as i had given up in frustration. And I
woke to this morning to find your reply..and it worked great!

Many thanks...much appreciated.

Winger

You want the value of the combo box to be in your WHERE clause, not its
name. That means you need to put it outside of the quotes. However, since
it's a text value, you need to enclose its value in quotes:

" WHERE ((([tblResearchers].[ResName])=" & Chr$(34) &
Forms![Form2]![ComboResName] & Chr$(34) & "))"

(Chr$(34) is a double quote)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm using an SQL statement to define a RecordSet which is based on the
value
of a Combo Box.
The code works fine when I use an absolute value for the Researcher Name
i.e. "Dave"

" WHERE ((([tblResearchers].[ResName])='Dave'))"

But when I change this to look at the value of the Combo Box..

" WHERE ((([tblResearchers].[ResName])=Forms![Form2]![ComboResName]))"

I think I'm referencing the value of the combo box incorreclty.

Any pointers / help would be greatly appreciated.

Many thanks

Winger