SQL "IF" Question

J

Jon22

I'm trying to make the Row Source of a combo box named "Contact" be a list of
names based on the value in another combo box named "Customer" in the same
form named "Quotes New".

The table (named "Customers") that contains all the data I need has the
fields "Customer", "Contact1", "Contact2", "Contact3" and "Contact4".

I have successfully created an SQL statement using the UNION thingy which
returns [Contact1] to [Contact4] as a list for my "Contact" Combo Box Row
Source (which updates as the "Customer" Combo Box value changes) - that's all
fine. This Statement reads:

SELECT [Contact1]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact1 <> NULL
UNION SELECT[Contact2]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact2 <> NULL
UNION SELECT [Contact3]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact3 <> NULL
UNION SELECT [Contact4]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact4 <> NULL

I've managed to weed out the Contact fields that are null too (except for
one null line that remains in the drop down list which stumps me) as many of
the records from "Customers" only contain 1 or 2 Contacts.

What I'm trying to do though is get the Contact values to appear in the
order of their Field Name in the drop-down combo box (ie [Contact1],
[Contact2] etc) and maybe add the value "Add New" to the bottom of the
drop-down list after an empty line. Again - got over this hurdle with the
following statement:

SELECT [Contact1], 1 As [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact1 <> NULL
UNION SELECT[Contact2], 2 AS [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact2 <> NULL
UNION SELECT [Contact3], 3 As [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact3 <> NULL
UNION SELECT [Contact4], 4 AS [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact4 <> NULL
UNION SELECT Null AS [Contact5], 5 AS [Number]
FROM Customers
UNION SELECT "Add New" AS [Contact6], 6 AS [Number]
FROM Customers
ORDER BY [Number];

But, the problem with this is, because of the [Number] field, I get a blank
line where the value is Null from the four [Contact ] fields. So if for
instance I only have one contact for a particular Customer in the [Contact1]
field, the drop down list displays this name at the top then 5 blank lines
then "Add New" at the bottom.

I can't work out how to display just the contacts that are not null then a
blank line then the text "Add New" in the order I want them.
 
A

Arvin Meyer [MVP]

Your first and largest problem is that you have 4 Contact fields instead of
a Contact table. You haven't Normalized the Customer table. Nor have you
allowed for more than 4 contacts. You need a many side table with 2 (or
more) fields:

CustomerID and Contact

Once you've done that, you'll find that you have no nulls and don't need to
worry about a Union query. Use 4 queries to build and append a
tblCustomerContacts table. Then sort it so that any Nulls come to the top
and delete all of those. Now add an autonumber field for CustomerContactID
and you'll no longer have any problems. Build a subform/subreport to display
and edit the contacts.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Jon22 said:
I'm trying to make the Row Source of a combo box named "Contact" be a list
of
names based on the value in another combo box named "Customer" in the same
form named "Quotes New".

The table (named "Customers") that contains all the data I need has the
fields "Customer", "Contact1", "Contact2", "Contact3" and "Contact4".

I have successfully created an SQL statement using the UNION thingy which
returns [Contact1] to [Contact4] as a list for my "Contact" Combo Box Row
Source (which updates as the "Customer" Combo Box value changes) - that's
all
fine. This Statement reads:

SELECT [Contact1]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact1 <> NULL
UNION SELECT[Contact2]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact2 <> NULL
UNION SELECT [Contact3]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact3 <> NULL
UNION SELECT [Contact4]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact4 <> NULL

I've managed to weed out the Contact fields that are null too (except for
one null line that remains in the drop down list which stumps me) as many
of
the records from "Customers" only contain 1 or 2 Contacts.

What I'm trying to do though is get the Contact values to appear in the
order of their Field Name in the drop-down combo box (ie [Contact1],
[Contact2] etc) and maybe add the value "Add New" to the bottom of the
drop-down list after an empty line. Again - got over this hurdle with the
following statement:

SELECT [Contact1], 1 As [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact1 <> NULL
UNION SELECT[Contact2], 2 AS [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact2 <> NULL
UNION SELECT [Contact3], 3 As [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact3 <> NULL
UNION SELECT [Contact4], 4 AS [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact4 <> NULL
UNION SELECT Null AS [Contact5], 5 AS [Number]
FROM Customers
UNION SELECT "Add New" AS [Contact6], 6 AS [Number]
FROM Customers
ORDER BY [Number];

But, the problem with this is, because of the [Number] field, I get a
blank
line where the value is Null from the four [Contact ] fields. So if for
instance I only have one contact for a particular Customer in the
[Contact1]
field, the drop down list displays this name at the top then 5 blank lines
then "Add New" at the bottom.

I can't work out how to display just the contacts that are not null then a
blank line then the text "Add New" in the order I want them.
Click to expand...
 
J

Jon22

Thanks Arvin, I had actually considered doing this and was wavering. My
hesitation being that much of my coding is already based around the way I've
done it and I'll have to go through and re-do a fair bit of stuff but I think
it's probably the best way.

Arvin Meyer said:
Your first and largest problem is that you have 4 Contact fields instead of
a Contact table. You haven't Normalized the Customer table. Nor have you
allowed for more than 4 contacts. You need a many side table with 2 (or
more) fields:

CustomerID and Contact

Once you've done that, you'll find that you have no nulls and don't need to
worry about a Union query. Use 4 queries to build and append a
tblCustomerContacts table. Then sort it so that any Nulls come to the top
and delete all of those. Now add an autonumber field for CustomerContactID
and you'll no longer have any problems. Build a subform/subreport to display
and edit the contacts.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Jon22 said:
I'm trying to make the Row Source of a combo box named "Contact" be a list
of
names based on the value in another combo box named "Customer" in the same
form named "Quotes New".

The table (named "Customers") that contains all the data I need has the
fields "Customer", "Contact1", "Contact2", "Contact3" and "Contact4".

I have successfully created an SQL statement using the UNION thingy which
returns [Contact1] to [Contact4] as a list for my "Contact" Combo Box Row
Source (which updates as the "Customer" Combo Box value changes) - that's
all
fine. This Statement reads:

SELECT [Contact1]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact1 <> NULL
UNION SELECT[Contact2]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact2 <> NULL
UNION SELECT [Contact3]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact3 <> NULL
UNION SELECT [Contact4]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact4 <> NULL

I've managed to weed out the Contact fields that are null too (except for
one null line that remains in the drop down list which stumps me) as many
of
the records from "Customers" only contain 1 or 2 Contacts.

What I'm trying to do though is get the Contact values to appear in the
order of their Field Name in the drop-down combo box (ie [Contact1],
[Contact2] etc) and maybe add the value "Add New" to the bottom of the
drop-down list after an empty line. Again - got over this hurdle with the
following statement:

SELECT [Contact1], 1 As [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact1 <> NULL
UNION SELECT[Contact2], 2 AS [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact2 <> NULL
UNION SELECT [Contact3], 3 As [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact3 <> NULL
UNION SELECT [Contact4], 4 AS [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact4 <> NULL
UNION SELECT Null AS [Contact5], 5 AS [Number]
FROM Customers
UNION SELECT "Add New" AS [Contact6], 6 AS [Number]
FROM Customers
ORDER BY [Number];

But, the problem with this is, because of the [Number] field, I get a
blank
line where the value is Null from the four [Contact ] fields. So if for
instance I only have one contact for a particular Customer in the
[Contact1]
field, the drop down list displays this name at the top then 5 blank lines
then "Add New" at the bottom.

I can't work out how to display just the contacts that are not null then a
blank line then the text "Add New" in the order I want them.
Click to expand...
Click to expand...
 
A

Arvin Meyer [MVP]

Actually, by having 4 separate field to store the same data, you've
complicated the retrieval of that data. Just 1 small example: Suppose John
Smith leaves CompanyA and goes to work for CompanyB, all you have to do is
to change his CompanyID. In your methodology, you need to find him, delete
him, then add him to the new company. More work and more possibility for
mistakes. By reducing the volume of work and number of possible mistakes,
you save money. But more importantly, you spend time with what your company
makes money doing, rather than messing with data.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Jon22 said:
Thanks Arvin, I had actually considered doing this and was wavering. My
hesitation being that much of my coding is already based around the way
I've
done it and I'll have to go through and re-do a fair bit of stuff but I
think
it's probably the best way.

Arvin Meyer said:
Your first and largest problem is that you have 4 Contact fields instead
of
a Contact table. You haven't Normalized the Customer table. Nor have you
allowed for more than 4 contacts. You need a many side table with 2 (or
more) fields:

CustomerID and Contact

Once you've done that, you'll find that you have no nulls and don't need
to
worry about a Union query. Use 4 queries to build and append a
tblCustomerContacts table. Then sort it so that any Nulls come to the top
and delete all of those. Now add an autonumber field for
CustomerContactID
and you'll no longer have any problems. Build a subform/subreport to
display
and edit the contacts.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Jon22 said:
I'm trying to make the Row Source of a combo box named "Contact" be a
list
of
names based on the value in another combo box named "Customer" in the
same
form named "Quotes New".

The table (named "Customers") that contains all the data I need has the
fields "Customer", "Contact1", "Contact2", "Contact3" and "Contact4".

I have successfully created an SQL statement using the UNION thingy
which
returns [Contact1] to [Contact4] as a list for my "Contact" Combo Box
Row
Source (which updates as the "Customer" Combo Box value changes) -
that's
all
fine. This Statement reads:

SELECT [Contact1]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact1 <> NULL
UNION SELECT[Contact2]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact2 <> NULL
UNION SELECT [Contact3]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact3 <> NULL
UNION SELECT [Contact4]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact4 <> NULL

I've managed to weed out the Contact fields that are null too (except
for
one null line that remains in the drop down list which stumps me) as
many
of
the records from "Customers" only contain 1 or 2 Contacts.

What I'm trying to do though is get the Contact values to appear in the
order of their Field Name in the drop-down combo box (ie [Contact1],
[Contact2] etc) and maybe add the value "Add New" to the bottom of the
drop-down list after an empty line. Again - got over this hurdle with
the
following statement:

SELECT [Contact1], 1 As [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact1 <> NULL
UNION SELECT[Contact2], 2 AS [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact2 <> NULL
UNION SELECT [Contact3], 3 As [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact3 <> NULL
UNION SELECT [Contact4], 4 AS [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact4 <> NULL
UNION SELECT Null AS [Contact5], 5 AS [Number]
FROM Customers
UNION SELECT "Add New" AS [Contact6], 6 AS [Number]
FROM Customers
ORDER BY [Number];

But, the problem with this is, because of the [Number] field, I get a
blank
line where the value is Null from the four [Contact ] fields. So if for
instance I only have one contact for a particular Customer in the
[Contact1]
field, the drop down list displays this name at the top then 5 blank
lines
then "Add New" at the bottom.

I can't work out how to display just the contacts that are not null
then a
blank line then the text "Add New" in the order I want them.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Using the table structure Arvin suggested is the best solution.

Just for future information. <> Null is incorrect in a query, what you need
to use is IS Not Null. So your query should look more like the following. It
could be working if Access is changing <> null to Is Not Null - which my
version of Access 2003 will do behind the scenes.

If you are still getting blank lines, then I suspect that what is stored in
the Contact fields is actually a zero-length string. So your test may need to
include testing for that value also

SELECT [Contact1], 1 As [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact1 IS NOT NULL AND Customers.Contact1 <> ""
UNION SELECT[Contact2], 2 AS [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact2 IS NOT NULL AND Customers.Contact1 <> ""
UNION SELECT [Contact3], 3 As [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact3 IS NOT NULL AND Customers.Contact1 <> ""
UNION SELECT [Contact4], 4 AS [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact4 IS NOT NULL AND Customers.Contact1 <> ""
UNION SELECT Null AS [Contact5], 5 AS [Number]
FROM Customers
UNION SELECT "Add New" AS [Contact6], 6 AS [Number]
FROM Customers
ORDER BY [Number];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
J

Jon22

Thanks John, I did actually do a zero length string check but still no luck
the blank field was still there. I've since changed it all to Arvin's
suggestion and it's all good now.

John Spencer said:
Using the table structure Arvin suggested is the best solution.

Just for future information. <> Null is incorrect in a query, what you need
to use is IS Not Null. So your query should look more like the following. It
could be working if Access is changing <> null to Is Not Null - which my
version of Access 2003 will do behind the scenes.

If you are still getting blank lines, then I suspect that what is stored in
the Contact fields is actually a zero-length string. So your test may need to
include testing for that value also

SELECT [Contact1], 1 As [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact1 IS NOT NULL AND Customers.Contact1 <> ""
UNION SELECT[Contact2], 2 AS [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact2 IS NOT NULL AND Customers.Contact1 <> ""
UNION SELECT [Contact3], 3 As [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact3 IS NOT NULL AND Customers.Contact1 <> ""
UNION SELECT [Contact4], 4 AS [Number]
FROM Customers
WHERE Customers.Customer=[Forms]![Quotes New]![Customer] AND
Customers.Contact4 IS NOT NULL AND Customers.Contact1 <> ""
UNION SELECT Null AS [Contact5], 5 AS [Number]
FROM Customers
UNION SELECT "Add New" AS [Contact6], 6 AS [Number]
FROM Customers
ORDER BY [Number];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Actually, by having 4 separate field to store the same data, you've
complicated the retrieval of that data. Just 1 small example: Suppose John
Smith leaves CompanyA and goes to work for CompanyB, all you have to do is
to change his CompanyID. In your methodology, you need to find him, delete
him, then add him to the new company. More work and more possibility for
mistakes. By reducing the volume of work and number of possible mistakes,
you save money. But more importantly, you spend time with what your company
makes money doing, rather than messing with data.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Not sure how to make a grouped report 2
VBA Export to PDF 0
Form Problems with Combo Boxes 3
Show every customer even if mobile in null 2
Relationships and Form Query Problem 5
Join? 12
Dynamic Naming of Form Button 3
Limit drop down items based on another cell 3

Top