Whats wrong with this statement?

[Craig Armitage]

Hi guys,

Once again i humbly beg for your wisdom and assistance!


I am making a report that shows either a home address or a business address
depending on what the job requires...

i have 2 tables, Jobs and JobLocation. the job location table contains 2
entries, home and business.

ive created a textbox on the report with the following statement...

The idea is that if the job location is 1 (home) then the home address is
displayed. otherwise the business address is displayed.

For some reason, when i have [JobLocation=1] I only get home addresses
displayed and when i change it to [JobLocation=2] i get blank addresses
boxes except for the ones that should show a business address which are
displayed fine

=IIf([JobLocation]=1,[Customer_HomeNameNumber] & Chr$(13) & Chr$(10) &
[Customer_HomeStreet] & Chr$(13) & Chr$(10) & [Customer_HomeTown] & Chr$(13)
& Chr$(10) & [Customer_HomeCounty] & Chr$(13) & Chr$(10) &
[Customer_HomeCountry] & Chr$(13) & Chr$(10) &
[Customer_HomePostcode],[Customer_BusinessNameNumber] & Chr$(13) & Chr$(10)
& [Customer_BusinessStreet] & Chr$(13) & Chr$(10) & [Customer_BusinessTown]
& Chr$(13) & Chr$(10) & [Customer_BusinessCounty] & Chr$(13) & Chr$(10) &
[Customer_BusinessCountry] & Chr$(13) & Chr$(10) &
[Customer_BusinessPostcode])


Please help!!

 

[Guest]

My guess would be that you have Nulls in the JobLocation columns for the
'business' jobs. Its important to realize that a Null is not a value, so
when you compare a Null with a value the answer is not True or False, but
Null. This would explain the behaviour you are experiencing because
comparing 1 with 1 returns True, 2 with 1 False, but 2 with Null or 1 with
Null returns Null.

With your present design you can avoid this by ensuring that all rows in
Jobs have a value in the JobLocation columns, i.e. 2 (or any value other than
1) for the 'business' jobs.

However, the design is not a good one. By having two set of address columns
you are doing what's known in the jargon of the relational model as 'encoding
data as column headings'. This contravenes a fundamental principle of the
database relational model, which is that data must be stored as values at
column positions in rows in tables, and in no other way.

A better model would be to have just one set of address columns and an
additional JobLocation column in the JobLocation table (a better name for the
table would be JobLocations BTW as the convention is that table names are
normally plural or collective nouns, and column names singular nouns). For
home addresses in the table the JobLocation value would be 1, for business
addresses 2. You can then join the Jobs and Joblocations tables on two
columns in the report's underlying query, e.g.

SELECT <column list>
FROM Jobs INNER JOIN JobLocations
ON Jobs.JobNumber = JobLocations.JobNumber
AND Jobs.JobLocation = JobLocations.JobLocation;

You can very easily change the table design by creating a blank table with
the JobNumber (or whatever the foreign key column is called), JobLocation and
one set of address columns, and then using two append queries to insert the
rows from the two sets of relevant columns of the existing table. For the
home addresses you'd append a 1 into the JobLocation column, for the business
addresses a 2. In each case restrict the rows inserted to those with the
relevant address data by restricting the query to those where a suitable
column is not Null, e.g. for home addresses:

WHERE Customer_HomeNameNumber IS NOT NULL

and for business addresses:

WHERE Customer_BusinessNameNumber IS NOT NULL

Ken Sheridan
Stafford, England

Hi guys,

Once again i humbly beg for your wisdom and assistance!


I am making a report that shows either a home address or a business address
depending on what the job requires...

i have 2 tables, Jobs and JobLocation. the job location table contains 2
entries, home and business.

ive created a textbox on the report with the following statement...

The idea is that if the job location is 1 (home) then the home address is
displayed. otherwise the business address is displayed.

For some reason, when i have [JobLocation=1] I only get home addresses
displayed and when i change it to [JobLocation=2] i get blank addresses
boxes except for the ones that should show a business address which are
displayed fine

=IIf([JobLocation]=1,[Customer_HomeNameNumber] & Chr$(13) & Chr$(10) &
[Customer_HomeStreet] & Chr$(13) & Chr$(10) & [Customer_HomeTown] & Chr$(13)
& Chr$(10) & [Customer_HomeCounty] & Chr$(13) & Chr$(10) &
[Customer_HomeCountry] & Chr$(13) & Chr$(10) &
[Customer_HomePostcode],[Customer_BusinessNameNumber] & Chr$(13) & Chr$(10)
& [Customer_BusinessStreet] & Chr$(13) & Chr$(10) & [Customer_BusinessTown]
& Chr$(13) & Chr$(10) & [Customer_BusinessCounty] & Chr$(13) & Chr$(10) &
[Customer_BusinessCountry] & Chr$(13) & Chr$(10) &
[Customer_BusinessPostcode])


