Field Blank in Report

R

R Marko

I've recently added a combo box which pulls information from our client list
in order to populate the Address, City, State, Zip, Phone, Fax in the new
record. Works great.
In the report, all of the information prints. Life is Good.

In an effort to save space (and paper) on several reports I created a
"SortNAME" field

SortNAME: IIf(IsNull([ACRONYM]),[ClientName],[ACRONYM])

so the acronym prints instead of the long name.

My problem.....In those records where the drop down, combo box was used to
populate the client name, the client name does not print when I request the
"SortNAME"

If I insert the field "ClientName" on the report, it prints.
If I use the field "SortNAME" on the report, it's blank (only in the
records that were populated by the combo box)

Any ideas???

Rhonda
 
J

Jeff Boyce

Rhonda

I'm having trouble visualizing your underlying data structure. You
described combo boxes, reports, ..., but I don't see where the original
table data is coming from, nor do I see the query that you are using to
"feed" the report(s).

If you put that same IIF() function into a query against your underlying
table(s), you might be able to use that without issue in your report
definition.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
R

R Marko

It's rather a simple procedure. The original table, [tblClient] houses all
of the information.

[frmClient] is how we enter the information. Simple, Simple.

On occasion, clients will return with other projects. To make the data
entry easy, I replaced the field [ClientName] with a combo box which gets the
information from [tblClient] and populates the address, city, state, etc
fields, enabling us to go right to the "meat and potatoes" of the form and
enter information about the new project.

Private Sub cmbGroupName_AfterUpdate()

Me!ACRONYM = Me![cmbGroupName].Column(2)
Me!SALUTATION = Me![cmbGroupName].Column(3)
Me!FIRSTNAME = Me![cmbGroupName].Column(4)
Me!MIDDLEINITIAL = Me![cmbGroupName].Column(5)
Me!CONTACT = Me![cmbGroupName].Column(6)
Me!NICKNAME = Me![cmbGroupName].Column(7)
Me!TITLE = Me![cmbGroupName].Column(8)
Me!ADDRESS1 = Me![cmbGroupName].Column(9)
Me!ADDRESS2 = Me![cmbGroupName].Column(10)
Me!CITY = Me![cmbGroupName].Column(11)
Me!STATE = Me![cmbGroupName].Column(12)
Me!ZIP = Me![cmbGroupName].Column(13)
Me!COUNTRY = Me![cmbGroupName].Column(14)
Me!Phone = Me![cmbGroupName].Column(15)
Me!EXT = Me![cmbGroupName].Column(16)
Me!FAX = Me![cmbGroupName].Column(17)
Me!HOMEPHONE = Me![cmbGroupName].Column(18)
Me!EMAIL = Me![cmbGroupName].Column(19)

End Sub


I created a simple report that lists all of the clients. To the right of
the name, other information, taken from [tblClient] is listed.

Some of our clients are associations with long names, so we use acronyms.
On the report, where I want to show the clients name, I created a field,
[SortName] and entered...

SortNAME: IIf(IsNull([ACRONYM]),[ClientName],[ACRONYM])


When I replaced [SortName] with [ClientName] it works fine.
What is wrong with my "If" statement?

Hope this explains it.
Thank you,
Rhonda

Jeff Boyce said:
Rhonda

I'm having trouble visualizing your underlying data structure. You
described combo boxes, reports, ..., but I don't see where the original
table data is coming from, nor do I see the query that you are using to
"feed" the report(s).

If you put that same IIF() function into a query against your underlying
table(s), you might be able to use that without issue in your report
definition.

Regards

Jeff Boyce
Microsoft Office/Access MVP


R Marko said:
I've recently added a combo box which pulls information from our client
list
in order to populate the Address, City, State, Zip, Phone, Fax in the new
record. Works great.
In the report, all of the information prints. Life is Good.

In an effort to save space (and paper) on several reports I created a
"SortNAME" field

SortNAME: IIf(IsNull([ACRONYM]),[ClientName],[ACRONYM])

so the acronym prints instead of the long name.

My problem.....In those records where the drop down, combo box was used to
populate the client name, the client name does not print when I request
the
"SortNAME"

