checking whether field in table has space or not (1 Viewer)

johnmerlino

Registered User.
Local time
Today, 10:23
Joined
Oct 14, 2010
Messages
81
Hey all,

I have a table called FinalForgotten which contains this:
aname (field)
Smith John
Smith,John
Smith,Jane

I have a table called defense_final which contains this:
last_name (field) first_name (field) middle_initial (field)
Smith John T
Smith,John Ruby
Smith,Jane Lis
Marie,Ann Sam
Simms,Bob
Kent,Joe

I am trying to build table that checks the first and last names of the two tables (even though FinalForgotten only has a single field by default which contains both the first and last names) and if there is a match, then return the first and last and middle initial into a new table:

FinalForgottenWithMiddle
Smith John T
Smith,John Ruby
Smith,Jane Lis


In order to do this, I have this query:

Code:
SELECT left([aname],
IIF(instr([aname], ",") = 0, InStr(1,[aname]," ")-1),InStr(1,[aname],",")-1) 
&  ", "  &
right([aname],
IIF(instr([aname], ",") = 0,Len(aname)-InStr(1,[aname]," "),Len(aname)-InStr(1,[aname],",")
&  " " &
defense_final.middle_initial AS fullname INTO FinalForgottenWithMiddle
FROM FinalForgotten INNER JOIN defense_final ON 
((right(FinalForgotten.aname,IIF(instr([aname], ",") =  0,Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname],"  ")),Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname],","))=defense_final.first_name))  
AND 
((left(FinalForgotten.aname,,IIF(instr([aname], ",") =  0,InStr(1,FinalForgotten.[aname],"  ")-1)),InStr(1,FinalForgotten.[aname],",")-1))=defense_final.last_name);

Problem is it gives a missing operator syntax error highlighting the "AS" clause. Not sure why and do you think there are other errors in this as well?

Thanks for response.
 

vbaInet

AWF VIP
Local time
Today, 17:23
Joined
Jan 22, 2010
Messages
26,374
I don't understand your second table, defense_final. Which field contains the comma and/or space? Is it the Surname field or the Firstname field?
 

johnmerlino

Registered User.
Local time
Today, 10:23
Joined
Oct 14, 2010
Messages
81
I don't understand your second table, defense_final. Which field contains the comma and/or space? Is it the Surname field or the Firstname field?

Thanks for response. I have to check if finalforgotten has a comma or not"

Smith John
Smith,John
Smith,Jane

Because if I don't, then i will get an "invalid procedure call" error.
 

vbaInet

AWF VIP
Local time
Today, 17:23
Joined
Jan 22, 2010
Messages
26,374
You didn't understand my question. How is the Surname field separate from the Firstname field in the defense table?

Use a color code like this to explain:
Surname (field) Firstname (field) Middle_Initials (field)
Smith, John Ruby

From that I know that Smith, (with the comma at the end) is in the Surname field in your defense table.
 

johnmerlino

Registered User.
Local time
Today, 10:23
Joined
Oct 14, 2010
Messages
81
FinalForgotten only contains one field called aname. The field could either look like Smith John or Smith,John. So both last and first name are in same field and delimited by either space or comma. The defense field contains three fields: first_name,last_name,middle_initial. The first_name field will contain data that matches exactly a piece a data IN aname field (e.g. John). And the last_name field will contain data that matches exactly a piece of data IN aname field (e.g. Smith). I'm trying to get all the FinalForgotten aname records with a middle initial into a new table (e.g. Smith,John S). The defense table is what has this middle initial.

This would work:

Code:
SELECT left([aname],InStr(1,[aname],",")-1) & " "& right([aname],Len(aname)-InStr(1,[aname],",")) & " "& summary_judgment.middle_initial AS fullnameINTO FinalForgottenWithMiddle FROM FinalForgotten INNER JOIN summary_judgment ON((left(FinalForgotten.aname,InStr(1,FinalForgotten.[aname],",")-1))=summary_judgment.last_name) AND((right(FinalForgotten.aname,Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname],","))=summary_judgment.first_name));
But it will return "invalid procedure call" should FinalForgotten contain a field that doesn't have a comma like:
Smith John.

Hence, to address this, I tried to factor whether a comma was in the field or not:

