report field dependent on another

J

J

I have a membership database that, among other things, has a home and
business address for each member then a preferred address. Occasionally we
need to print a report that shows the preferred address for each member.

If Member A prefers the work address, Member B prefers the work address and
Member C prefers the home address - I want just the preferred address to
print on a report. How do I do that? I've looked but cannot find an
applicable post here or elsewhere.

Right now all of the records are in one table and this is in Access 2000.

Thanks.
 
F

Fons Ponsioen

You could make just a short query somewhat like this:
SELECT IIf([Preferred]=1,[Address1],[Address2]) AS
Address, IIf([Preferred]=1,[City1],[City2]) AS City, IIf
([Preferred]=1,[Zip1],[Zip2]) AS Zip
FROM [Address1-2];
I made preferred a numeric indicating which was preferred
address1 or address2 and this query selects than the
corresponding city and zip. You can add state and
anything else as applicable.
This query is tahn used as the source for your report.
Hope this helps.
Fons
 
J

J

Fons said:
You could make just a short query somewhat like this:
SELECT IIf([Preferred]=1,[Address1],[Address2]) AS
Address, IIf([Preferred]=1,[City1],[City2]) AS City, IIf
([Preferred]=1,[Zip1],[Zip2]) AS Zip
FROM [Address1-2];
I made preferred a numeric indicating which was preferred
address1 or address2 and this query selects than the
corresponding city and zip. You can add state and
anything else as applicable.
This query is tahn used as the source for your report.
Hope this helps.
Fons
-----Original Message-----
I have a membership database that, among other things, has a home and
business address for each member then a preferred address.
Occasionally we need to print a report that shows the preferred
address for each member.

If Member A prefers the work address, Member B prefers the work
address and Member C prefers the home address - I want just the
preferred address to print on a report. How do I do that? I've
looked but cannot find an applicable post here or elsewhere.

Right now all of the records are in one table and this is in Access
2000.

Thanks.
Click to expand...
Click to expand...
I cannot get this to work.

I have the following fields in the table:

BStreet
BCity
BState
BZip

HStreet
HCity
HState
HZip

PrefAddress (this field contains either "Business" or "Home")

What I need to do:
Create a query or report that will show either BSteet, BCity, BState, BZip
OR HSteet, HCity, HState, HZip - depending on if PrefAddress is "Business"
or "Home"

I tried to adapt the expression from Fons in every way but cannot get it to
work.
 
F

Fons Ponsioen

Hi J
Well, let's try again.
We are going to create a query which will select the
desired portions of your table for the data elements and
than you base the report on the query.
We will create the fields FStreet, FCity, FState, and FZip.
I just prefixed with F to indicate Final.
So start a new query in the design mode, and include the
table where you have the address data.
In the first Column "Field" enter;
FStreet:IIF([PrefAddress] = "Business",[BStreet],[HStreet])
Now in the second Column "Field" enter;
FCity:IIF([PrefAddress] = "Business",[BCity],[HCity])
Now in the third Column "Field" enter;
FState:IIF([PrefAddress] = "Business",[BState],[HState])
Now in the fourth Column "Field" enter;
FZip:IIF([PrefAddress] = "Business",[BZip],[HZip])
What these IIF statements do is they check for the
condition PrefAddress] = "Business" if this is true, the
first data element [Bzip] will be used otherwise it will
default to the second [HZip].
Now use this query as the data source for your report.
Hope this helps.
Sorry it took me a while to respond, but you see, I have
to work for a living also.
Fons
-----Original Message-----


Fons said:
You could make just a short query somewhat like this:
SELECT IIf([Preferred]=1,[Address1],[Address2]) AS
Address, IIf([Preferred]=1,[City1],[City2]) AS City, IIf
([Preferred]=1,[Zip1],[Zip2]) AS Zip
FROM [Address1-2];
I made preferred a numeric indicating which was preferred
address1 or address2 and this query selects than the
corresponding city and zip. You can add state and
anything else as applicable.
This query is tahn used as the source for your report.
Hope this helps.
Fons
-----Original Message-----
I have a membership database that, among other things, has a home and
business address for each member then a preferred address.
Occasionally we need to print a report that shows the preferred
address for each member.

If Member A prefers the work address, Member B prefers the work
address and Member C prefers the home address - I want just the
preferred address to print on a report. How do I do that? I've
looked but cannot find an applicable post here or elsewhere.

Right now all of the records are in one table and this is in Access
2000.

Thanks.
Click to expand...
Click to expand...
I cannot get this to work.

I have the following fields in the table:

BStreet
BCity
BState
BZip

HStreet
HCity
HState
HZip

PrefAddress (this field contains either "Business" or "Home")

What I need to do:
Create a query or report that will show either BSteet, BCity, BState, BZip
OR HSteet, HCity, HState, HZip - depending on if PrefAddress is "Business"
or "Home"

I tried to adapt the expression from Fons in every way but cannot get it to
work.


.
Click to expand...
 
J

J