If I insert the field "ClientName" on the report, it prints.
If I use the field "SortNAME" on the report, it's blank (only in the
records that were populated by the combo box)

Any ideas???

Rhonda
Click to expand...
Click to expand...
 
J

Jeff Boyce

Rhonda

Please re-read my response. Take your IIF() statement out of the report and
put it in the query that returns the rows of data that feed the report.

Regards

Jeff Boyce
Microsoft Office/Access MVP


R Marko said:
It's rather a simple procedure. The original table, [tblClient] houses
all
of the information.

[frmClient] is how we enter the information. Simple, Simple.

On occasion, clients will return with other projects. To make the data
entry easy, I replaced the field [ClientName] with a combo box which gets
the
information from [tblClient] and populates the address, city, state, etc
fields, enabling us to go right to the "meat and potatoes" of the form and
enter information about the new project.

Private Sub cmbGroupName_AfterUpdate()

Me!ACRONYM = Me![cmbGroupName].Column(2)
Me!SALUTATION = Me![cmbGroupName].Column(3)
Me!FIRSTNAME = Me![cmbGroupName].Column(4)
Me!MIDDLEINITIAL = Me![cmbGroupName].Column(5)
Me!CONTACT = Me![cmbGroupName].Column(6)
Me!NICKNAME = Me![cmbGroupName].Column(7)
Me!TITLE = Me![cmbGroupName].Column(8)
Me!ADDRESS1 = Me![cmbGroupName].Column(9)
Me!ADDRESS2 = Me![cmbGroupName].Column(10)
Me!CITY = Me![cmbGroupName].Column(11)
Me!STATE = Me![cmbGroupName].Column(12)
Me!ZIP = Me![cmbGroupName].Column(13)
Me!COUNTRY = Me![cmbGroupName].Column(14)
Me!Phone = Me![cmbGroupName].Column(15)
Me!EXT = Me![cmbGroupName].Column(16)
Me!FAX = Me![cmbGroupName].Column(17)
Me!HOMEPHONE = Me![cmbGroupName].Column(18)
Me!EMAIL = Me![cmbGroupName].Column(19)

End Sub


I created a simple report that lists all of the clients. To the right of
the name, other information, taken from [tblClient] is listed.

Some of our clients are associations with long names, so we use acronyms.
On the report, where I want to show the clients name, I created a field,
[SortName] and entered...

SortNAME: IIf(IsNull([ACRONYM]),[ClientName],[ACRONYM])


When I replaced [SortName] with [ClientName] it works fine.
What is wrong with my "If" statement?

Hope this explains it.
Thank you,
Rhonda

Jeff Boyce said:
Rhonda

I'm having trouble visualizing your underlying data structure. You
described combo boxes, reports, ..., but I don't see where the original
table data is coming from, nor do I see the query that you are using to
"feed" the report(s).

If you put that same IIF() function into a query against your underlying
table(s), you might be able to use that without issue in your report
definition.

Regards

Jeff Boyce
Microsoft Office/Access MVP


R Marko said:
I've recently added a combo box which pulls information from our client
list
in order to populate the Address, City, State, Zip, Phone, Fax in the
new
record. Works great.
In the report, all of the information prints. Life is Good.

In an effort to save space (and paper) on several reports I created a
"SortNAME" field

SortNAME: IIf(IsNull([ACRONYM]),[ClientName],[ACRONYM])

so the acronym prints instead of the long name.

My problem.....In those records where the drop down, combo box was used
to
populate the client name, the client name does not print when I request
the
"SortNAME"

If I insert the field "ClientName" on the report, it prints.
If I use the field "SortNAME" on the report, it's blank (only in the
records that were populated by the combo box)

Any ideas???

Rhonda
Click to expand...
Click to expand...
Click to expand...
 
R

R Marko

I'm not sure if I understand exactly what to do.
1. Create a query from tblClient
2. In the [ClientName] field, paste the IIf statement?
Is that what you're saying?

Jeff Boyce said:
Rhonda

Please re-read my response. Take your IIF() statement out of the report and
put it in the query that returns the rows of data that feed the report.

