Accessing query fields from code with in a report

[Dennis]

Hi,

OS: XP Pro SP3
Access: XP Office Pro - SP3

I have a report that is based upon a query. I want to format the City, St &
Zip, but I can not "see" my variables. So, how do I get access to the
variables?

The field names in the query are:

City
State
PostalCode

My code in the Detail Print event is as follows:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim strZip As String
strZip = Left([PostalCode], 5)
End Sub

The value of PrintCount is 1. The error message I recieve is "... can't
find the field 'PostalCode' referred to in your expression.

I have tried the following to access the variable in the Immediate window
while in the Print Event of the Detail section of the report.

?PostalCode
?[PostalCode]
?[mailinglist].[PostalCode]
?me.PostalCode
?[qryMemberReport]![PostalCode]
?Me.PostalCode.Value

I can access the following variables from my query record:
?MemName = Abernathy, Joe
?MemNo = 1234567
?Address1 = 123 Main Street
?HomePhone = 3211234567
?MemType = P
?DateJoined = 12/1/2001
?DateExpires = 12/31/2099
?BirthDate = 1/1/1950
?MemberTerm = L
?PrintCount = 1

I could not access the following fields:
?PostNo
?LastName
?FirstName
?Suffix
?City
?State
?PostalCode
?PaidBy


So I can access some of field on the query record, but not all of them. Any
suggestions?

 

[Allen Browne]

Put text boxes on the report (visible = No if you wish.)
You will then be able to use the data in your code.

 

[Dennis]

Thanks, worked great!
--
Dennis

Put text boxes on the report (visible = No if you wish.)
You will then be able to use the data in your code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

OS: XP Pro SP3
Access: XP Office Pro - SP3

I have a report that is based upon a query. I want to format the City, St
&
Zip, but I can not "see" my variables. So, how do I get access to the
variables?

The field names in the query are:

City
State
PostalCode

My code in the Detail Print event is as follows:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim strZip As String
strZip = Left([PostalCode], 5)
End Sub

The value of PrintCount is 1. The error message I recieve is "... can't
find the field 'PostalCode' referred to in your expression.

I have tried the following to access the variable in the Immediate window
while in the Print Event of the Detail section of the report.

?PostalCode
?[PostalCode]
?[mailinglist].[PostalCode]
?me.PostalCode
?[qryMemberReport]![PostalCode]
?Me.PostalCode.Value

I can access the following variables from my query record:
?MemName = Abernathy, Joe
?MemNo = 1234567
?Address1 = 123 Main Street
?HomePhone = 3211234567
?MemType = P
?DateJoined = 12/1/2001
?DateExpires = 12/31/2099
?BirthDate = 1/1/1950
?MemberTerm = L
?PrintCount = 1

I could not access the following fields:
?PostNo
?LastName
?FirstName
?Suffix
?City
?State
?PostalCode
?PaidBy


So I can access some of field on the query record, but not all of them.
Any
suggestions?

 

[Dennis]

Follow up question.

I have a mailing label form where I have to access Name1, Name, Address1,
Address2, City, St, Zip, NewLetNm1, and NewLetNm2.

If one option is taken, I will print Name 1 and Name 2, if another option is
taken I'll print NewLetNm1 and NewLetNm2 instead of Name1 and Name 2. There
is not enough space on the labels to print Name1, Name2, Address1, and
Address2 so I'll have to put a little code in to figure out how to print
three lines from four. That is not a concern.

There is not physically enough room to fit all of these fields on the label
(setting the fields to invisible). I could lay one field on top of another,
that gets very complicated very quickly.

Is having the field on the form the only way to access it? Is so, what do
you do when there is no space on the report / mailing labels?

Dennis

Thanks, worked great!
--
Dennis

Put text boxes on the report (visible = No if you wish.)
You will then be able to use the data in your code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

OS: XP Pro SP3
Access: XP Office Pro - SP3

I have a report that is based upon a query. I want to format the City, St
&
Zip, but I can not "see" my variables. So, how do I get access to the
variables?

The field names in the query are:

City
State
PostalCode

