New to Access, Im sure this is simple, but how??!

[Craig Armitage]

Hiya, Im trying to make a customers/jobs database and I was wondering if the
following is possible....

I have a a customers table and a jobs table... The customers table has the
following fields...

CustomerID (Primary Key)
CustomerPrefix
CustomerFirstName
CustomerLastName
...etc

The jobs table has a JobCustomerID thats related to CustomerID in the
customers table. I have created a Jobs form that has a dropdown combo box
so i can select which customer the job is about.. The problem is this....

When i select the dropdown box, it shows a list with entries like... "Mr |
John | Smith" which is fine. but after selecting the customer, the box
only shows "Mr". Is there a way to make the box show the customers full
name, or is there a better way to do this?

Thanks

 

[Ken Snell \(MVP\)]

You want to concatenate the names into a single field for your combo box to
see. This is done by using a query as the RowSource of your combo box. Using
the info that you've provided, put this SQL statement in the Row Source
property of the combo box:

SELECT CustomerID, CustomerPrefix & " " & CustomerFirstName & " "
CustomerLastName FROM Customers;

Now, set these properties to the indicated values for your combo box:
Column Count 2
Column Widths 0";2"
Bound Column 1
List Width 2"

 

[Granny Spitz via AccessMonster.com]

Is there a way to make the box show the customers full
name, or is there a better way to do this?

Use a query for the combo box, not the table, and use a calculated column to
form the full name like this:

SELECT CustomerID, CustomerPrefix, CustomerFirstName, CustomerLastName,
CustomerPrefix & " " & CustomerFirstName & " " & CustomerLastName AS
FullName
FROM Customers
ORDER BY CustomerLastName, CustomerFirstName;

In the column widths property set all the column widths to zero except for
the FullName column, like this:

0";0";0";0";1.5"

 

[Dirk Goldgar]

Hiya, Im trying to make a customers/jobs database and I was wondering
if the following is possible....

I have a a customers table and a jobs table... The customers table
has the following fields...

CustomerID (Primary Key)
CustomerPrefix
CustomerFirstName
CustomerLastName
..etc

The jobs table has a JobCustomerID thats related to CustomerID in the
customers table. I have created a Jobs form that has a dropdown
combo box so i can select which customer the job is about.. The
problem is this....

When i select the dropdown box, it shows a list with entries like...
"Mr | John | Smith" which is fine. but after selecting the
customer, the box only shows "Mr". Is there a way to make the box
show the customers full name, or is there a better way to do this?

You need to set the rowsource of the combo box to a query that uses a
calculated field to assemble the customer's full name. The SQL might
look like this:

SELECT
CustomerID,
CustomerLastName &
", " & CustomerFirstName &
(" "+CustomerMiddleName)
AS FullName
FROM Customers
ORDER BY
CustomerLastName,
CustomerFirstName,
CustomerMiddleName;

Then your combo would only need to have two columns, and you'd hide the
ID column and show the FullName column.

 

[Craig Armitage]

Thats brilliant.. thank you for answering so quickly..

Just going a slight step further... is there a way to cope with blank table
entries... for example a record might just have a prefix and a lastname but
there would be 2 spaces inbetween.

expanding on that, i was thinking of putting the company name in brackets
arround it..

i know the query would be SELECT CustomerID, CustomerPrefix & " " &
CustomerFirstName & " " & CustomerLastName & " (" & CustomerCompanyName &
")" FROM Customers;

but lets say the firstname and company are blank you would get...

Mr Smith ()

Sorry to be a nuisance.


PS Thanks to Granny Spitz for replying also...

 

[Smartin]

Thats brilliant.. thank you for answering so quickly..

Just going a slight step further... is there a way to cope with blank table
entries... for example a record might just have a prefix and a lastname but
there would be 2 spaces inbetween.

expanding on that, i was thinking of putting the company name in brackets
arround it..

i know the query would be SELECT CustomerID, CustomerPrefix & " " &
CustomerFirstName & " " & CustomerLastName & " (" & CustomerCompanyName &
")" FROM Customers;

but lets say the firstname and company are blank you would get...

Mr Smith ()

Sorry to be a nuisance.


PS Thanks to Granny Spitz for replying also...

Maybe this?

SELECT
CustomerID,
IIF(CustomerPrefix Is Null, "", CustomerPrefix & " ") &
IIF(CustomerFirstName Is Null, "", CustomerFirstName & " ") &
IIF(CustomerLastName Is Null, "", CustomerLastName & " ") &
"(" & CustomerCompanyName & ")"
FROM Customers;

 

[Craig Armitage]

Thanks Smartin, Thats tidied it up nicely.. cheers for all your help guys,
im sure ill have tonnes more problems in the future!!

Craig

 

[Granny Spitz via AccessMonster.com]

Just going a slight step further... is there a way to cope with blank table
entries... for example a record might just have a prefix and a lastname but
there would be 2 spaces inbetween.

expanding on that, i was thinking of putting the company name in brackets
arround it..

SELECT CustomerID, (IIf(IsNull(CustomerPrefix), Null, CustomerPrefix & " ") &
IIf(IsNull(CustomerFirstName), Null, CustomerFirstName & " ") & IIf(IsNull
(CustomerLastName), Null, CustomerLastName & " ") & IIf(IsNull
(CustomerCompanyName), Null, "(" & CustomerCompanyName & ")")) AS FullName
FROM Customers
ORDER BY CustomerLastName, CustomerFirstName;

 

[Ken Snell \(MVP\)]

You can use the + operator to "cancel out" spaces when a field has a Null
value:

SELECT CustomerID, CustomerPrefix & (" " + CustomerFirstName) & (" " +
CustomerLastName) & (" (" + CustomerCompanyName +
")" FROM Customers;

 

[Smartin]

You can use the + operator to "cancel out" spaces when a field has a Null
value:

SELECT CustomerID, CustomerPrefix & (" " + CustomerFirstName) & (" " +
CustomerLastName) & (" (" + CustomerCompanyName +
")" FROM Customers;

Nice tip!