Problems manipulating text data - Access XP 2002

[Xeseus]

I am trying to combine the city, state and zip in a field for my report and I continually find Access is temperamental. Sometimes it works other times not, but more often than not the latter. I get "error" in the field when looking at it from the view window. Although this doesn't give you the whole picture, here is the expression I use in the Control Source property box: =Trim([AgencyCity] & ", " & [AgencyStateOrProvince] & " " & [AgencyPostalCode])
How can I get this to work correctly and consistently?

 

[TheAceMan1]

How are ya Xeseus . . .

Remove the [blue]Control Source[/blue] of the textbox then in the [blue]On Format[/blue] event of the [blue]Detail Section[/blue] try this:

[blue]   Me![purple][b][i]TextboxName[/i][/b][/purple] = Me!AgencyCity & ", " & Me!AgencyStateOrProvince & " " & Me!AgencyPostalCode[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[SmallTime]

I've come across this too, the concatenated text disappears, leaving a blank field when a pop-up opens over it. I found that removing the Trim solved the issue. I just have to put up with an untidy looking field.

AceMan1 hope you feeling better.

Regards

 

[TheAceMan1]

SmallTime . . .

Thank You! . . . All is well!

Have you tride my code snippet! . . . you can use [blue]Trim[/blue] as well if it suites your purposes! Just be sure to use the [blue]On Format[/blue] event of the [blue]Detail[/blue] section . . .

See Ya! . . . . . .

 

[Xeseus]

I'm not following what you mean by the On Format event of the Detail section. How do I get to this?

 

[TheAceMan1]

Xeseus . . .
[ol][li]Open the report in [blue]design view.[/blue][/li]
[li]Clear the [blue]Record Source[/blue] property of the textbox.[/li]
[li][blue]Double-Click[/blue] the box just to the left of the ruler (brings up the [blue]properties window[/blue]). ([/li]
[li]Select the [blue]Events Tab[/blue].[/li]
[li]Click the [blue]Detail Section bar[/blue].[/li]
[li]Put the cursor on the [blue]On Format[/blue] property line.[/li]
[li]Click the [blue]three elipses[/blue]

just to the right:
[ol a][li]If the [blue]Choose Builder Dialog[/blue] comes up, select [purple]Code Builder[/purple].[/li][/ol][/li]
[li]You should now see the [blue]Detail Format event[/blue] routine for the detail section . . . looks like:

[blue]Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

End Sub[/blue]

[/li]
[li][blue]Copy/paste[/blue] the following to the event ([blue]you[/blue] substitute [purple]TextboxName[/purple]):

[blue]   Me![purple][b][i]TextboxName[/i][/b][/purple] = Trim(Me!AgencyCity) & ", " & _
                    Trim(Me!AgencyStateOrProvince) & " " & _
                    Trim(Me!AgencyPostalCode)[/blue]

The event [blue]should now look like[/blue]:

[blue]Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   Me![purple][b][i]TextboxName[/i][/b][/purple] = Trim(Me!AgencyCity) & ", " & _
                 Trim(Me!AgencyStateOrProvince) & " " & _
                 Trim(Me!AgencyPostalCode)
End Sub[/blue]

[/li]
[li]Alt + F4[/li]
[li][blue]Save & Close[/blue] the report.[/li]
[li]Open the report like you normally would . . .[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[Xeseus]

Aceman1: I do not have a Detail Section bar on my Events tab. Also, I only have On Activate, On Close, On Deactivate, On Error, On NoData, On Open, and On Page - but no On Format event when I go into the Code Builder. I am using Access XP 2002, is that what you are using?

Smalltime: Removing Trim didn't remedy things either.

 

[TheAceMan1]

Xeseus . . .

The Detail Section seperator!

See Ya! . . . . . .

 

[Xeseus]

TheAceMan1: Okay, now I'm cued in: the "Detail" separator bar below the "Page Header" separator bar.

I got this to work but only once I put the City and State fields back on the report from the Field list. To keep the report looking as intended I had to hide them, which is very easy. It is still a little disconcerting that I cannot manipulate and concatenate data in a textfield in the simple way and straight forward way that Access is supposed to be able to handle. I have a lot of fields like this, other addresses, and name fields that need to be concatenated in a similar fashion - not to mention several more reports. (I just realized that I had erroneously indicated that this was a form but it is actually a report, which might make my need for this a little more clear.)

I have been able to make the text field concatenation work in the past but it is only after a series of attempts. Once an attempt fails* there is nothing I can do to fix it - I have to delete the field and re-introduce it to the report from the Field List. Eventually, with enough attempts at doing the same thing I can get the concatenation to work and all the concatenated fields appear on the report.

*("#Error" is what all that is seen in the field when the report is previewed.)

I really appreciate your help and patience though. If you have any more ideas please let me know. Thank you very much!
-Xeseus

 

[TheAceMan1]

Roger That Xeseus . . .

As another option if you base the [blue]RecordSource[/blue] of the report on a [blue]query[/blue] you can add a [blue]custom field[/blue] to the query . . .

[blue]CSZ:Trim([AgencyCity] & ", " & [AgencyStateOrProvince] & " " & [AgencyPostalCode])[/blue]

. . . and use that instead!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

BTW: [blue]Welcome to Tek-Tips![/blue] To get great answers and know whats expected of you in the forums be sure to have a look at FAQ219-2884

See Ya! . . . . . .

 

[Xeseus]

AceMan1: That's another very cool work around, thank you! I think I have identified my problem, though. I have been taking a text box named "PostalCode" and modifying its source to be =Trim([AgencyCity] & ", " & [AgencyStateOrProvince] & " " & [AgencyPostalCode]). I am having better luck creating a textbox from scratch and putting that code in for the source.