My code in the Detail Print event is as follows:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim strZip As String
strZip = Left([PostalCode], 5)
End Sub

The value of PrintCount is 1. The error message I recieve is "... can't
find the field 'PostalCode' referred to in your expression.

I have tried the following to access the variable in the Immediate window
while in the Print Event of the Detail section of the report.

?PostalCode
?[PostalCode]
?[mailinglist].[PostalCode]
?me.PostalCode
?[qryMemberReport]![PostalCode]
?Me.PostalCode.Value

I can access the following variables from my query record:
?MemName = Abernathy, Joe
?MemNo = 1234567
?Address1 = 123 Main Street
?HomePhone = 3211234567
?MemType = P
?DateJoined = 12/1/2001
?DateExpires = 12/31/2099
?BirthDate = 1/1/1950
?MemberTerm = L
?PrintCount = 1

I could not access the following fields:
?PostNo
?LastName
?FirstName
?Suffix
?City
?State
?PostalCode
?PaidBy


So I can access some of field on the query record, but not all of them.
Any
suggestions?

 

[Allen Browne]

Okay, there are several levels of issue in this question.

At the deepest level, if a client can have multiple addresses, there seems
to be a one-to-many relation between clients and addresses, so it might be a
good idea to put the addresses in a related table. (Depending on the data,
one address may also be for multiple clients, so it could be a many to
many.) The addresses table (or the junction table if it's many-to-many) will
have a field indicating the address type, and another field indicating the
priority (preferred addresses), and your query will draw the top 1 preferred
address of the right type for the client and feed it to the report. This
solves the problem completely, by using a relational database design at the
table level.

I'm not sure why clients would have multiple names (nicknames? addressee
names? Or are these actually multiple people in the one household/company?)
Perhaps this may be relevant:
People in households and companies - Modeling human relationships
at:
http://allenbrowne.com/AppHuman.html
Again, the article deals with how to store the data correctly at the table
level.

In essence, whenever you see repeating fields (like Name1, Name2, ..., or
Address1, Address2, ...), it always means you need a related table where you
can have many records instead of cramming them all into one table with many
fields.

If it's not possible to build a relational design, you may be able to design
a new report, and get away with expressions such as:
=IIf([Name1] Is Null, [Name2], [Name1])
and so on. The issue is that the report optimizer in Access tries to be too
smart, and doesn't fetch all the fields in the source query if it can't see
where they are used. Typically expressions like the above work if you
include them from the very start of the report, but may not work if you just
add them later. (BTW, it's important to ensure that the Name of this text
box is not the same as the name of a field, since Access gets confused if it
has the same name as a field but is bound to something else.)

Finally, if you can't do that either, you can change the height and width of
the text boxes to very small (e.g. 0.05") so they don't use up too much
space.

 

[Dennis]

Allen,

Name 1 & Name 2 / Address 1 & Address 2 are my standard variable names for
Name Line 1, Name Line 2, etc.. I can not fit some full names on the a
single line of a mailing label so I have to break some names into two lines.

Now, Name and NewsLetterName can be two different name on the same record.
This is because the user will:

1. Mail one new letter to a household regardless of how many members live
in that house (husband / wife). This mailing label needs to have all of the
member's name on the individual label. (User requirement). The user
manually decided on which record the will place the new letter name. Since
the new letter name is a single field, I just put it on the the member record
for the sake of simplicity. In this case, I use the NewLetterName for my
mailing lables and onliy send it to members that have the NewsLetterName.
Note: each member has their own member record, but only one of them has the
NewsLetterName field filled in.

2. Send out individual letters to all individual members, regardless of how
many members live in the house. In this case, I use the member's name.
(User requirement.)

I can do two different mailing list routines, but this means two very
similar mailing label programs, which doubles my maintenance and change
management issues. Always a balance act between ease of development and ease
of maintenace.

I did read the human relationship model and I think that it will be very
usefull in my next project, which does deal with people who are customers
both because they belong to a company who is a customer and who are also
customers from their household. Very interesting ideas. It will be of great
help.

Thanks once again for your invaluable help!