Regards

Jeff Boyce
Microsoft Office/Access MVP


R Marko said:
It's rather a simple procedure. The original table, [tblClient] houses
all
of the information.

[frmClient] is how we enter the information. Simple, Simple.

On occasion, clients will return with other projects. To make the data
entry easy, I replaced the field [ClientName] with a combo box which gets
the
information from [tblClient] and populates the address, city, state, etc
fields, enabling us to go right to the "meat and potatoes" of the form and
enter information about the new project.

Private Sub cmbGroupName_AfterUpdate()

Me!ACRONYM = Me![cmbGroupName].Column(2)
Me!SALUTATION = Me![cmbGroupName].Column(3)
Me!FIRSTNAME = Me![cmbGroupName].Column(4)
Me!MIDDLEINITIAL = Me![cmbGroupName].Column(5)
Me!CONTACT = Me![cmbGroupName].Column(6)
Me!NICKNAME = Me![cmbGroupName].Column(7)
Me!TITLE = Me![cmbGroupName].Column(8)
Me!ADDRESS1 = Me![cmbGroupName].Column(9)
Me!ADDRESS2 = Me![cmbGroupName].Column(10)
Me!CITY = Me![cmbGroupName].Column(11)
Me!STATE = Me![cmbGroupName].Column(12)
Me!ZIP = Me![cmbGroupName].Column(13)
Me!COUNTRY = Me![cmbGroupName].Column(14)
Me!Phone = Me![cmbGroupName].Column(15)
Me!EXT = Me![cmbGroupName].Column(16)
Me!FAX = Me![cmbGroupName].Column(17)
Me!HOMEPHONE = Me![cmbGroupName].Column(18)
Me!EMAIL = Me![cmbGroupName].Column(19)

End Sub


I created a simple report that lists all of the clients. To the right of
the name, other information, taken from [tblClient] is listed.

Some of our clients are associations with long names, so we use acronyms.
On the report, where I want to show the clients name, I created a field,
[SortName] and entered...

SortNAME: IIf(IsNull([ACRONYM]),[ClientName],[ACRONYM])


When I replaced [SortName] with [ClientName] it works fine.
What is wrong with my "If" statement?

Hope this explains it.
Thank you,
Rhonda

Jeff Boyce said:
Rhonda

I'm having trouble visualizing your underlying data structure. You
described combo boxes, reports, ..., but I don't see where the original
table data is coming from, nor do I see the query that you are using to
"feed" the report(s).

If you put that same IIF() function into a query against your underlying
table(s), you might be able to use that without issue in your report
definition.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I've recently added a combo box which pulls information from our client
list
in order to populate the Address, City, State, Zip, Phone, Fax in the
new
record. Works great.
In the report, all of the information prints. Life is Good.

In an effort to save space (and paper) on several reports I created a
"SortNAME" field

SortNAME: IIf(IsNull([ACRONYM]),[ClientName],[ACRONYM])

so the acronym prints instead of the long name.

My problem.....In those records where the drop down, combo box was used
to
populate the client name, the client name does not print when I request
the
"SortNAME"

If I insert the field "ClientName" on the report, it prints.
If I use the field "SortNAME" on the report, it's blank (only in the
records that were populated by the combo box)

Any ideas???

Rhonda
Click to expand...
Click to expand...
Click to expand...
 
R

R Marko

I'm sorry, I just realized what you're saying.
I didn't explain myself well before. The report does get it's information
from a query. It's in the query that I created the [SortName] field.

Rhonda

Jeff Boyce said:
Rhonda

Please re-read my response. Take your IIF() statement out of the report and
put it in the query that returns the rows of data that feed the report.

Regards

Jeff Boyce
Microsoft Office/Access MVP


R Marko said:
It's rather a simple procedure. The original table, [tblClient] houses
all
of the information.

[frmClient] is how we enter the information. Simple, Simple.

On occasion, clients will return with other projects. To make the data
entry easy, I replaced the field [ClientName] with a combo box which gets
the
information from [tblClient] and populates the address, city, state, etc
fields, enabling us to go right to the "meat and potatoes" of the form and
enter information about the new project.

