field properties, lookup, space issue

[clippertm]

Hi Everyone,

I am learning how to use access 2007 and am making a small book database for this purpose.

In my table "books" there is a lookup for the "author" field, which import the data from another table (the "authors" table).

This lookup displays the value from 3 different fields: last name, middle name and first name, divided by a space.

Here is the lookup:

SELECT [authors].[ID], [authors].[last name] & " " & [authors].[middle name] & " " & [authors].[first name] FROM authors ORDER BY [last name], [middle name], [first name];

The issue is, when there is no middle name (middle name field is null/blank), the last name and first name are separated by 2 spaces instead of one.

Does anyone know how I can set it so that the two fields are only separated by one space when there is no middle name?

Many thanks,

 

[DCrake]

You can use the Replace() function

FullName:Replace(Forename & " " & Middlename & " " & LastName," "," ")

David

 

[clippertm]

Hi David,

Sorry, I am a beginner, I don't understand the the logic with the Replace() function.

I only want to delete one space if there is no middle name.

Should it not involve a "IF" function? Like: IF no middle name THEN delete space?

Many thanks,

 

[DCrake]

If you took the time to look up the help on Replace() it would have said some thing like:

Replace("StringToSeach","What to look for","replace with")

FullName:Replace([FirstName] & " " [MiddleName] & " " & [LastName],"^^","^")

In the above example ^ represents a space

So whe have told Access to look a the fullname of the person and replace all doulbe spaces with single spaces. No need to bother with iff's.

David

 

[clippertm]

Hi David,

I looked at the help and several websites but it was a bit difficult for me to grasp the logic.

Thank you for explaning me.

 

[clippertm]

Hi David,

I can't figure out where to write this Replace() function.

Is it after or before SELECT, or FROM?

Many thanks,

 

[DCrake]

Create a new query with the table that contains the users names. Then in the first column enter:

FullName:StrConv(Replace([First Name] & " " & [Middle Name] & " " & [Last Name],"  "," "),3)

Then view the results in dataview.

David