Concatenation

[Adrian Parker]

I would like to do a query of two fields (firstName and lastName) and
compare the concatenated value of these two fields (with a signle spaces
added between them) to a string. Can this be achieved in Access?

From VB I have tried the following:

adorecordset.Open "select * from students, instructors " & "WHERE
textcat(textcat(instructors.[firstName], ' '), instructors.[lastName]) = '"
& cmbInstructor.Text & "' AND " & students.instructorCode =
instructors.instructorCode", db, adOpenStatic, adLockPessimistic

It works under SQL I'm told, but the textcat() function does not seem
available in my environment.

What are my options?


Adrian

 

[Adrian Parker]

I would like to do a query of two fields (firstName and lastName) and
compare the concatenated value of these two fields (with a signle spaces
added between them) to a string. Can this be achieved in Access?

From VB I have tried the following:

adorecordset.Open "select * from students, instructors " & "WHERE
textcat(textcat(instructors.[firstName], ' '), instructors.[lastName]) = '"
& cmbInstructor.Text & "' AND " & students.instructorCode =
instructors.instructorCode", db, adOpenStatic, adLockPessimistic

It works under SQL I'm told, but the textcat() function does not seem
available in my environment.

What are my options?

I know this works, but is it the best option:

adorecordset.Open "select * from students, instructors " _
& "WHERE instructors.[firstName] & ' ' & instructors.[lastName] = '"
& cmbInstructor.Text & "' AND " _
& "students.instructorCode = instructors.instructorCode", _
db, adOpenStatic, adLockPessimistic



Adrian