report options box text values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I'm working on putting a report together, and one of the items in the
detail of the report shows the values of an options box from the table. The
problem is, the data showing is the numerical value of the information I'm
looking for...example
option 1 is set for "phone"; in the report, value reporting is "1"
option 2 is set for "fax"; in the report, value reporting is "2"
I have a total of 6 different "select only 1" options.

Can I have Access report back "phone" and "fax" and whatever else I have
instead of the numerical values?

I thought about putting in a text field and having as the control source:
=Iif([Order Type]=1,"Phone",Iif([Order Type]=2,"EDI",Iif([Order
Type]=3,"AVL",Iif([Order Type]=4,"XML",Iif([Order Type]=5,"FAX",Iif([Order
Type]=6,"BARF",""))))))

The formula was accepted by Access, but it reported back the value "#ERROR"

Any help would be appreciated...I'm VERY limited in my ability for VB.

Thanks.
 
You should create a small lookup table of OrderTypes
tblOrderTypes
OTID OTName
1 phone
2 EDI
3 ....
...
6 BARF

Then add this table to your reports query and join the OTID field to your
[Order Type] field. Add the OTName to the query grid to display it in the
report.
 
Thanks Duane. Done; think I did something wrong. I got a "Type mismatch in
expression" error when I tried to run the query.

Duane Hookom said:
You should create a small lookup table of OrderTypes
tblOrderTypes
OTID OTName
1 phone
2 EDI
3 ....
...
6 BARF

Then add this table to your reports query and join the OTID field to your
[Order Type] field. Add the OTName to the query grid to display it in the
report.

--
Duane Hookom
MS Access MVP

The
bluezcruizer said:
Hi. I'm working on putting a report together, and one of the items in the
detail of the report shows the values of an options box from the table.
The
problem is, the data showing is the numerical value of the information I'm
looking for...example
option 1 is set for "phone"; in the report, value reporting is "1"
option 2 is set for "fax"; in the report, value reporting is "2"
I have a total of 6 different "select only 1" options.

Can I have Access report back "phone" and "fax" and whatever else I have
instead of the numerical values?

I thought about putting in a text field and having as the control source:
=Iif([Order Type]=1,"Phone",Iif([Order Type]=2,"EDI",Iif([Order
Type]=3,"AVL",Iif([Order Type]=4,"XML",Iif([Order Type]=5,"FAX",Iif([Order
Type]=6,"BARF",""))))))

The formula was accepted by Access, but it reported back the value
"#ERROR"

Any help would be appreciated...I'm VERY limited in my ability for VB.

Thanks.
Click to expand...
Click to expand...
 
I figured it out, Duane. I didn't create the right relationships; had to
delete and go back to my table, change the data type for Order Type field to
lookup...

....bottom line, I'm in business. Thanks.
bluezcruizer.

bluezcruizer said:
Thanks Duane. Done; think I did something wrong. I got a "Type mismatch in
expression" error when I tried to run the query.

Duane Hookom said:
You should create a small lookup table of OrderTypes
tblOrderTypes
OTID OTName
1 phone
2 EDI
3 ....
...
6 BARF

Then add this table to your reports query and join the OTID field to your
[Order Type] field. Add the OTName to the query grid to display it in the
report.

--
Duane Hookom
MS Access MVP

The
bluezcruizer said:
Hi. I'm working on putting a report together, and one of the items in the
detail of the report shows the values of an options box from the table.
The
problem is, the data showing is the numerical value of the information I'm
looking for...example
option 1 is set for "phone"; in the report, value reporting is "1"
option 2 is set for "fax"; in the report, value reporting is "2"
I have a total of 6 different "select only 1" options.

Can I have Access report back "phone" and "fax" and whatever else I have
instead of the numerical values?

I thought about putting in a text field and having as the control source:
=Iif([Order Type]=1,"Phone",Iif([Order Type]=2,"EDI",Iif([Order
Type]=3,"AVL",Iif([Order Type]=4,"XML",Iif([Order Type]=5,"FAX",Iif([Order
Type]=6,"BARF",""))))))

The formula was accepted by Access, but it reported back the value
"#ERROR"

Any help would be appreciated...I'm VERY limited in my ability for VB.

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top