Carriage returns 2

[SpankYou]

Hey,

Ok where to start, I'm using Microsoft Access to connect to Access Accounts Horizons. From this I have inserted some tables into Microsoft Access using ODBC. This is fairly irrelevant, but will help explain my problem. Basically the accounts package has an orders section with a description field, and each time a carriage return is entered into this field a square is placed in the description field within Microsoft Access. When I produce a report this square is still visible, and when convert to the word format it implements these carriage returns, and the report is all over the place.
Is there anyway of getting rid of the carriage returns before I send it to the report. I've tried to select this square (carriage return) and use the replace function, but the square cannot be selected through copy and paste.

Any ideas that may help would be great.

Thanks for any help

Sam

 

[SpankYou]

Oh by the way no data can be edited from the Access Accounts Horizons from within Microsoft Access, so I'm not even sure if there is anything I can do, Anyway thanks in advance again.

Sam

 

[DrSimon]

I've often had this sort of problem. There is not always an easy way round it. The first point is - are you sure that carriage return is inserted? There are ways to find out what the character is, but the box could well be Carriage Return (chr(13)) and/or Form Feed (chr(10))

I suggest you use the replace function like this where [Text] is the field containing the 'box'.
Replace([Text],Chr(13) & Chr(10),"&quot

Play with different permutations of Chr(13) and Chr(10) until it works. If it doesn't, let me know and I'll send you a function to find out what the character is or are.
Simon Rouse

 

[SpankYou]

I've tried the replace function but it comes up with an error "expected expression". So I tried this...

Dim strDescription As String
Dim StrReplaceDesc As String
strDescription = OD_DETAIL
StrReplaceDesc = Replace(strDescription, Chr(13), "&quot
OD_DETAIL = StrReplaceDesc

This come up with an error "you cant assign a value to tis object". I was wondering whether this would be because the text was bound to a control source. Would it be possible to create another unbound textbox and place each replaced text into the new text box or is there a better way?

thanks for the help

Sam

 

[SpankYou]

Ok I've created this unbound textbox, and the code is...

Dim strDescription As String
Dim StrReplaceDesc As String
strDescription = OD_DETAIL
StrReplaceDesc = Replace(strDescription, Chr(13), "&quot
TxtDescription = StrReplaceDesc

but instead of looking each time at the data in the recordset, it places the first value that is in the the OD_DETAIL, and keeps it the same. I assume it needs a loop any ideas?

Cheers

Sam

 

[DrSimon]

Re unbound text box - Yes that's sort of what you need to do. Delete the original and replace it with an unbound text box. But you don't need to write the code with a full VB function, just put =Replace([OD_DETAIL],Chr(13),"&quot as the control source for the new unbound object.
Simon Rouse

 

[SpankYou]

Hey Simon,

Ok i've adpated it as you said but the same problem as before has come about e.g..

OD_DETAIL UNBOUND
NAME1 NAME1
NAME2 NAME1
NAME3 NAME1
NAME4 NAME1
NAME5 NAME1
NAME6 NAME1

Any Ideas?

Cheers

Sam

 

[SpankYou]

Ok back again,

I've created a loop that puts each recordset value in the new textbox, but I have placed this code in the activate, and when the report loads up, my new unbound text box just holds the last OD_Detail recordset, so it obviously runs the code before it loads the report, now i'm stuck, ne ideas? thanks

Sam

 

[DrSimon]

Sam
There's something odd here. Are you working from a single table are are there joins involved? I don't think this has anything to do with your actual problem, using Replace seems to have shonw something up.
I assume that you have a link to the original table(s). Could you try importing the table(s) and see if that makes any difference.
Simon

 

[SpankYou]

Simon

Im working from two tables with a single join, which are links to the original tables (held in the accounts package).I'll give the import thing a go and get back to you.

Cheers


Sam

 

[SpankYou]

I've imported the tables and tested it, and the same problem occurs, I'm confused, hows about you?

Sam

 

[DrSimon]

Agreed. I can't think of much more apart from starting small and building up from that.
I suggest you first create a simple report using only the table with the rogue data and see if the replace makes any difference. Try on both linked and imported but I now suspect that isn't the problem. If all seems OK, then build up the joins round that and see if/when problems occur. The simplest way to do that is to base the report on a query for one table and then add the other tables.
Simon

 

[SpankYou]

Ok thanks for your help, I'll see what I can do n If I ever get it sorted i'll post you, thanks again.

Sam

 

[TonyJollans]

Hi Sam,

Just arrived hotfoot from the VBA Forum

I'm very confused by what seems to have gone on so far, so I'll try and start from the top. First a statement of what I think we have:

1. You are producing an Access Report from some sort of Record Source - it shouldn't matter whether the data comes via ODBC or whether there are Joins involved.

2. One of your controls contains a Chr(13) which you want to get rid of.

3. DrSimon's suggestion to use Replace seems correct to me, but you are having problems with it.

Now, when I tried to do this I found a couple of interesting things which I can only report (no pun intended) and tell you how I sorted it.

1. The Replace function, which is new in VBA in Access 2K, doesn't appear to be available as a BuiltIn Function in the Expression Builder - this may just be my setup, but it may explain some of your problems.

2. On the Report, create a textbox - DO NOT CALL IT OD_DETAIL (or whatever your column is named) and do not have another control on the report named OD_DETAIL either (unless it is simply bound to the OD_DETAIL column).

3. In the Control Source for this textbox, put

=

My

Replace([OD_DETAIL],Chr(13),"")

4. In the Report's Code Module add the following Function:

Function MyReplace(a, b, c)
    MyReplace = Replace(a, b, c)
End Function

What this does is effectively apply formatting to a bound control, rather than having an unbound control which will have the same value on every record (as you have experienced), and don't try updating the table in code (it's not what you want to do at all).

Hope it helps! If not, come back and I'll try again.

Enjoy,
Tony

 

[SpankYou]

Thank You soooo much Tony & Simon it was beginning to drive me crazzy, and now it all works, again thanks and have a star on me.

Cheers

Sam