Concatenate Query Question...

[Guest]

I am using the following SQL statement. I am trying to understand the syntax.
What is the logical way to read it.
I have been trying to remove the middle name field form it, but can't get
the syntax correct.

SELECT [LastName] & IIf([FirstName],", " & [FirstName],"") &
IIf([MiddleName]," " & [MiddleName]) AS LastNameFirst,
IIf([MiddleName],[FirstName] & " " & [MiddleName],[FirstName]) &
IIf([LastName]," " & [LastName],[LastName]) AS FirstNameFirst
FROM tblStaffumbers;

Any help would be great.

 

[Marshall Barton]

I am using the following SQL statement. I am trying to understand the syntax.
What is the logical way to read it.
I have been trying to remove the middle name field form it, but can't get
the syntax correct.

SELECT [LastName] & IIf([FirstName],", " & [FirstName],"") &
IIf([MiddleName]," " & [MiddleName]) AS LastNameFirst,
IIf([MiddleName],[FirstName] & " " & [MiddleName],[FirstName]) &
IIf([LastName]," " & [LastName],[LastName]) AS FirstNameFirst
FROM tblStaffumbers;

 

[Guest]

Sorry about the typo....form....should be FORM

I am using the following SQL statement. I am trying to understand the syntax.
What is the logical way to read it.
I have been trying to remove the middle name field form it, but can't get
the syntax correct.

SELECT [LastName] & IIf([FirstName],", " & [FirstName],"") &
IIf([MiddleName]," " & [MiddleName]) AS LastNameFirst,
IIf([MiddleName],[FirstName] & " " & [MiddleName],[FirstName]) &
IIf([LastName]," " & [LastName],[LastName]) AS FirstNameFirst
FROM tblStaffumbers;

I am using the following SQL statement. I am trying to understand the syntax.
What is the logical way to read it.
I have been trying to remove the middle name field form it, but can't get
the syntax correct.

SELECT [LastName] & IIf([FirstName],", " & [FirstName],"") &
IIf([MiddleName]," " & [MiddleName]) AS LastNameFirst,
IIf([MiddleName],[FirstName] & " " & [MiddleName],[FirstName]) &
IIf([LastName]," " & [LastName],[LastName]) AS FirstNameFirst
FROM tblStaffumbers;

 

[Guest]

mmmm......sorry about that....should really go to sleep!!

Rephrased.....
I would like to concatenate only LastName First and FirstName Last
I do not want to include the middle name.

I am not sure what inverted commas and what brackets should remain in the
sql statement as, I have only been able to generate error messages, when I
make the changes....
Please Advise

Sorry about the typo....form....should be FORM

I am using the following SQL statement. I am trying to understand the syntax.
What is the logical way to read it.
I have been trying to remove the middle name field form it, but can't get
the syntax correct.

SELECT [LastName] & IIf([FirstName],", " & [FirstName],"") &
IIf([MiddleName]," " & [MiddleName]) AS LastNameFirst,
IIf([MiddleName],[FirstName] & " " & [MiddleName],[FirstName]) &
IIf([LastName]," " & [LastName],[LastName]) AS FirstNameFirst
FROM tblStaffumbers;

I am using the following SQL statement. I am trying to understand the syntax.
What is the logical way to read it.
I have been trying to remove the middle name field form it, but can't get
the syntax correct.

SELECT [LastName] & IIf([FirstName],", " & [FirstName],"") &
IIf([MiddleName]," " & [MiddleName]) AS LastNameFirst,
IIf([MiddleName],[FirstName] & " " & [MiddleName],[FirstName]) &
IIf([LastName]," " & [LastName],[LastName]) AS FirstNameFirst
FROM tblStaffumbers;

 

[Marshall Barton]

Sorry about the typo....form....should be FORM

I am using the following SQL statement. I am trying to understand the syntax.
What is the logical way to read it.
I have been trying to remove the middle name field form it, but can't get
the syntax correct.

SELECT [LastName] & IIf([FirstName],", " & [FirstName],"") &
IIf([MiddleName]," " & [MiddleName]) AS LastNameFirst,
IIf([MiddleName],[FirstName] & " " & [MiddleName],[FirstName]) &
IIf([LastName]," " & [LastName],[LastName]) AS FirstNameFirst
FROM tblStaffumbers;

Sorry about the empty post, no idea what went wrong.

Whatever. You can read the IIf part as
IIf(condition, truevalue, falsevalue)
your condition is [FirstName] which may work but is really
incomplete. I think it shoul be [FirstName] Is Not Null
Anyway, if the condition expression evaluates to True, then
the result of the entire IIf( ... ) will be the result of
the truevalue expression, which in your case is the
concatenation of a comma and space with [FirstName]. If the
result of the condition expression is False, then the value
of the falsevalue expression is used (in your case an empty
string).