Private Sub cmbGroupName_AfterUpdate()

Me!ACRONYM = Me![cmbGroupName].Column(2)
Me!SALUTATION = Me![cmbGroupName].Column(3)
Me!FIRSTNAME = Me![cmbGroupName].Column(4)
Me!MIDDLEINITIAL = Me![cmbGroupName].Column(5)
Me!CONTACT = Me![cmbGroupName].Column(6)
Me!NICKNAME = Me![cmbGroupName].Column(7)
Me!TITLE = Me![cmbGroupName].Column(8)
Me!ADDRESS1 = Me![cmbGroupName].Column(9)
Me!ADDRESS2 = Me![cmbGroupName].Column(10)
Me!CITY = Me![cmbGroupName].Column(11)
Me!STATE = Me![cmbGroupName].Column(12)
Me!ZIP = Me![cmbGroupName].Column(13)
Me!COUNTRY = Me![cmbGroupName].Column(14)
Me!Phone = Me![cmbGroupName].Column(15)
Me!EXT = Me![cmbGroupName].Column(16)
Me!FAX = Me![cmbGroupName].Column(17)
Me!HOMEPHONE = Me![cmbGroupName].Column(18)
Me!EMAIL = Me![cmbGroupName].Column(19)

End Sub


I created a simple report that lists all of the clients. To the right of
the name, other information, taken from [tblClient] is listed.

Some of our clients are associations with long names, so we use acronyms.
On the report, where I want to show the clients name, I created a field,
[SortName] and entered...

SortNAME: IIf(IsNull([ACRONYM]),[ClientName],[ACRONYM])


When I replaced [SortName] with [ClientName] it works fine.
What is wrong with my "If" statement?

Hope this explains it.
Thank you,
Rhonda

Jeff Boyce said:
Rhonda

I'm having trouble visualizing your underlying data structure. You
described combo boxes, reports, ..., but I don't see where the original
table data is coming from, nor do I see the query that you are using to
"feed" the report(s).

If you put that same IIF() function into a query against your underlying
table(s), you might be able to use that without issue in your report
definition.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I've recently added a combo box which pulls information from our client
list
in order to populate the Address, City, State, Zip, Phone, Fax in the
new
record. Works great.
In the report, all of the information prints. Life is Good.

In an effort to save space (and paper) on several reports I created a
"SortNAME" field

SortNAME: IIf(IsNull([ACRONYM]),[ClientName],[ACRONYM])

so the acronym prints instead of the long name.

My problem.....In those records where the drop down, combo box was used
to
populate the client name, the client name does not print when I request
the
"SortNAME"

If I insert the field "ClientName" on the report, it prints.
If I use the field "SortNAME" on the report, it's blank (only in the
records that were populated by the combo box)

Any ideas???

Rhonda
Click to expand...
Click to expand...
Click to expand...
 
R

R Marko

I have new information that may shine some light on this problem.

I’ve been testing the report and have noticed that….
a) In the form, when I create a new record and type in a [client name]
and[acronym], the [SortName] on the Report works perfectly


b) In the form, when I create a new record and insert a [clientname] and
leave the [acronym] blank, the [SortName] on the Report works perfectly


c) In the form, when the client is a repeat client and the cmb box
populates the fields including [clientname] and [acronym], the [SortName] on
the Report works.


d) In the form, when I choose an existing client in the cmb box to populate
the fields in the form where there is a [clientname] but the [acronym] was
BLANK, the [SortName] on the Report DOES NOT WORK. The space is blank.

The code in the query that populates the report is:

SortNAME: IIf(IsNull([ACRONYM]),[ClientNAME],[ACRONYM])


Please help me, I'm at wits end!

Rhonda


R Marko said:
I'm sorry, I just realized what you're saying.
I didn't explain myself well before. The report does get it's information
from a query. It's in the query that I created the [SortName] field.

Rhonda

Jeff Boyce said:
Rhonda

Please re-read my response. Take your IIF() statement out of the report and
put it in the query that returns the rows of data that feed the report.

Regards

Jeff Boyce
Microsoft Office/Access MVP


