Report data selection

  • Thread starter Thread starter Al Hotmail
  • Start date Start date
A

Al Hotmail

I need help with report data selection.
In a report I have the code below to print the first line and similar code
to print the 2nd, 3rd & 4th lines. I believe that a function using the
select case would be a better approach. I have tried to generate the
function with no success. PLEASE HELP Al

=IIf([tblFromAcode.FormAcode]=1,[Petition date],
IIf([tblFromAcode.FormAcode]=3,[petition date],
IIf([tblFromAcode.FormAcode]=6,Null,
IIf([tblFromAcode.FormAcode]=7,[Demit],
IIf([tblFromAcode.FormAcode]=8,[Death],
IIf([tblFromAcode.FormAcode]=9,[EA Degree],
IIf([tblFromAcode.FormAcode]=10,[FC Degree],
IIf([tblFromAcode.FormAcode]=11,[MM Degree],
IIf([tblFromAcode.FormAcode]=12,[Exemplified],
IIf(([tblFromAcode.FormAcode]=14 And [sendmail]=- 1),[address],
IIf(([tblFromAcode.FormAcode]=14 And [sendmail]=0),"**UNKNOWN**",
IIf([tblFromAcode.FormAcode]=16,"EA Deg. = " & [EA Degree],
IIf([tblFromAcode.FormAcode]=17,"???UNKNOWN???",
IIf([tblFromAcode.FormAcode]=18,[HomePhone]))))))))))))))
 
If you have that many alternate values, what about setting up a "lookup"
table that contains the translations?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I have even more alternate values
how would the lookup table work. I do not understand. how to create and use?
Al

Jeff Boyce said:
If you have that many alternate values, what about setting up a "lookup"
table that contains the translations?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Al Hotmail said:
I need help with report data selection.
In a report I have the code below to print the first line and similar
code to print the 2nd, 3rd & 4th lines. I believe that a function using
the select case would be a better approach. I have tried to generate the
function with no success. PLEASE HELP Al

=IIf([tblFromAcode.FormAcode]=1,[Petition date],
IIf([tblFromAcode.FormAcode]=3,[petition date],
IIf([tblFromAcode.FormAcode]=6,Null,
IIf([tblFromAcode.FormAcode]=7,[Demit],
IIf([tblFromAcode.FormAcode]=8,[Death],
IIf([tblFromAcode.FormAcode]=9,[EA Degree],
IIf([tblFromAcode.FormAcode]=10,[FC Degree],
IIf([tblFromAcode.FormAcode]=11,[MM Degree],
IIf([tblFromAcode.FormAcode]=12,[Exemplified],
IIf(([tblFromAcode.FormAcode]=14 And [sendmail]=- 1),[address],
IIf(([tblFromAcode.FormAcode]=14 And [sendmail]=0),"**UNKNOWN**",
IIf([tblFromAcode.FormAcode]=16,"EA Deg. = " & [EA Degree],
IIf([tblFromAcode.FormAcode]=17,"???UNKNOWN???",
IIf([tblFromAcode.FormAcode]=18,[HomePhone]))))))))))))))
Click to expand...
Click to expand...
 
A two-column table containing the values you are testing for in your IIF()
statements (field1) and the "translation" you are converting to (e.g., "8"
corresponds to "Death").

Regards

Jeff Boyce
Microsoft Office/Access MVP

AlTamres said:
I have even more alternate values
how would the lookup table work. I do not understand. how to create and
use?
Al

Jeff Boyce said:
If you have that many alternate values, what about setting up a "lookup"
table that contains the translations?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Al Hotmail said:
I need help with report data selection.
In a report I have the code below to print the first line and similar
code to print the 2nd, 3rd & 4th lines. I believe that a function using
the select case would be a better approach. I have tried to generate the
function with no success. PLEASE HELP Al

