I am hoping someone can help me with a problem I've been having. I am trying to make a concatenation function to use in a query. This is my first big access project and I'm really not the best at coding.

The code I have so far started with someone else's concatenation function which I tried to adapt to my needs. A few notes:


ID1 and ID2 are numeric ID numbers and are not strings.
If I take out the where clause, the rest of the function works just fine. I just don't get the results I want of course.
I've checked and rechecked the parameters in the query to make sure there aren't any misspellings.
I'm very new to this and might very well have made a huge mistake somewhere.

As the code is now, I'm getting a data type mismatch which I think is because the ID values aren't strings. However, I don't know the correct syntax and I haven't been able to find a syntax that will work in my trips around the net. As I try to fix my code I've been going between the errors 'data type mismatch in criteria expression' and 'too few parameters.'

Here is the beginning of my function:

Public Function Concat(ID1, ID2, ConField, Match1, Match2) As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL As String
Dim vConc As Variant

Set db = CurrentDb
vConc = ""

SQL = "SELECT " & ConField & " As Conc" & _
" FROM tblPhysCreds INNER JOIN tblCreds" & _
" ON " & Match1 & " = " & Match2 & "" & _
" WHERE " & ID1 & " = '" & ID2 & "'"

Set rs = db.OpenRecordset(SQL)


So I'm pretty sure the issue is with the syntax of the where clause and maybe the way I handle my parameters. Any help or advice would be appreciated and I'll gladly answer any questions about my database structure.

1. You don't need the empty string on line 3 after Match2

2. You don't need the single quotes around ID2 if ID2 is numeric

SQL = "SELECT " & ConField & " As Conc" & _
" FROM tblPhysCreds INNER JOIN tblCreds" & _
" ON " & Match1 & " = " & Match2 & _
" WHERE " & ID1 & " = " & ID2

I've changed my code but unfortunately I'm still getting an error:
"Too few parameters. Expected 1."

I am wondering if my parameters are getting interpreted by the where clause correctly. Is there a way to check the current value of my parameters? I have tried to show them with a message box, but only get the name of the parameter and not the current value. Like I said, I'm not too great with access yet.

1. You don't need the empty string on line 3 after Match2

2. You don't need the single quotes around ID2 if ID2 is numeric

SQL = "SELECT " & ConField & " As Conc" & _
" FROM tblPhysCreds INNER JOIN tblCreds" & _
" ON " & Match1 & " = " & Match2 & _
" WHERE " & ID1 & " = " & ID2



Shouldn't that be " ON Match1 = Match2"?

I would think that in the case where [ Match1=Match2=1234" ], the original statement would interpret to " ON 1234 = 1234"

Note: The same could also be said about ID1 and ID2

Is Match1 and Match2 numeric or text? It DOES make a difference in the syntax.

not looked at eveything else, but this cant be right

WHERE " & ID1 & " = " & ID2

you are passing ID1 and ID2 as parameters - normally a where clause would test a tables field value, and therefore the first part must be a string (field name)

ie "where [idi] = " & id2

- but if so what exactly is the purpose of passing id1 and id2 into this function as
parameters

- not sure about this, but there may also be a problem in that ALL your parameters arent typed, therefore access will treat them as variants which in itself MIGHT give a type mismatch - ie a variant is not exactly a long

I guess I should be a little more specific about what I'm trying to do. I'm working on a database of physicians and for this particular function, I'm trying to concatenate their list of credentials. I have a table of physicians (tblPhysicians), a table of credentials (tblCreds) and a linking table (tblPhysCreds).

My parameters are:
ConField - Text field to be concatenated from the credentials table, tblCreds.
Match 1 - Credential ID number from the linking table, tblPhysCreds.
Match 2 - Credential ID number from the credentials table, tblCreds.
ID1 - Physician ID number from the linking table, tblPhysCreds.
ID2 - Physician ID number from the physicians table, tblPhysicians, that I assumed would be passed in through the parameters in the query.

So I guess something is very messed up there but I'm not familiar enough with access or coding to know what. Like I said before, the function seems to work without the where clause, but then every physician has every credential since the data isn't getting filtered by the where clause.

And thanks for the help and suggestions given so far, I definitely do appreciate it.

I'm also open to any suggestions of other methods I could use to accomplish this.

Well, I ended up making a lot of changes and I simplified my code quite a bit by calling to a separate query. I also found out I had been using some wrong punctuation in the query that called to the function. But now I've gotten it working. Thanks to everyone who gave suggestions.