Type Mismatch

I am trying to match the first two character's in a forms textbox with a 2 digit text field from a table and I'm getting type mismatch.
What am I doing wrong?


Private Sub loadList()

Dim db As Database

Dim TemptxtPatNO As String * 2


Set db = CurrentDb


TemptxtPatNO = Mid(txtPatNo, 1, 2)


Combo69.RowSource = "SELECT qry_Clinic.[clinicID] & " - " & qry_Clinic.[ClinicName] AS IDName" & _
"FROM qry_Clinic & qry_Clinic" & _
"Where TemptxtPatNO = qry_Clinic.[ClinicAgencyID];"


End Sub

You're gonna need some quotes in there...
And you can't put the variable into the SQL string like that. Try this:

VB Code:

Private Sub loadList() 
 
  Dim db As Database 
  Dim TemptxtPatNO As String * 2 
 
  Set db = CurrentDb 
 
  TemptxtPatNO = Mid(txtPatNo, 1, 2) 
 
  Combo69.RowSource = "SELECT qry_Clinic.[clinicID] & " - " & qry_Clinic.[ClinicName] AS IDName" & _ 
  "FROM qry_Clinic & qry_Clinic" & _ 
  "Where qry_Clinic.[ClinicAgencyID] = '" & TemptxtPatNO & "';" 
 
End Sub

Still getting a type mismatch error

Just noticed - did you know you have qryClinic & qryClinic in your FROM clause?

If you are getting type mis match its because your types are wrong when matching (numbers to text and visa versa).

So Pilgrim Pete is right with this
Combo69.RowSource = "SELECT qry_Clinic.[clinicID] & " - " & qry_Clinic.[ClinicName] AS IDName FROM qry_Clinic Where qry_Clinic.[ClinicAgencyID] = '" & TemptxtPatNO & "';"

Except the single quotes aren't required (remove them)

Try that :)

Regards

Vince

You also need spaces at the end of the clauses.
I'll give it another try...

VB Code:

Private Sub loadList() 
 
  Dim db As Database 
  Dim TemptxtPatNO As String * 2 
 
  Set db = CurrentDb 
 
  TemptxtPatNO = Mid(txtPatNo, 1, 2) 
 
  Combo69.RowSource = "SELECT qry_Clinic.[clinicID] & " - " & qry_Clinic.[ClinicName] AS IDName " & _ 
  "FROM qry_Clinic " & _ 
  "Where qry_Clinic.[ClinicAgencyID] = '" & TemptxtPatNO & "';" 
 
End Sub

combo69 is a combobox on a form called addvisit. I declare the variable temptxtPatno as a string of length 2, and clinicAgencyId is in a table as a text field of size 2. I am still getting the type mismatch error with or without the single quotes. Please help

OK. I'm at a bit of a loss; let's take a step back.

First - when you go into debug mode which line are you on?
Next - does TemptxtPatNO definitely have a value when you get to the RowSource line?
Finally, if none of these makes the solution jump out at you, can you add these lines before the RowSource line:

VB Code:

dim strSQL as string
strSQL= "SELECT qry_Clinic.[clinicID] & " - " & qry_Clinic.[ClinicName] AS IDName " & _ 
  "FROM qry_Clinic " & _ 
  "Where qry_Clinic.[ClinicAgencyID] = '" & TemptxtPatNO & "';" 
debug.print strSQL

and post the resultant string?

You are using in Access, so use the query builder.
Then once you have the filter (put a test value in as the filter), change the query builder to SQL view. Copy this and paste into your code then change the where clause to use the combo box.
(see previous examples).

Since you are basing on a query the type may be changing, or there is a problem with the base query. Might be worth ensuring that works.

Other than that you'll have to list the tables (and fields, types etc) in the base query and the sql of the base query. Or compress and zip the mdb and post up here on a zip file (less than 100k though). We can d/l and look for you then.

Regards

Vince

Got it!!

You ever have one of those "can't see for looking" days??

It's your double quotes around the hyphen. Either double 'em up, or make 'em singles, like this:

VB Code:

"SELECT qry_Clinic.[clinicID] & "" - "" & qry_Clinic.[ClinicName] AS IDName " 
'or
"SELECT qry_Clinic.[clinicID] & ' - ' & qry_Clinic.[ClinicName] AS IDName "

Thanks.

No probs. Sory it took so long!