Fons said:
Hi J
Well, let's try again.
We are going to create a query which will select the
desired portions of your table for the data elements and
than you base the report on the query.
We will create the fields FStreet, FCity, FState, and FZip.
I just prefixed with F to indicate Final.
So start a new query in the design mode, and include the
table where you have the address data.
In the first Column "Field" enter;
FStreet:IIF([PrefAddress] = "Business",[BStreet],[HStreet])
Now in the second Column "Field" enter;
FCity:IIF([PrefAddress] = "Business",[BCity],[HCity])
Now in the third Column "Field" enter;
FState:IIF([PrefAddress] = "Business",[BState],[HState])
Now in the fourth Column "Field" enter;
FZip:IIF([PrefAddress] = "Business",[BZip],[HZip])
What these IIF statements do is they check for the
condition PrefAddress] = "Business" if this is true, the
first data element [Bzip] will be used otherwise it will
default to the second [HZip].
Now use this query as the data source for your report.
Hope this helps.
Sorry it took me a while to respond, but you see, I have
to work for a living also.
Fons
-----Original Message-----


Fons said:
You could make just a short query somewhat like this:
SELECT IIf([Preferred]=1,[Address1],[Address2]) AS
Address, IIf([Preferred]=1,[City1],[City2]) AS City, IIf
([Preferred]=1,[Zip1],[Zip2]) AS Zip
FROM [Address1-2];
I made preferred a numeric indicating which was preferred
address1 or address2 and this query selects than the
corresponding city and zip. You can add state and
anything else as applicable.
This query is tahn used as the source for your report.
Hope this helps.
Fons
-----Original Message-----
I have a membership database that, among other things, has a home
and business address for each member then a preferred address.
Occasionally we need to print a report that shows the preferred
address for each member.

If Member A prefers the work address, Member B prefers the work
address and Member C prefers the home address - I want just the
preferred address to print on a report. How do I do that? I've
looked but cannot find an applicable post here or elsewhere.

Right now all of the records are in one table and this is in Access
2000.

Thanks.
Click to expand...
I cannot get this to work.

I have the following fields in the table:

BStreet
BCity
BState
BZip

HStreet
HCity
HState
HZip

PrefAddress (this field contains either "Business" or "Home")

What I need to do:
Create a query or report that will show either BSteet, BCity,
BState, BZip OR HSteet, HCity, HState, HZip - depending on if
PrefAddress is "Business" or "Home"

I tried to adapt the expression from Fons in every way but cannot
get it to work.


.
Click to expand...
Click to expand...
That worked. Would it have made a difference that I was putting the
expression in Criteria instead of Field? I saw something last night while
trying get it to work that recommended not putting an expression in Field.
Icouldn't find anything that said why? not to do that.
 
F

Fons Ponsioen

Glad you got it worked out.
It is possible to use a formula in a criteria however be
careful as you found out. Putting something in a criteria
askes for an evaluation and the result will be a true or
false.
Take care.
Fons
-----Original Message-----


Fons said:
Hi J
Well, let's try again.
We are going to create a query which will select the
desired portions of your table for the data elements and
than you base the report on the query.
We will create the fields FStreet, FCity, FState, and FZip.
I just prefixed with F to indicate Final.
So start a new query in the design mode, and include the
table where you have the address data.
In the first Column "Field" enter;
FStreet:IIF([PrefAddress] = "Business",[BStreet], [HStreet])
Now in the second Column "Field" enter;
FCity:IIF([PrefAddress] = "Business",[BCity],[HCity])
Now in the third Column "Field" enter;
FState:IIF([PrefAddress] = "Business",[BState],[HState])
Now in the fourth Column "Field" enter;
FZip:IIF([PrefAddress] = "Business",[BZip],[HZip])
What these IIF statements do is they check for the
condition PrefAddress] = "Business" if this is true, the
first data element [Bzip] will be used otherwise it will
default to the second [HZip].
Now use this query as the data source for your report.
Hope this helps.
Sorry it took me a while to respond, but you see, I have
to work for a living also.
Fons
-----Original Message-----


Fons Ponsioen wrote:
You could make just a short query somewhat like this:
SELECT IIf([Preferred]=1,[Address1],[Address2]) AS
Address, IIf([Preferred]=1,[City1],[City2]) AS City, IIf
([Preferred]=1,[Zip1],[Zip2]) AS Zip
FROM [Address1-2];
I made preferred a numeric indicating which was preferred
address1 or address2 and this query selects than the
corresponding city and zip. You can add state and
anything else as applicable.
This query is tahn used as the source for your report.
Hope this helps.
Fons
-----Original Message-----
I have a membership database that, among other things, has a home
and business address for each member then a preferred address.
Occasionally we need to print a report that shows the preferred
address for each member.

If Member A prefers the work address, Member B prefers the work
address and Member C prefers the home address - I want just the
preferred address to print on a report. How do I do that? I've
looked but cannot find an applicable post here or elsewhere.

Right now all of the records are in one table and this is in Access
2000.

Thanks.


I cannot get this to work.

I have the following fields in the table:

BStreet
BCity
BState
BZip

HStreet
HCity
HState
HZip

PrefAddress (this field contains either "Business" or "Home")

What I need to do:
Create a query or report that will show either BSteet, BCity,
BState, BZip OR HSteet, HCity, HState, HZip - depending on if
PrefAddress is "Business" or "Home"

I tried to adapt the expression from Fons in every way but cannot
get it to work.


.
Click to expand...
Click to expand...
That worked. Would it have made a difference that I was putting the
expression in Criteria instead of Field? I saw something last night while
trying get it to work that recommended not putting an expression in Field.
Icouldn't find anything that said why? not to do that.


.
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

Report printing multiple pages based on query 2
Textbox with multiple categories on report 1
Report setup 3
Problem Printing Images in a Report 5
To show the other words on the report 6
create report based on data from other form? 7
fake checkbox based off lookup column 2
data type mismatch 4

Top