How to string together multiple data into one field

[Antimatter]

Right now i have this following code for my author field in the row source

SELECT DISTINCT [Author].[AuthorID], [First] & " " & [Middle] & " " & [Last] AS FullName FROM Author;

and it works great, but this is not an serious problem but more of an vanity issue, but i can live with it if there's no easy way to solve it.

Okay for the author name, some authors don't have middle name so using that above code to string together the author name, it works fine but i have a extra space.

example:

R.A.  Salvore

it should be

R.A. Salvore

without the extra space in middle from stringing together the non existant middle name.

anyway to setup an sort of code that is an IF statement, such as it check is there anything in the Middle name field, if no then it would use this code

SELECT DISTINCT [Author].[AuthorID], [First] & " " & [Last] AS FullName FROM Author;

but if there is something in the middle name field then it would use this code

SELECT DISTINCT [Author].[AuthorID], [First] & " " & [Middle] & " " & [Last] AS FullName FROM Author;

is there anyway to do that from the Row Source or will i have to construct some sort of visual basic code and stick it in an event for it to work, if its too complex then i can just leave it alone, its more of an vanity issue than an serious problem.


i'm thinking maybe one solution is to have an "back space" character in the empty middle name field so when it get merged to be showen on the form it will automaticaly delete one of the space. would that work?

 

[WayneRyan]

Anti,

Look up the Replace function. You can use it to substitute a single-space
for each double-space.

Wayne

 

[Antimatter]

Hmm... i indexed the REPLACE and also tested out the SUBSTITUTE function

so from what i read, i went on and plugged it into the row source such as

SELECT DISTINCT [Author].[AuthorID], SUBSTITUTE( [First] & " " & [Middle] & " " & [Last] AS FullName FROM Author, "  ", " " );

that gave me an error that i was missing something in the function defination,

the REPLACE required me to give two number, the beguning and the ending of the area that i wanted to replace, and i can't set it because each author is different size.

or does it belong into an VBA module if so then ?

 

[WayneRyan]

Anti,

Oops, to address this in SQL, we'll have to do something different:

First, you can use an IIf statement:

TheName: IIf(IsNull([Middle]), [First] & " " & [Last], [First] & " " & [Middle] & [Last])

Or, you can make a Public VBA function that you can include in the SQL.

Note: Depending on context, "IsNull([Middle]) or [Middle] Is Null".

Wayne

 

[Antimatter]

Ahh i see cool thanks that was the last thing i had left to fix in my database, i got all of the other NotInList code done, and couple of custom function for extracting the authors, and finished constructing the database, that was the last item left before i could say i was offericaly finished

thanks!

 

[WayneRyan]

Anti,

Glad to hear it. I assume that you used the IIf statement.

Wayne

 

[Antimatter]

Yep i did and it worked like a charm, i'm not as familar with VBA so i try to avoid it for most items, althought as i get more familar with it i might use it more often.