=IIf([tblFromAcode.FormAcode]=1,[Petition date],
IIf([tblFromAcode.FormAcode]=3,[petition date],
IIf([tblFromAcode.FormAcode]=6,Null,
IIf([tblFromAcode.FormAcode]=7,[Demit],
IIf([tblFromAcode.FormAcode]=8,[Death],
IIf([tblFromAcode.FormAcode]=9,[EA Degree],
IIf([tblFromAcode.FormAcode]=10,[FC Degree],
IIf([tblFromAcode.FormAcode]=11,[MM Degree],
IIf([tblFromAcode.FormAcode]=12,[Exemplified],
IIf(([tblFromAcode.FormAcode]=14 And [sendmail]=- 1),[address],
IIf(([tblFromAcode.FormAcode]=14 And [sendmail]=0),"**UNKNOWN**",
IIf([tblFromAcode.FormAcode]=16,"EA Deg. = " & [EA Degree],
IIf([tblFromAcode.FormAcode]=17,"???UNKNOWN???",
IIf([tblFromAcode.FormAcode]=18,[HomePhone]))))))))))))))
Click to expand...
Click to expand...
Click to expand...
 
I have a 2 column table, 1 field is labeled FormAcode the other is labeled
Event. My data table has a field labeled FormAcode and a column for each
event. how do I combine the information into a text box in my report?
by the way THANKS for your support al

Jeff Boyce said:
A two-column table containing the values you are testing for in your IIF()
statements (field1) and the "translation" you are converting to (e.g., "8"
corresponds to "Death").

Regards

Jeff Boyce
Microsoft Office/Access MVP

AlTamres said:
I have even more alternate values
how would the lookup table work. I do not understand. how to create and
use?
Al

Jeff Boyce said:
If you have that many alternate values, what about setting up a "lookup"
table that contains the translations?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need help with report data selection.
In a report I have the code below to print the first line and similar
code to print the 2nd, 3rd & 4th lines. I believe that a function using
the select case would be a better approach. I have tried to generate
the function with no success. PLEASE HELP Al

=IIf([tblFromAcode.FormAcode]=1,[Petition date],
IIf([tblFromAcode.FormAcode]=3,[petition date],
IIf([tblFromAcode.FormAcode]=6,Null,
IIf([tblFromAcode.FormAcode]=7,[Demit],
IIf([tblFromAcode.FormAcode]=8,[Death],
IIf([tblFromAcode.FormAcode]=9,[EA Degree],
IIf([tblFromAcode.FormAcode]=10,[FC Degree],
IIf([tblFromAcode.FormAcode]=11,[MM Degree],
IIf([tblFromAcode.FormAcode]=12,[Exemplified],
IIf(([tblFromAcode.FormAcode]=14 And [sendmail]=- 1),[address],
IIf(([tblFromAcode.FormAcode]=14 And [sendmail]=0),"**UNKNOWN**",
IIf([tblFromAcode.FormAcode]=16,"EA Deg. = " & [EA Degree],
IIf([tblFromAcode.FormAcode]=17,"???UNKNOWN???",
IIf([tblFromAcode.FormAcode]=18,[HomePhone]))))))))))))))
Click to expand...
Click to expand...
Click to expand...
 
Use a query to "fill" your report. In your query, join that 'translation'
table to the main table in which you have the values. Include the [Event]
field in your query (and, hence, in your report).

Regards

Jeff Boyce
Microsoft Office/Access MVP

AlTamres said:
I have a 2 column table, 1 field is labeled FormAcode the other is labeled
Event. My data table has a field labeled FormAcode and a column for each
event. how do I combine the information into a text box in my report?
by the way THANKS for your support al

Jeff Boyce said:
A two-column table containing the values you are testing for in your
IIF() statements (field1) and the "translation" you are converting to
(e.g., "8" corresponds to "Death").

Regards

Jeff Boyce
Microsoft Office/Access MVP

AlTamres said:
I have even more alternate values
how would the lookup table work. I do not understand. how to create and
use?
Al

