Access Report Problem

[lm0304]

Good morning,

We are having a problem with a report. One of the main forms in the database has an Customer Name field and Location field (which holds the city, state).

In the report, within the detail section, we have a text box defined as =[Account]+", "+[Location].

The problem is that when the user does NOT put in a location, neither an Account or Location prints. It should printing as "Customer, Location".

Can anyone shed some light on this? It is probably something simple that I am missing.

Thanks so much for your help.

 

[Gazer44]

Try the NZ function to ignore blanks.

=Nz([Account]+", "+[Location])

see if that works !

 

[SgtJarrow]

The problem lies in the +.....Access defines this mathematical addition a little funny when dealing with text. If any text string is Null and you use the +, the ENTIRE equation will return Null, hence your blank. A solution would be to change the +s to &s. But then you run into the problem of having one of the fields blank. If Account is blank, you will end up with ,Location and Account, if Location is left blank. You could perform some checking first and then display as necessary by using the OnEnter property for the text box. Place the following code in the OnEnter property for the text box....

'**************Start Code*************
If IsNull(Me![Account]) Then
MsgBox("Account Information Missing!&quot
Exit Sub
End If
If IsNull(Me![Location]) Then
MsgBox("Location Data Missing!&quot
Exit SUb
End If
Me![Name of TextBox = Me![Account] & ", " & Me![Location]
'***************End Code**************** Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@ssmb.com