In other words, if there is a first name add a comma, space
and the first name after the last name. If there is no
firstname, don't add anything.

Follow the same logic for the middle name (without the
comma).

So, to get rid of the middle name, use:

SELECT [LastName] & IIf([FirstName] Is Not Null, ", " &
[FirstName], "") AS LastNameFirst
FROM tblStaffumbers

 

[Guest]

Marsh,
Thanks for the informative and clear answer.
Can you tell me where the double II comes from in the IIF as compared with a
simple IF function?
I thought it was my problem....that I hadn't explained myself
properly......when your posting was returned empty......and then I returned
the same typo in CAP!! there's no justice!
It's a pity the postings don't have a preview or edit feature.....I assume
microsoft have their reasons.....but it would be handy as in the case of my
typo error.
Thanks once more
Dermot

Sorry about the typo....form....should be FORM

I am using the following SQL statement. I am trying to understand the syntax.
What is the logical way to read it.
I have been trying to remove the middle name field form it, but can't get
the syntax correct.

SELECT [LastName] & IIf([FirstName],", " & [FirstName],"") &
IIf([MiddleName]," " & [MiddleName]) AS LastNameFirst,
IIf([MiddleName],[FirstName] & " " & [MiddleName],[FirstName]) &
IIf([LastName]," " & [LastName],[LastName]) AS FirstNameFirst
FROM tblStaffumbers;

Sorry about the empty post, no idea what went wrong.

Whatever. You can read the IIf part as
IIf(condition, truevalue, falsevalue)
your condition is [FirstName] which may work but is really
incomplete. I think it shoul be [FirstName] Is Not Null
Anyway, if the condition expression evaluates to True, then
the result of the entire IIf( ... ) will be the result of
the truevalue expression, which in your case is the
concatenation of a comma and space with [FirstName]. If the
result of the condition expression is False, then the value
of the falsevalue expression is used (in your case an empty
string).

In other words, if there is a first name add a comma, space
and the first name after the last name. If there is no
firstname, don't add anything.

Follow the same logic for the middle name (without the
comma).

So, to get rid of the middle name, use:

SELECT [LastName] & IIf([FirstName] Is Not Null, ", " &
[FirstName], "") AS LastNameFirst
FROM tblStaffumbers

 

[Marshall Barton]

It sounds like you're using the MS web interface to these
newsgroups, which is missing some features found in most
email/news reader programs. I don't have that excuse, but I
can still fat finger the keyboard into all kinds of strange
behavior ;-)

Back to your question. IF is not a function, it is a VBA
statement, which is only available in VBA procedures. IIF
(Immediate IF) is a function that can be used anywhere that
expressions can be evaluated. Note that a statement
performs an action within a sequence of actions that are
collectively called a procedure, which can be a Function
procedure or a Sub procedure. The big difference between
function and sub procedures is that functions return a value
that can be used in expressions, while sub procedures can
only be used in a Call statement. Eventually, this will all
be confused by your use of properties and methods provided
by various objects so hang in there on this issue.
--
Marsh
MVP [MS Access]

Thanks for the informative and clear answer.
Can you tell me where the double II comes from in the IIF as compared with a
simple IF function?
I thought it was my problem....that I hadn't explained myself
properly......when your posting was returned empty......and then I returned
the same typo in CAP!! there's no justice!
It's a pity the postings don't have a preview or edit feature.....I assume
microsoft have their reasons.....but it would be handy as in the case of my
typo error.
Thanks once more
Dermot

Sorry about the typo....form....should be FORM

I am using the following SQL statement. I am trying to understand the syntax.
What is the logical way to read it.
I have been trying to remove the middle name field form it, but can't get
the syntax correct.

SELECT [LastName] & IIf([FirstName],", " & [FirstName],"") &
IIf([MiddleName]," " & [MiddleName]) AS LastNameFirst,
IIf([MiddleName],[FirstName] & " " & [MiddleName],[FirstName]) &
IIf([LastName]," " & [LastName],[LastName]) AS FirstNameFirst
FROM tblStaffumbers;

Sorry about the empty post, no idea what went wrong.

Whatever. You can read the IIf part as
IIf(condition, truevalue, falsevalue)
your condition is [FirstName] which may work but is really
incomplete. I think it shoul be [FirstName] Is Not Null
Anyway, if the condition expression evaluates to True, then
the result of the entire IIf( ... ) will be the result of
the truevalue expression, which in your case is the
concatenation of a comma and space with [FirstName]. If the
result of the condition expression is False, then the value
of the falsevalue expression is used (in your case an empty
string).

In other words, if there is a first name add a comma, space
and the first name after the last name. If there is no
firstname, don't add anything.

