Convert Excel formula to Access query

[Stephen sjw_ost]

Hello all,
I need your help please.

I need help with converting the following 2 Excel formulas to an Access
Query formula.

=LEFT(C2,FIND(",",C2,1)-1)

=RIGHT(C2,LEN(C2)-FIND(",",C2,1))

The cell C2 would be referencing the Access table field IDName.

I am needing this so I can separate the Last name and First name into 2
separate fields in Access.

Thanks in advance,

 

[Marshall Barton]

I need help with converting the following 2 Excel formulas to an Access
Query formula.

=LEFT(C2,FIND(",",C2,1)-1)

=RIGHT(C2,LEN(C2)-FIND(",",C2,1))

The cell C2 would be referencing the Access table field IDName.

I am needing this so I can separate the Last name and First name into 2
separate fields in Access.

LastName: Left(IDName,InStr(",",IDName)-1)
FirstName: Mid(IDName,InStr(",",IDName)+1)

 

[Stephen sjw_ost]

Thank you for the quick response.

I tried to use the formulas provided but keep getting ERROR in the results.

What am I doing wrong?

 

[John Spencer]

I think Marshall reversed his arguments. Try
LastName: Left(IDName,InStr(IDName & ",",",")-1)
FirstName: Mid(IDName,InStr(IDName,",")+1)

I added a comma to the end of IDName when looking for it for LastName.
This is to take care of the possibility that IDName might contain a
value with no comma in it. In that case, the second argument for LEFT
would result in a negative number and that would cause Left to error.
Adding the comma will end up returning the ENTIRE IDName as the LastName
value when there is no comma.

FirstName will also return the entire IDName value if there is no comma
in IDName.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Marshall Barton]

I tried to use the formulas provided but keep getting ERROR in the results.

I forgot to reverse the aeguments. It should be:

LastName: Left(IDName,InStr(IDName,",")-1)
FirstName: Mid(IDName,InStr(IDName,",")+1)

BUT, that should not account for you getting ERROR. Post a
copy/Paste of your query's SQL view so I can see what you
have.

You should get an error for any name that does not have a
comma, so double check all your records for it. Any records
missing the name will also get an error. If there are any
records like that, please explain what you want to do about
it.

 

[Stephen sjw_ost]

These formulas worked. Thank you very much!
Also,
There are records with no name and that have no comma for being entered
Firstname Lastname so like you mentioned they have the #Error as their
result.
For these I would like the result to be null so I can exclude them from the
query. I did look at the IsError in the builder but can't seem to wrap my
head around how to get it to work. Anyway, I'd like the results with #Error
to ultimately be null.

Thanks again!

 

[Marshall Barton]

These formulas worked. Thank you very much!
Also,
There are records with no name and that have no comma for being entered
Firstname Lastname so like you mentioned they have the #Error as their
result.
For these I would like the result to be null so I can exclude them from the
query. I did look at the IsError in the builder but can't seem to wrap my
head around how to get it to work. Anyway, I'd like the results with #Error
to ultimately be null.

I think this might do that:

LastName: IIf(InStr(Nz(IDName,""), ",") > 0,
Left(IDName,InStr(IDName,",")-1), Null)

FirstName: IIf(InStr(Nz(IDName,""), ",") > 0,
Mid(IDName,InStr(IDName,",")+1), Null)

If you should change your mind about names without a comma.
see John's reply.

 

[Stephen sjw_ost]

This worked perfectly! Thank you very much!