R Marko said:
It's rather a simple procedure. The original table, [tblClient] houses
all
of the information.

[frmClient] is how we enter the information. Simple, Simple.

On occasion, clients will return with other projects. To make the data
entry easy, I replaced the field [ClientName] with a combo box which gets
the
information from [tblClient] and populates the address, city, state, etc
fields, enabling us to go right to the "meat and potatoes" of the form and
enter information about the new project.

Private Sub cmbGroupName_AfterUpdate()

Me!ACRONYM = Me![cmbGroupName].Column(2)
Me!SALUTATION = Me![cmbGroupName].Column(3)
Me!FIRSTNAME = Me![cmbGroupName].Column(4)
Me!MIDDLEINITIAL = Me![cmbGroupName].Column(5)
Me!CONTACT = Me![cmbGroupName].Column(6)
Me!NICKNAME = Me![cmbGroupName].Column(7)
Me!TITLE = Me![cmbGroupName].Column(8)
Me!ADDRESS1 = Me![cmbGroupName].Column(9)
Me!ADDRESS2 = Me![cmbGroupName].Column(10)
Me!CITY = Me![cmbGroupName].Column(11)
Me!STATE = Me![cmbGroupName].Column(12)
Me!ZIP = Me![cmbGroupName].Column(13)
Me!COUNTRY = Me![cmbGroupName].Column(14)
Me!Phone = Me![cmbGroupName].Column(15)
Me!EXT = Me![cmbGroupName].Column(16)
Me!FAX = Me![cmbGroupName].Column(17)
Me!HOMEPHONE = Me![cmbGroupName].Column(18)
Me!EMAIL = Me![cmbGroupName].Column(19)

End Sub


I created a simple report that lists all of the clients. To the right of
the name, other information, taken from [tblClient] is listed.

Some of our clients are associations with long names, so we use acronyms.
On the report, where I want to show the clients name, I created a field,
[SortName] and entered...

SortNAME: IIf(IsNull([ACRONYM]),[ClientName],[ACRONYM])


When I replaced [SortName] with [ClientName] it works fine.
What is wrong with my "If" statement?

Hope this explains it.
Thank you,
Rhonda

:

Rhonda

I'm having trouble visualizing your underlying data structure. You
described combo boxes, reports, ..., but I don't see where the original
table data is coming from, nor do I see the query that you are using to
"feed" the report(s).

If you put that same IIF() function into a query against your underlying
table(s), you might be able to use that without issue in your report
definition.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I've recently added a combo box which pulls information from our client
list
in order to populate the Address, City, State, Zip, Phone, Fax in the
new
record. Works great.
In the report, all of the information prints. Life is Good.

In an effort to save space (and paper) on several reports I created a
"SortNAME" field

SortNAME: IIf(IsNull([ACRONYM]),[ClientName],[ACRONYM])

so the acronym prints instead of the long name.

My problem.....In those records where the drop down, combo box was used
to
populate the client name, the client name does not print when I request
the
"SortNAME"

If I insert the field "ClientName" on the report, it prints.
If I use the field "SortNAME" on the report, it's blank (only in the
records that were populated by the combo box)

Any ideas???

Rhonda
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

One step at a time.

You used [client name], [clientname], and [clientNAME] in your description.
Spelling counts (Access is fairly stupid this way).

Please post the SQL of your query.

Does your query return the values you need/expect? Forget about the report
for a moment, just get the query working correctly.

Regards

Jeff Boyce
Microsoft Office/Access MVP


R Marko said:
I have new information that may shine some light on this problem.

I've been testing the report and have noticed that..
a) In the form, when I create a new record and type in a [client name]
and[acronym], the [SortName] on the Report works perfectly


b) In the form, when I create a new record and insert a [clientname] and
leave the [acronym] blank, the [SortName] on the Report works perfectly


c) In the form, when the client is a repeat client and the cmb box
populates the fields including [clientname] and [acronym], the [SortName]
on
the Report works.


d) In the form, when I choose an existing client in the cmb box to
populate
the fields in the form where there is a [clientname] but the [acronym] was
BLANK, the [SortName] on the Report DOES NOT WORK. The space is blank.