If you have that many alternate values, what about setting up a
"lookup" table that contains the translations?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need help with report data selection.
In a report I have the code below to print the first line and similar
code to print the 2nd, 3rd & 4th lines. I believe that a function
using the select case would be a better approach. I have tried to
generate the function with no success. PLEASE HELP Al

=IIf([tblFromAcode.FormAcode]=1,[Petition date],
IIf([tblFromAcode.FormAcode]=3,[petition date],
IIf([tblFromAcode.FormAcode]=6,Null,
IIf([tblFromAcode.FormAcode]=7,[Demit],
IIf([tblFromAcode.FormAcode]=8,[Death],
IIf([tblFromAcode.FormAcode]=9,[EA Degree],
IIf([tblFromAcode.FormAcode]=10,[FC Degree],
IIf([tblFromAcode.FormAcode]=11,[MM Degree],
IIf([tblFromAcode.FormAcode]=12,[Exemplified],
IIf(([tblFromAcode.FormAcode]=14 And [sendmail]=- 1),[address],
IIf(([tblFromAcode.FormAcode]=14 And [sendmail]=0),"**UNKNOWN**",
IIf([tblFromAcode.FormAcode]=16,"EA Deg. = " & [EA Degree],
IIf([tblFromAcode.FormAcode]=17,"???UNKNOWN???",
IIf([tblFromAcode.FormAcode]=18,[HomePhone]))))))))))))))
Click to expand...
Click to expand...
Click to expand...
 
Jeff
I do not see how it will work because the main data table has all the
information, the second print line has has different fields then the 1st
and the 3rd line has different info. the report is grouped by events and
under each event are a list of the records related to the event for example
address change, the first line would contain the address, the second line
would have the city, state, zip, the third line would contain the phone
number for a record and then go to the next person that hade the same
FormAcode. when the last record with the same FormAcode is processed it
would go to the next FormAcode. Some FormAcode have only 1 line to print
before going to the next record. I think a Function for each print line
might be cleaner. Can you help me generate a function or a dlookup function
which would check against a separate event list for each line of print to
know that field to print.
THANKS AGAIN FOR YOUR HELP
AL


Jeff Boyce said:
Use a query to "fill" your report. In your query, join that 'translation'
table to the main table in which you have the values. Include the [Event]
field in your query (and, hence, in your report).

Regards

Jeff Boyce
Microsoft Office/Access MVP

AlTamres said:
I have a 2 column table, 1 field is labeled FormAcode the other is labeled
Event. My data table has a field labeled FormAcode and a column for each
event. how do I combine the information into a text box in my report?
by the way THANKS for your support al

Jeff Boyce said:
A two-column table containing the values you are testing for in your
IIF() statements (field1) and the "translation" you are converting to
(e.g., "8" corresponds to "Death").

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have even more alternate values
how would the lookup table work. I do not understand. how to create and
use?
Al

If you have that many alternate values, what about setting up a
"lookup" table that contains the translations?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need help with report data selection.
In a report I have the code below to print the first line and similar
code to print the 2nd, 3rd & 4th lines. I believe that a function
using the select case would be a better approach. I have tried to
generate the function with no success. PLEASE HELP Al

=IIf([tblFromAcode.FormAcode]=1,[Petition date],
IIf([tblFromAcode.FormAcode]=3,[petition date],
IIf([tblFromAcode.FormAcode]=6,Null,
IIf([tblFromAcode.FormAcode]=7,[Demit],
IIf([tblFromAcode.FormAcode]=8,[Death],
IIf([tblFromAcode.FormAcode]=9,[EA Degree],
IIf([tblFromAcode.FormAcode]=10,[FC Degree],
IIf([tblFromAcode.FormAcode]=11,[MM Degree],
IIf([tblFromAcode.FormAcode]=12,[Exemplified],
IIf(([tblFromAcode.FormAcode]=14 And [sendmail]=- 1),[address],
IIf(([tblFromAcode.FormAcode]=14 And [sendmail]=0),"**UNKNOWN**",
IIf([tblFromAcode.FormAcode]=16,"EA Deg. = " & [EA Degree],
IIf([tblFromAcode.FormAcode]=17,"???UNKNOWN???",
IIf([tblFromAcode.FormAcode]=18,[HomePhone]))))))))))))))
Click to expand...
Click to expand...
Click to expand...
 