Follow the same logic for the middle name (without the
comma).

So, to get rid of the middle name, use:

SELECT [LastName] & IIf([FirstName] Is Not Null, ", " &
[FirstName], "") AS LastNameFirst
FROM tblStaffumbers

 

[Guest]

Thanks for this Marshall

In response to you comment below
It sounds like you're using the MS web interface to these
newsgroups, which is missing some features found in most
email/news reader programs. I don't have that excuse.........

I have been browsing the internet for compatible software....what are you
using?
Regards
Dermot

It sounds like you're using the MS web interface to these
newsgroups, which is missing some features found in most
email/news reader programs. I don't have that excuse, but I
can still fat finger the keyboard into all kinds of strange
behavior ;-)

Back to your question. IF is not a function, it is a VBA
statement, which is only available in VBA procedures. IIF
(Immediate IF) is a function that can be used anywhere that
expressions can be evaluated. Note that a statement
performs an action within a sequence of actions that are
collectively called a procedure, which can be a Function
procedure or a Sub procedure. The big difference between
function and sub procedures is that functions return a value
that can be used in expressions, while sub procedures can
only be used in a Call statement. Eventually, this will all
be confused by your use of properties and methods provided
by various objects so hang in there on this issue.
--
Marsh
MVP [MS Access]

Thanks for the informative and clear answer.
Can you tell me where the double II comes from in the IIF as compared with a
simple IF function?
I thought it was my problem....that I hadn't explained myself
properly......when your posting was returned empty......and then I returned
the same typo in CAP!! there's no justice!
It's a pity the postings don't have a preview or edit feature.....I assume
microsoft have their reasons.....but it would be handy as in the case of my
typo error.
Thanks once more
Dermot

Dermot wrote:
Sorry about the typo....form....should be FORM

I am using the following SQL statement. I am trying to understand the syntax.
What is the logical way to read it.
I have been trying to remove the middle name field form it, but can't get
the syntax correct.

SELECT [LastName] & IIf([FirstName],", " & [FirstName],"") &
IIf([MiddleName]," " & [MiddleName]) AS LastNameFirst,
IIf([MiddleName],[FirstName] & " " & [MiddleName],[FirstName]) &
IIf([LastName]," " & [LastName],[LastName]) AS FirstNameFirst
FROM tblStaffumbers;

Sorry about the empty post, no idea what went wrong.

Whatever. You can read the IIf part as
IIf(condition, truevalue, falsevalue)
your condition is [FirstName] which may work but is really
incomplete. I think it shoul be [FirstName] Is Not Null
Anyway, if the condition expression evaluates to True, then
the result of the entire IIf( ... ) will be the result of
the truevalue expression, which in your case is the
concatenation of a comma and space with [FirstName]. If the
result of the condition expression is False, then the value
of the falsevalue expression is used (in your case an empty
string).

In other words, if there is a first name add a comma, space
and the first name after the last name. If there is no
firstname, don't add anything.

Follow the same logic for the middle name (without the
comma).

So, to get rid of the middle name, use:

SELECT [LastName] & IIf([FirstName] Is Not Null, ", " &
[FirstName], "") AS LastNameFirst
FROM tblStaffumbers

 

[Brian Bastl]

Dermot,

Outlook Express, which should already be on your machine, works swimmingly.
Been so long since I've used a dedicated newsreader, that I couldn't even
recommend any, although I'm sure they have additional functionality built
into them.

Brian

Thanks for this Marshall

In response to you comment below
It sounds like you're using the MS web interface to these
newsgroups, which is missing some features found in most
email/news reader programs. I don't have that excuse.........

I have been browsing the internet for compatible software....what are you
using?
Regards
Dermot

It sounds like you're using the MS web interface to these
newsgroups, which is missing some features found in most
email/news reader programs. I don't have that excuse, but I
can still fat finger the keyboard into all kinds of strange
behavior ;-)

Back to your question. IF is not a function, it is a VBA
statement, which is only available in VBA procedures. IIF
(Immediate IF) is a function that can be used anywhere that
expressions can be evaluated. Note that a statement
performs an action within a sequence of actions that are
collectively called a procedure, which can be a Function
procedure or a Sub procedure. The big difference between
function and sub procedures is that functions return a value
that can be used in expressions, while sub procedures can
only be used in a Call statement. Eventually, this will all
be confused by your use of properties and methods provided
by various objects so hang in there on this issue.
--
Marsh
MVP [MS Access]

Thanks for the informative and clear answer.
Can you tell me where the double II comes from in the IIF as compared with a
simple IF function?
I thought it was my problem....that I hadn't explained myself
properly......when your posting was returned empty......and then I returned
the same typo in CAP!! there's no justice!
It's a pity the postings don't have a preview or edit feature.....I assume
microsoft have their reasons.....but it would be handy as in the case of my
typo error.
Thanks once more
Dermot


