Problem with using query results

[Andrew Slentz]

I have an access 2002 database which I am having trouble with.
Basically I run a select query which results in a result of "yes" or
"no". I need that result to be put into an update query. I am lost on
this.

Here's the VBA:

Dim strSQL As String
Dim strSQL2 As String

strSQL = "SELECT Hosp_Network.[HUM/CHC_Par]FROM Hosp_Network INNER
JOIN Hosp_Name " & _
"ON Hosp_Network.HumID = Hosp_Name.HumID WHERE (((Hosp_Name.Name)='"
& List71 & "'))"

strSQL2 = "Update testfile set HUM/CHC_Par = '" & strSQL & "' where
HospitalName = ' " & Me.Combo0.Value & " ' "
DoCmd.RunSQL strSQL2

I checked the queries and they both work but there's something simple
I'm missing. The table "hosp_network" contains the field "HUM/CHC Par"
which is either empty or "Yes".

Thanks a lot!!! I am stuck on this one.

Andrew

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Darryl Kerkeslager]

The Query Design view in Access is a wonderful thing. I'd copy this SQL
into a query form, put some dummy values in for List71 and Me.Combo0.Value.

If the update query works there, then the problem may be with the contents
of the listBox or comboBox.

"SELECT [HUM/CHC_Par] FROM Hosp_Network INNER JOIN Hosp_Name ON
Hosp_Network.HumID = Hosp_Name.HumID WHERE Hosp_Name.Name = ' " & List71 &
" ' "

"UPDATE testfile SET [HUM/CHC_Par] = ' " & strSQL & " ' WHERE HospitalName =
' " & Me.Combo0.Value & " ' "


Darryl Kerkeslager
"Andrew Slentz" <aj******@yahoo.com> wrote:

strSQL = "SELECT Hosp_Network.[HUM/CHC_Par]FROM Hosp_Network INNER
JOIN Hosp_Name " & _
"ON Hosp_Network.HumID = Hosp_Name.HumID WHERE (((Hosp_Name.Name)='"
& List71 & "'))"

strSQL2 = "Update testfile set HUM/CHC_Par = '" & strSQL & "' where
HospitalName = ' " & Me.Combo0.Value & " ' "