I may have made a faulty assumption that your data was well-normalized.
Access' relationally-oriented features/functions don't work well (or easily)
if you feed it 'sheet data.

A simple pair of tables, one of them a "lookup" table, gives you an easy way
to have a main table of records in which one field is a value, for which
there is an [Event] you can "look up" via that lookup table.

If you are talking about records in a table where there are more than one
row per "record", you have a spreadsheet, not a relational database.

Start over, and describe the table structure you are using. Here's an
example:

tblPerson
PersonID
FirstName
LastName
DateOfBirth

Notice that there are not "second line" and "third line" records.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Al Hotmail said:
Jeff
I do not see how it will work because the main data table has all the
information, the second print line has has different fields then the 1st
and the 3rd line has different info. the report is grouped by events and
under each event are a list of the records related to the event for
example address change, the first line would contain the address, the
second line would have the city, state, zip, the third line would contain
the phone number for a record and then go to the next person that hade the
same FormAcode. when the last record with the same FormAcode is processed
it would go to the next FormAcode. Some FormAcode have only 1 line to
print before going to the next record. I think a Function for each print
line might be cleaner. Can you help me generate a function or a dlookup
function which would check against a separate event list for each line of
print to know that field to print.
THANKS AGAIN FOR YOUR HELP
AL


Jeff Boyce said:
Use a query to "fill" your report. In your query, join that
'translation' table to the main table in which you have the values.
Include the [Event] field in your query (and, hence, in your report).

Regards

Jeff Boyce
Microsoft Office/Access MVP

AlTamres said:
I have a 2 column table, 1 field is labeled FormAcode the other is
labeled Event. My data table has a field labeled FormAcode and a column
for each event. how do I combine the information into a text box in my
report?
by the way THANKS for your support al

A two-column table containing the values you are testing for in your
IIF() statements (field1) and the "translation" you are converting to
(e.g., "8" corresponds to "Death").

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have even more alternate values
how would the lookup table work. I do not understand. how to create
and use?
Al

If you have that many alternate values, what about setting up a
"lookup" table that contains the translations?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need help with report data selection.
In a report I have the code below to print the first line and
similar code to print the 2nd, 3rd & 4th lines. I believe that a
function using the select case would be a better approach. I have
tried to generate the function with no success. PLEASE HELP Al

=IIf([tblFromAcode.FormAcode]=1,[Petition date],
IIf([tblFromAcode.FormAcode]=3,[petition date],
IIf([tblFromAcode.FormAcode]=6,Null,
IIf([tblFromAcode.FormAcode]=7,[Demit],
IIf([tblFromAcode.FormAcode]=8,[Death],
IIf([tblFromAcode.FormAcode]=9,[EA Degree],
IIf([tblFromAcode.FormAcode]=10,[FC Degree],
IIf([tblFromAcode.FormAcode]=11,[MM Degree],
IIf([tblFromAcode.FormAcode]=12,[Exemplified],
IIf(([tblFromAcode.FormAcode]=14 And [sendmail]=- 1),[address],
IIf(([tblFromAcode.FormAcode]=14 And [sendmail]=0),"**UNKNOWN**",
IIf([tblFromAcode.FormAcode]=16,"EA Deg. = " & [EA Degree],
IIf([tblFromAcode.FormAcode]=17,"???UNKNOWN???",
IIf([tblFromAcode.FormAcode]=18,[HomePhone]))))))))))))))
Click to expand...
Click to expand...
Click to expand...
 
Back
Top