Complex Expression - I need help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Excel Spreadsheet that I want to make into an access database. One
of the fields in Excel contains the following statement:
=IF(ISBLANK(E3),"",CONCATENATE(E3,".",F3,IF(F3="","","."),G3,"@yahoo.com"))

Where:
E3 = First Name
F3 = Middle Name
G3 = Last Name

How do I make this expression in an Access Query?
 
Your statement looks wrong as it would put a comma after first name.

Access would look like this --
Name: [First Name] & IIf([Middle Name] Is Null," " & [Middle Name] & [Last
Name]," " & [Middle Name] & [Last Name])
 
One method would be to use the two concatenation operators.
(E3 + " ") & (F3 + " ") & G3

The E3 is blank (null) then the space will not get added since null plus
anything is Null.
The & treats Null as if it were "" (a zero-length string) and concatenates
(adds) that "" to whatever else is there.

Another way
E3 & IIF(IsNull(E3),""," ") & F3 & IIF(IsNull(F3),"", " ") & G3
 
I guess I should have been more clear. What I am trying to accomplish is
this. I enter the name John M Doe into my form, I want a nother field to
join the string together so that it displays as (e-mail address removed).
Unfortunately, not all people have middle initials, so when the middle name
is blank I don't want to have two dots (..) or to have the following result.
(e-mail address removed).

KARL DEWEY said:
Your statement looks wrong as it would put a comma after first name.

Access would look like this --
Name: [First Name] & IIf([Middle Name] Is Null," " & [Middle Name] & [Last
Name]," " & [Middle Name] & [Last Name])


~C said:
I have an Excel Spreadsheet that I want to make into an access database. One
of the fields in Excel contains the following statement:
=IF(ISBLANK(E3),"",CONCATENATE(E3,".",F3,IF(F3="","","."),G3,"@yahoo.com"))

Where:
E3 = First Name
F3 = Middle Name
G3 = Last Name

How do I make this expression in an Access Query?
Click to expand...
Click to expand...
 
I guess I should have been more clear. What I am trying to accomplish is
this. I enter the name John M Doe into my form, I want a nother field to
join the string together so that it displays as (e-mail address removed).
Unfortunately, not all people have middle initials, so when the middle name
is blank I don't want to have two dots (..) or to have the following result.
(e-mail address removed).
 
Do you have one field containing John M. Doe or do you have three fields
(FirstName, MiddleInitial, LastName)? If three fields, does middle initial
always include the period, never include the period, or sometimes include
the period?
 
Three fields. I never put in the period, but I suppose I could, if it made
this less complicated.
 
So, you should be able to do

E3 & IIF(IsNull(E3),"",".") & F3 & IIF(IsNull(F3),"", ".") & G3 & "@Yahoo.com"

Replace E3, F3, G3 With your field names.

(E3 + ".") & (F3 + ".") & G3 & "@Yahoo.com"

If your fields are not null but Contain zero-length strings (ZLS), then you
modify the IIF version to or if you don't know if they are null or ZLS then use

E3 & IIF(Len(E3 & "")=0,"",".") & F3 & IIF(Len(F3 & "") = 0,"",".") & ...
 
John,

I sort of got it to work. I used the following formula:
User Email Address: [User First Name] & IIf(IsNull([User First Name]),"","."
& [User MI] & IIf(IsNull([User MI]),"","." & [User Last Name] & "@yahoo.com"))

If the person has a middle inital then it works great! example:
Mary F. Smith displays as (e-mail address removed)

However, if the person does not have a Middle Initial then the result is as
follows:
mary.

What do i need to add to this formula to make it work?

~C
 
User Email Address: [User First Name] & IIf(IsNull([User First
Name]),"",".")
& [User MI] & IIf(IsNull([User MI]),"","." )
& [User Last Name] & "@yahoo.com"

If this fails, then try breaking this down into pieces to trouble shoot it.


~C said:
John,

I sort of got it to work. I used the following formula:
User Email Address: [User First Name] & IIf(IsNull([User First
Name]),"","."
& [User MI] & IIf(IsNull([User MI]),"","." & [User Last Name] &
"@yahoo.com"))

If the person has a middle inital then it works great! example:
Mary F. Smith displays as (e-mail address removed)

However, if the person does not have a Middle Initial then the result is
as
follows:
mary.

What do i need to add to this formula to make it work?

~C

John Spencer said:
So, you should be able to do

E3 & IIF(IsNull(E3),"",".") & F3 & IIF(IsNull(F3),"", ".") & G3 &
"@Yahoo.com"

Replace E3, F3, G3 With your field names.

(E3 + ".") & (F3 + ".") & G3 & "@Yahoo.com"

If your fields are not null but Contain zero-length strings (ZLS), then
you
modify the IIF version to or if you don't know if they are null or ZLS
then use

E3 & IIF(Len(E3 & "")=0,"",".") & F3 & IIF(Len(F3 & "") = 0,"",".") & ...
Click to expand...
Click to expand...
 
John,

Thanks! That worked! :)

~C said:
John,

I sort of got it to work. I used the following formula:
User Email Address: [User First Name] & IIf(IsNull([User First Name]),"","."
& [User MI] & IIf(IsNull([User MI]),"","." & [User Last Name] & "@yahoo.com"))

If the person has a middle inital then it works great! example:
Mary F. Smith displays as (e-mail address removed)

However, if the person does not have a Middle Initial then the result is as
follows:
mary.

What do i need to add to this formula to make it work?

~C

John Spencer said:
So, you should be able to do

E3 & IIF(IsNull(E3),"",".") & F3 & IIF(IsNull(F3),"", ".") & G3 & "@Yahoo.com"

Replace E3, F3, G3 With your field names.

(E3 + ".") & (F3 + ".") & G3 & "@Yahoo.com"

If your fields are not null but Contain zero-length strings (ZLS), then you
modify the IIF version to or if you don't know if they are null or ZLS then use

E3 & IIF(Len(E3 & "")=0,"",".") & F3 & IIF(Len(F3 & "") = 0,"",".") & ...
Click to expand...
Click to expand...
 
Back
Top