Query using concatenated fields bombs

[1uglyMug]

Hi All!
I was in a car accident and laid up for quite a while so I'm just getting back in.


I inherited a databae from a developer (since gone) and there are a lot of tables where a single field contains a first name,a space, and last name and then others which are in 2 fields. I know this is not cool but there is so much of it. As example, there is a table named tblContacts which has 3 fields, ContactID, FirstName, LastName. He was trying to find duplicite records and had this SQL. I think he was working on a part where the strContactName was like "joe smith".

He had it setup like this:

strSQL = "SELECT ContactID, FirstName & " " & LastName AS rsContactName FROM tblContacts Where rsContactName = '" & strContactName & "'"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

rs.FindFirst "[rsContactName] = '" & sContactName & "'"

He said he didnt get errors but it didnt run, just stayed on the first record. I've look all over, trieed different search strings, etc., but nothing quite like this. Any ideas why it wont run? Thanks in advance.

 

[June7]

The recordset is already restricted by criteria of strContactName. If sContactName has the same value, FindFirst accomplishes nothing because of course the first record has matching value.

 

[arnelgp]

firstly you do not need to use the FindFirst method of the recordset since you already had it Filtered by strContactName.

second, which one is correct "strContactName" or "sContactName". replace the other one with correct variable name on strSQL variable.

 

[1uglyMug]

oh nuts!
june7 and arnelgp youre both spot on. when i made the changes it works like a charm. much obliged to you both

 

[arnelgp]

sometimes it happens.