Form Text Fields & Queries & Migranes

[Guest]

I have two main tables: Customers and Tours. Each has its own form.

The Tours table has autonumbered primary key and a column for Customer ID,
in order to keep track of which customer does what.

A query (salutation) uses an IIf statement to combine the separate name
fields from Customers (First1, Middle1, Last1, First2, Middle2, Last2) into a
salutation ("John and Jane Doe", or "David D Doofus and Hellen Keller"). The
query has two colums: Customer ID and Salutation.

-----------

The form I've made is based off of the Tours table. I would like to add a
text box to display the salutation (Name and such) based off of the Customer
ID (which is found in the Tours Table, but is based off of the Customers
Table). I've done it with a query, but when I looked at the SQL code it was
something about 5 lines long (at 1280 x 1024 resolution). Eventually I'd
have to run it against several thousand records, and my guess is that it'd
probably take my processor to school. The database crash was a telltale sign
too.

Three hours later, I'm left wondering: is there any way to write maybe some
SQL code or hopefully something even more simple that does this:

- Read a field to get the Customer ID
- Create a salutation based off of the name fields for the corresponding
Customer
- Display the result in a text field in the form

Sorry for the overly-complicated explanation... and thanks a Million in
advance!

Nick

-----------------------

Code for Salutation Query:

salutation: IIf(IsNull([last2]),[first1] & " " &
[last1],IIf([last2]=[last1],[first1] & " and " & [first2] & " " &
[last1],[first1] & " " & [last1] & " and " & [first2] & " " & [last2]))

 

[Guest]

Hey again Klatuu!

After looking at the code you wrote, I put together a short bit of code that
seems to do the trick:

If Not IsNull(Me!leadnum) Then
Me!infobox = DLookup("[Salutation]", "Salutation", "[leadnum] = " &
Me!leadnum)
Else
Me!infobox = Null
End If

Thank you so much for taking the time to help me out over the past few
weeks! I know I ask a lot of questions that seem silly, but thanks to you
and several others, I am getting much better at this!

Nick

It will take a little experimenting to get it all correct given that you will
have to deal with new records and such, but here is an idea that will work
for the form, but I don't know about other things you may want to do with it.
Try putting something like this in the Current event of the Tours form:

dim varNameArrary as Variant
dim varNameList as Variant

varNameList = DLookup("[First1] & '/' & [Middle1] & '/' & [Last1] & '/' _
& [First2] & '/' & [Middle2] & '/' & [Last2]", "CustomerTable", _
"[Customer ID] = " & Me.CustomerId)
If Not IsNull(varNameList) Then
varNameArray = Split(varNameList, "/")
Me.Salutation = varNameArray(0) & " " & varNameArray(1) & " " _
& varNameArray(2) & " And " & varNameArray(3) & " " _
& varNameArray(4) & " " & varNameArray(5)
End If

This is untested air code, and incomplete, bu maybe it will give you a
usefull idea.

I have two main tables: Customers and Tours. Each has its own form.

The Tours table has autonumbered primary key and a column for Customer ID,
in order to keep track of which customer does what.

A query (salutation) uses an IIf statement to combine the separate name
fields from Customers (First1, Middle1, Last1, First2, Middle2, Last2) into a
salutation ("John and Jane Doe", or "David D Doofus and Hellen Keller"). The
query has two colums: Customer ID and Salutation.

-----------

The form I've made is based off of the Tours table. I would like to add a
text box to display the salutation (Name and such) based off of the Customer
ID (which is found in the Tours Table, but is based off of the Customers
Table). I've done it with a query, but when I looked at the SQL code it was
something about 5 lines long (at 1280 x 1024 resolution). Eventually I'd
have to run it against several thousand records, and my guess is that it'd
probably take my processor to school. The database crash was a telltale sign
too.

Three hours later, I'm left wondering: is there any way to write maybe some
SQL code or hopefully something even more simple that does this:

- Read a field to get the Customer ID
- Create a salutation based off of the name fields for the corresponding
Customer
- Display the result in a text field in the form

Sorry for the overly-complicated explanation... and thanks a Million in
advance!

Nick

-----------------------

Code for Salutation Query:

salutation: IIf(IsNull([last2]),[first1] & " " &
[last1],IIf([last2]=[last1],[first1] & " and " & [first2] & " " &
[last1],[first1] & " " & [last1] & " and " & [first2] & " " & [last2]))