Please help!!

 

[Craig Armitage]

Hi Ken,

Thanks for your very in-depth reply. Ive tried to take my time and read it
carefuly and I will probably have to read it a few more times just incase
ive missed what your saying. I just wanted to give you the background of
how my database is put together because it sounds like ive got it set up how
you suggested anyway....

Okay, the database is for tracking jobs from my customers. I sometimes do
work for the same customer at their home or business premises and when
assigning a specific job to an engineer, its important the job shows the
correct address.

For this side of things my database has 3 tables which are basically laid
out as follows..

tbl_Customers -> this has fields such as CustomerID(PK), Customer_FirstName,
Customer_LastName, Customer_BusinessName etc and also has all the fields
required to have the business details and home details etc..

tbl_JobLocations -> This has two fields.. there are two database entries in
JobLocation.. "Home" and "Business"

JobLocationID (PK)
JobLocation

tbl_Jobs-> This contains all the info required about a specific job.. it
links back to both CustomerID and JobLocationID. JobLocationID is a
required field so it can never be Null.

When i make the report, with both the Home and Business address, everything
is fine but that would be confusing to the engineer. I would like to show
just one address determined by JobLocation. im sure my error is in the
original statement of my first post but i dont understand where.

I dont mind re-designing my database if its been done in an incorrect way
but im sure the error is something simple.

Thanks

Craig

My guess would be that you have Nulls in the JobLocation columns for the
'business' jobs. Its important to realize that a Null is not a value, so
when you compare a Null with a value the answer is not True or False, but
Null. This would explain the behaviour you are experiencing because
comparing 1 with 1 returns True, 2 with 1 False, but 2 with Null or 1 with
Null returns Null.

With your present design you can avoid this by ensuring that all rows in
Jobs have a value in the JobLocation columns, i.e. 2 (or any value other
than
1) for the 'business' jobs.

However, the design is not a good one. By having two set of address
columns
you are doing what's known in the jargon of the relational model as
'encoding
data as column headings'. This contravenes a fundamental principle of the
database relational model, which is that data must be stored as values at
column positions in rows in tables, and in no other way.

A better model would be to have just one set of address columns and an
additional JobLocation column in the JobLocation table (a better name for
the
table would be JobLocations BTW as the convention is that table names are
normally plural or collective nouns, and column names singular nouns).
For
home addresses in the table the JobLocation value would be 1, for business
addresses 2. You can then join the Jobs and Joblocations tables on two
columns in the report's underlying query, e.g.

SELECT <column list>
FROM Jobs INNER JOIN JobLocations
ON Jobs.JobNumber = JobLocations.JobNumber
AND Jobs.JobLocation = JobLocations.JobLocation;

You can very easily change the table design by creating a blank table with
the JobNumber (or whatever the foreign key column is called), JobLocation
and
one set of address columns, and then using two append queries to insert
the
rows from the two sets of relevant columns of the existing table. For the
home addresses you'd append a 1 into the JobLocation column, for the
business
addresses a 2. In each case restrict the rows inserted to those with the
relevant address data by restricting the query to those where a suitable
column is not Null, e.g. for home addresses:

WHERE Customer_HomeNameNumber IS NOT NULL

and for business addresses:

WHERE Customer_BusinessNameNumber IS NOT NULL

Ken Sheridan
Stafford, England

Hi guys,

Once again i humbly beg for your wisdom and assistance!


I am making a report that shows either a home address or a business
address
depending on what the job requires...

i have 2 tables, Jobs and JobLocation. the job location table contains 2
entries, home and business.

ive created a textbox on the report with the following statement...

The idea is that if the job location is 1 (home) then the home address is
displayed. otherwise the business address is displayed.

For some reason, when i have [JobLocation=1] I only get home addresses
displayed and when i change it to [JobLocation=2] i get blank addresses
boxes except for the ones that should show a business address which are
displayed fine

=IIf([JobLocation]=1,[Customer_HomeNameNumber] & Chr$(13) & Chr$(10) &
[Customer_HomeStreet] & Chr$(13) & Chr$(10) & [Customer_HomeTown] &
Chr$(13)
& Chr$(10) & [Customer_HomeCounty] & Chr$(13) & Chr$(10) &
[Customer_HomeCountry] & Chr$(13) & Chr$(10) &
[Customer_HomePostcode],[Customer_BusinessNameNumber] & Chr$(13) &
Chr$(10)
& [Customer_BusinessStreet] & Chr$(13) & Chr$(10) &
[Customer_BusinessTown]
& Chr$(13) & Chr$(10) & [Customer_BusinessCounty] & Chr$(13) & Chr$(10) &
[Customer_BusinessCountry] & Chr$(13) & Chr$(10) &
[Customer_BusinessPostcode])


Please help!!

 

[Craig Armitage]