The code in the query that populates the report is:

SortNAME: IIf(IsNull([ACRONYM]),[ClientNAME],[ACRONYM])


Please help me, I'm at wits end!

Rhonda


R Marko said:
I'm sorry, I just realized what you're saying.
I didn't explain myself well before. The report does get it's
information
from a query. It's in the query that I created the [SortName] field.

Rhonda

Jeff Boyce said:
Rhonda

Please re-read my response. Take your IIF() statement out of the
report and
put it in the query that returns the rows of data that feed the report.

Regards

Jeff Boyce
Microsoft Office/Access MVP


It's rather a simple procedure. The original table, [tblClient]
houses
all
of the information.

[frmClient] is how we enter the information. Simple, Simple.

On occasion, clients will return with other projects. To make the
data
entry easy, I replaced the field [ClientName] with a combo box which
gets
the
information from [tblClient] and populates the address, city, state,
etc
fields, enabling us to go right to the "meat and potatoes" of the
form and
enter information about the new project.

Private Sub cmbGroupName_AfterUpdate()

Me!ACRONYM = Me![cmbGroupName].Column(2)
Me!SALUTATION = Me![cmbGroupName].Column(3)
Me!FIRSTNAME = Me![cmbGroupName].Column(4)
Me!MIDDLEINITIAL = Me![cmbGroupName].Column(5)
Me!CONTACT = Me![cmbGroupName].Column(6)
Me!NICKNAME = Me![cmbGroupName].Column(7)
Me!TITLE = Me![cmbGroupName].Column(8)
Me!ADDRESS1 = Me![cmbGroupName].Column(9)
Me!ADDRESS2 = Me![cmbGroupName].Column(10)
Me!CITY = Me![cmbGroupName].Column(11)
Me!STATE = Me![cmbGroupName].Column(12)
Me!ZIP = Me![cmbGroupName].Column(13)
Me!COUNTRY = Me![cmbGroupName].Column(14)
Me!Phone = Me![cmbGroupName].Column(15)
Me!EXT = Me![cmbGroupName].Column(16)
Me!FAX = Me![cmbGroupName].Column(17)
Me!HOMEPHONE = Me![cmbGroupName].Column(18)
Me!EMAIL = Me![cmbGroupName].Column(19)

End Sub


I created a simple report that lists all of the clients. To the
right of
the name, other information, taken from [tblClient] is listed.

Some of our clients are associations with long names, so we use
acronyms.
On the report, where I want to show the clients name, I created a
field,
[SortName] and entered...

SortNAME: IIf(IsNull([ACRONYM]),[ClientName],[ACRONYM])


When I replaced [SortName] with [ClientName] it works fine.
What is wrong with my "If" statement?

Hope this explains it.
Thank you,
Rhonda

:

Rhonda

I'm having trouble visualizing your underlying data structure. You
described combo boxes, reports, ..., but I don't see where the
original
table data is coming from, nor do I see the query that you are using
to
"feed" the report(s).

If you put that same IIF() function into a query against your
underlying
table(s), you might be able to use that without issue in your report
definition.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I've recently added a combo box which pulls information from our
client
list
in order to populate the Address, City, State, Zip, Phone, Fax in
the
new
record. Works great.
In the report, all of the information prints. Life is Good.

In an effort to save space (and paper) on several reports I
created a
"SortNAME" field

SortNAME: IIf(IsNull([ACRONYM]),[ClientName],[ACRONYM])

so the acronym prints instead of the long name.

My problem.....In those records where the drop down, combo box was
used
to
populate the client name, the client name does not print when I
request
the
"SortNAME"

If I insert the field "ClientName" on the report, it prints.
If I use the field "SortNAME" on the report, it's blank (only in
the
records that were populated by the combo box)

Any ideas???

Rhonda
Click to expand...
Click to expand...
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 - Sorting and Grouping 2
Publisher is filling blank lines with data 2
Auto Populated fields from a form 2
Report formatting hangs 3
Errors in Report total fields 19
How do I suppress blank lines in reports 1
Form filter with multi-column combo box does not work in report 0
Form filter with multi-column combo box in report 0

Top