Code:
SELECT left([aname],
IIF(instr([aname], ",") = 0, InStr(1,[aname]," ")-1),InStr(1,[aname],",")-1) 
&  ", "  &
right([aname],
IIF(instr([aname], ",") = 0,Len(aname)-InStr(1,[aname]," "),Len(aname)-InStr(1,[aname],",")
&  " " &
defense_final.middle_initial AS fullname INTO FinalForgottenWithMiddle
FROM FinalForgotten INNER JOIN defense_final ON 
((right(FinalForgotten.aname,IIF(instr([aname], ",") = 0,Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname]," ")),Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname],","))=defense_final.first_name)) 
AND 
((left(FinalForgotten.aname,,IIF(instr([aname], ",") = 0,InStr(1,FinalForgotten.[aname]," ")-1)),InStr(1,FinalForgotten.[aname],",")-1))=defense_final.last_name);
This gives me a "missing operator syntax" error.

Thanks for response.
 
Last edited:

vbaInet

AWF VIP
Local time
Today, 17:23
Joined
Jan 22, 2010
Messages
26,374
I was going by the sample data you wrote in your first post for the fields in your defense table. You had commas in there which made it look as though the existing records in that table had commas. I understand your objective but your initial examples were incorrect. I suppose you wrote it just to separate the fields.

Anyway, I think you're going about this the wrong way. Repetition of data defeats the whole purpose of a relational database. You have two tables of customer details and if you continue in this fashion you will encounter much bigger problems. Normalize your data. Also, why don't you have a CustomerID field?
 

johnmerlino

Registered User.
Local time
Today, 10:23
Joined
Oct 14, 2010
Messages
81
I do have normalized data, but this query is for data that couldn't matched due to differences between database data and data I need to compare it against, which is unpredictable. Sometimes data cannot be normalized in certain situations, especially with name variability. These are one of those situations
 

vbaInet

AWF VIP
Local time
Today, 17:23
Joined
Jan 22, 2010
Messages
26,374
Is the data coming from somewhere else? I thought you said they were two tables in your db? It will continue to be inconsistent if you keep running these updates and for sure you will miss out some data.

Do you have surnames or firstnames that are double? That is, Martin Mac Donald for example where Mac Donald is the surname?
 

johnmerlino

Registered User.
Local time
Today, 10:23
Joined
Oct 14, 2010
Messages
81
I do an initial query. It finds 95% of matches. However, the reason it doesn't find the 5% is mostly because of database contains middle initial and external data sheet doesn't or database contains middle initial and external data sheet contains entire middle name spelled out. So I remove the middle intiials from both and compare them. Now for the remainders, I want to bring back the middle initial so that the names can be manually searched. So I'm trying to bring back the middle initials here and I am getting syntax error with it. Do you know what is wrong with my query? It might just be a small error.

Thanks for response.
 

vbaInet

AWF VIP
Local time
Today, 17:23
Joined
Jan 22, 2010
Messages
26,374
I haven't scrutinised the whole code but I can already see that your second IIF() statement is missing a closing brace.

But note, the reason why I asked you about the double firstname or surname scenario is that your code will fail if it exists.
 

johnmerlino

Registered User.
Local time
Today, 10:23
Joined
Oct 14, 2010
Messages
81
Why would it fail? If there are matching first names and last names between the two sources, then it should return the same first and last name, with the addition of the middle initial. The chances of two names where first and last are the same would rarely happen and if it does, then I accept the failure, because it would be very rare.
 

vbaInet

AWF VIP
Local time
Today, 17:23
Joined
Jan 22, 2010
Messages
26,374
Using my previous example,

FirstName - Martin
Surname - Mac Donald

It would look like this in your FinalForgotten table Mac Donald, Martin. Instr() will find the first space which is between Mac and Donald.
 

johnmerlino

Registered User.
Local time
Today, 10:23
Joined
Oct 14, 2010
Messages
81
Yeah but there's no other way around it. So I would just have to be aware of that.
 

vbaInet

AWF VIP
Local time
Today, 17:23
Joined
Jan 22, 2010
Messages
26,374
Use a function for your check and call the function in your query. You can use the Split() function check how many names are returned after a split. Look into that.
 

johnmerlino

Registered User.
Local time
Today, 10:23
Joined
Oct 14, 2010
Messages
81
Out of curiousity, do you know why it still highlights AS even when I add the closing brace to the second IIF? It says missing operator in query expression. It looks to me no oeprator is missing.
 

vbaInet

AWF VIP
Local time
Today, 17:23
Joined
Jan 22, 2010
Messages
26,374
The other lines are fairly long and hard to detect by just looking at them. Either you break down your statements and piece them together one by one or download an sql editor.
 

Users who are viewing this thread

Top Bottom