Its Ok, I fixed it!! actually the statement was completely fine, my test
data had the business and home address the wrong way round on the test
business record! Feel free to call me a muppet!

Thanks for your help

Hi Ken,

Thanks for your very in-depth reply. Ive tried to take my time and read
it
carefuly and I will probably have to read it a few more times just incase
ive missed what your saying. I just wanted to give you the background of
how my database is put together because it sounds like ive got it set up
how
you suggested anyway....

Okay, the database is for tracking jobs from my customers. I sometimes do
work for the same customer at their home or business premises and when
assigning a specific job to an engineer, its important the job shows the
correct address.

For this side of things my database has 3 tables which are basically laid
out as follows..

tbl_Customers -> this has fields such as CustomerID(PK),
Customer_FirstName,
Customer_LastName, Customer_BusinessName etc and also has all the fields
required to have the business details and home details etc..

tbl_JobLocations -> This has two fields.. there are two database entries
in
JobLocation.. "Home" and "Business"

JobLocationID (PK)
JobLocation

tbl_Jobs-> This contains all the info required about a specific job.. it
links back to both CustomerID and JobLocationID. JobLocationID is a
required field so it can never be Null.

When i make the report, with both the Home and Business address,
everything
is fine but that would be confusing to the engineer. I would like to show
just one address determined by JobLocation. im sure my error is in the
original statement of my first post but i dont understand where.

I dont mind re-designing my database if its been done in an incorrect way
but im sure the error is something simple.

Thanks

Craig

My guess would be that you have Nulls in the JobLocation columns for the
'business' jobs. Its important to realize that a Null is not a value, so
when you compare a Null with a value the answer is not True or False, but
Null. This would explain the behaviour you are experiencing because
comparing 1 with 1 returns True, 2 with 1 False, but 2 with Null or 1
with
Null returns Null.

With your present design you can avoid this by ensuring that all rows in
Jobs have a value in the JobLocation columns, i.e. 2 (or any value other
than
1) for the 'business' jobs.

However, the design is not a good one. By having two set of address
columns
you are doing what's known in the jargon of the relational model as
'encoding
data as column headings'. This contravenes a fundamental principle of
the
database relational model, which is that data must be stored as values at
column positions in rows in tables, and in no other way.

A better model would be to have just one set of address columns and an
additional JobLocation column in the JobLocation table (a better name for
the
table would be JobLocations BTW as the convention is that table names are
normally plural or collective nouns, and column names singular nouns).
For
home addresses in the table the JobLocation value would be 1, for
business
addresses 2. You can then join the Jobs and Joblocations tables on two
columns in the report's underlying query, e.g.

SELECT <column list>
FROM Jobs INNER JOIN JobLocations
ON Jobs.JobNumber = JobLocations.JobNumber
AND Jobs.JobLocation = JobLocations.JobLocation;

You can very easily change the table design by creating a blank table
with
the JobNumber (or whatever the foreign key column is called), JobLocation
and
one set of address columns, and then using two append queries to insert
the
rows from the two sets of relevant columns of the existing table. For
the
home addresses you'd append a 1 into the JobLocation column, for the
business
addresses a 2. In each case restrict the rows inserted to those with the
relevant address data by restricting the query to those where a suitable
column is not Null, e.g. for home addresses:

WHERE Customer_HomeNameNumber IS NOT NULL

and for business addresses:

WHERE Customer_BusinessNameNumber IS NOT NULL

Ken Sheridan
Stafford, England

Hi guys,

Once again i humbly beg for your wisdom and assistance!


I am making a report that shows either a home address or a business
address
depending on what the job requires...

i have 2 tables, Jobs and JobLocation. the job location table contains
2
entries, home and business.

ive created a textbox on the report with the following statement...

The idea is that if the job location is 1 (home) then the home address
is
displayed. otherwise the business address is displayed.

For some reason, when i have [JobLocation=1] I only get home addresses
displayed and when i change it to [JobLocation=2] i get blank addresses
boxes except for the ones that should show a business address which are
displayed fine

=IIf([JobLocation]=1,[Customer_HomeNameNumber] & Chr$(13) & Chr$(10) &
[Customer_HomeStreet] & Chr$(13) & Chr$(10) & [Customer_HomeTown] &
Chr$(13)
& Chr$(10) & [Customer_HomeCounty] & Chr$(13) & Chr$(10) &
[Customer_HomeCountry] & Chr$(13) & Chr$(10) &
[Customer_HomePostcode],[Customer_BusinessNameNumber] & Chr$(13) &
Chr$(10)
& [Customer_BusinessStreet] & Chr$(13) & Chr$(10) &
[Customer_BusinessTown]
& Chr$(13) & Chr$(10) & [Customer_BusinessCounty] & Chr$(13) & Chr$(10)
&
[Customer_BusinessCountry] & Chr$(13) & Chr$(10) &
[Customer_BusinessPostcode])


Please help!!