Dermot wrote:
Sorry about the typo....form....should be FORM

I am using the following SQL statement. I am trying to understand the syntax.
What is the logical way to read it.
I have been trying to remove the middle name field form it, but can't get
the syntax correct.

SELECT [LastName] & IIf([FirstName],", " & [FirstName],"") &
IIf([MiddleName]," " & [MiddleName]) AS LastNameFirst,
IIf([MiddleName],[FirstName] & " " & [MiddleName],[FirstName]) &
IIf([LastName]," " & [LastName],[LastName]) AS FirstNameFirst
FROM tblStaffumbers;


:
Sorry about the empty post, no idea what went wrong.

Whatever. You can read the IIf part as
IIf(condition, truevalue, falsevalue)
your condition is [FirstName] which may work but is really
incomplete. I think it shoul be [FirstName] Is Not Null
Anyway, if the condition expression evaluates to True, then
the result of the entire IIf( ... ) will be the result of
the truevalue expression, which in your case is the
concatenation of a comma and space with [FirstName]. If the
result of the condition expression is False, then the value
of the falsevalue expression is used (in your case an empty
string).

In other words, if there is a first name add a comma, space
and the first name after the last name. If there is no
firstname, don't add anything.

Follow the same logic for the middle name (without the
comma).

So, to get rid of the middle name, use:

SELECT [LastName] & IIf([FirstName] Is Not Null, ", " &
[FirstName], "") AS LastNameFirst
FROM tblStaffumbers

 

[Marshall Barton]

Since Outlook Express is included with Windows, it is the
email/newsgroup program of choice for most people. I use
Forte Agent and have seen several recomendataions for
Thunderbird.
--
Marsh
MVP [MS Access]

Thanks for this Marshall

In response to you comment below
It sounds like you're using the MS web interface to these
newsgroups, which is missing some features found in most
email/news reader programs. I don't have that excuse.........

I have been browsing the internet for compatible software....what are you
using?

It sounds like you're using the MS web interface to these
newsgroups, which is missing some features found in most
email/news reader programs. I don't have that excuse, but I
can still fat finger the keyboard into all kinds of strange
behavior ;-)

Back to your question. IF is not a function, it is a VBA
statement, which is only available in VBA procedures. IIF
(Immediate IF) is a function that can be used anywhere that
expressions can be evaluated. Note that a statement
performs an action within a sequence of actions that are
collectively called a procedure, which can be a Function
procedure or a Sub procedure. The big difference between
function and sub procedures is that functions return a value
that can be used in expressions, while sub procedures can
only be used in a Call statement. Eventually, this will all
be confused by your use of properties and methods provided
by various objects so hang in there on this issue.

Thanks for the informative and clear answer.
Can you tell me where the double II comes from in the IIF as compared with a
simple IF function?
I thought it was my problem....that I hadn't explained myself
properly......when your posting was returned empty......and then I returned
the same typo in CAP!! there's no justice!
It's a pity the postings don't have a preview or edit feature.....I assume
microsoft have their reasons.....but it would be handy as in the case of my
typo error.
Thanks once more
Dermot


Dermot wrote:
Sorry about the typo....form....should be FORM

I am using the following SQL statement. I am trying to understand the syntax.
What is the logical way to read it.
I have been trying to remove the middle name field form it, but can't get
the syntax correct.

SELECT [LastName] & IIf([FirstName],", " & [FirstName],"") &
IIf([MiddleName]," " & [MiddleName]) AS LastNameFirst,
IIf([MiddleName],[FirstName] & " " & [MiddleName],[FirstName]) &
IIf([LastName]," " & [LastName],[LastName]) AS FirstNameFirst
FROM tblStaffumbers;


:
Sorry about the empty post, no idea what went wrong.

Whatever. You can read the IIf part as
IIf(condition, truevalue, falsevalue)
your condition is [FirstName] which may work but is really
incomplete. I think it shoul be [FirstName] Is Not Null
Anyway, if the condition expression evaluates to True, then
the result of the entire IIf( ... ) will be the result of
the truevalue expression, which in your case is the
concatenation of a comma and space with [FirstName]. If the
result of the condition expression is False, then the value
of the falsevalue expression is used (in your case an empty
string).

In other words, if there is a first name add a comma, space
and the first name after the last name. If there is no
firstname, don't add anything.

Follow the same logic for the middle name (without the
comma).

So, to get rid of the middle name, use:

SELECT [LastName] & IIf([FirstName] Is Not Null, ", " &
[FirstName], "") AS LastNameFirst
FROM tblStaffumbers