£ symbol converted to œ on access report output to .txt file 1

[AJParkinson]

Can anybody give me any clues as to how to stop my '£' symbol being converted to 'œ' when I output my report to a text file? If I alter the code to view the report on screen, the report displays the £ symbol ok, yet if I let the code run through and generate the text file and open it in notepad I get the œ symbol.
I can type the £ symbol immediately before or after the œ within the generated text file in notepad without problem, so I'm sure it's not a fonts related problem on my pc.

(The text file that is output to the directory must have the .spl extension as a 'watcher' service is running on the server waiting for particularly named files to automatically run them through another program.)

[COLOR=blue][B]
Private Sub cmdPrintDesp_Click()
    Dim stDocName As String
    Dim stDirectory As String
    pr_Desp_Note = Me.order_no.Value
    stDocName = "pr_Desp_Note"
    stDirectory = "\\Server\directory\textfile.spl"
    DoCmd.OutputTo acReport, stDocName, , stDirectory, No
End Sub
[/B][/color]

Many thanks in advance

 

[psemianonymous]

How does the pound symbol appear in your report? Did you use Currency-formatting for a field, or did you manually insert the pound symbol? Because note that standard text files are 8-bit ASCII, whereas many of the newer internationalized (internationalised? I'm American) applications use Unicode for text encoding. So your problem may be that you are typing in the Unicode pound symbol in the report designer, then when it is converted to ASCII loses ... whatever it is that it loses.

As an alternate, try using the dollar symbol, if it's available, or maybe figure out the decimal ASCII value for the pound sign (for the dollar it is 36, or 0x24) and do something like

=Chr(36) & " is the pound symbol"



Of course, I could be way off with all of this.

 

[Quehay]

The VBA Help ALT F11 > VB Language Reference > Misc
shows an ASC 8 bit code for pound:

chr(163)

This should work (I just tested with a text file).

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[AJParkinson]

Thanks to foolio12 & Quehay for replying, gives me something to start from.
Though I think it's a bit more complex than that, I'll try and explain:
The field that the report reads from is just a standard string field held in a SQL7 database, Access pulls this info onto a form, the text can then be amended in this field if the user wishes (changes are written back into SQL7), and printed from there.
The field must be free to take any type of string, so I couldn't restrict it to be a currency field. I suppose I could search the string looking for a £ symbol and if found, substitute it for your code. I'm guessing I would need to run this substitution on the access report as it is generated, would this work?

 

[MakeItSo]

You should take care: this is an encoding problem:
a) Check, if you are using "Pragmatica" font anywhere, it strangely seems to have a different encoding than other TT fonts.
b) If you are working with SQL Server, you can prevent corruption of such chars by using defining the table field as ntext or nvarchar (Unicode support).

Access is Unicode compatible, so there are no problems there. The prob should lie on the SQL Server side.

Hope this helps,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[Quehay]

It's probably late in the day and not helpful at this point, but the currency symbol should be isolated in a separate field from currency amount. This would be better modelling, and it would allow you to prevent non-numerical key entries in amount via client validation code and restrict currency symbols to a lookup list.

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[AJParkinson]

Quehay: as the old saying goes "if I had it my way...".
MakeItSo: good point, checked out the relevant SQL table fields they are char and not ntext or nvarchar - however, see below

I've tried to narrow down my problem:
I've cut out the SQL and started later in the process.
Create a simple table in access with one column (data type text) and typed "test£" straight into the first and only row. Created a simple report with one field that reads this table data. Created simple form with one button that prints this report as before to the specified folder. When I open this file in notepad the text reads "testœ" and not "test£". Yet if I add code to view the access report on screen just prior to printing, the field still reads "test£". The problem must be in the way the report is exported to a text file. (Output To format selected is MS-DOS Text).
Any more ideas you guys?

 

[AJParkinson]

One thing I forgot to add - not using the Pragmatica font anywhere, just plain and simple Times New Roman

 

[Quehay]

Hmmm...it's just ASCII 1-250, rather than unicode, so it should be in MS-DOS, no?

I'll skip the "why export to..." question since there must be a good reason for that.

You could export query results directly to a text file using recordsets, but there's a good bit of coding and trial and error to get decent layout.

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[Quehay]

I saved a Northwind query thus:

SELECT Chr(163) AS Pound, [Product List].UnitPrice
FROM Products AS [Product List];


and then exported to textfile with desired results.

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[MakeItSo]

Fascinating! I have just typed a £ in notepad - it's fine.
Then I typed it in Word and saved the doc as "MS-DOS Text" converts to œ...
Saving as "Text only" leaves the £ undamaged.

Obviously the format "MS-DOS text" has a system-internal coding error.

Can you switch to a different output format?

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[AJParkinson]

MakeItSo: Unfortunately I can't use a different output other than plain text as this file is read by another program. The exported rtf, html xls versions of the report don't work because the /PAR or <HTML> etc coding tags are picked up and output.
Quehay: Tried and successfully exported a '£' symbol from a simple query to a text file - good stuff, but wouldn't know where to start with the coding required for the layout of this output.

The program which uses the .txt file is called DBForms.

I could always script a search and replace within the .txt file I suppose...

 

[MakeItSo]

You could use the transfer text function:
DoCmd.TransferText acExportDelim, &quot;&quot;, &quot;Report Query&quot;, &quot;C:\Report.txt&quot;, True, &quot;&quot;, 1252

The last number defines the codepage.

If you want to format the output report-like I assume you must write your own output procedur, for example

Dim rs as recordset, a as integer,fld
a=freefile
Open &quot;C:\Report.txt&quot; for output as a
set rs=currentdb.openrecordset(&quot;query for report&quot
for each fld in rs.fields
Print #a, &quot;Report Reportname, &quot; & date()
Print #a, fld.name & &quot;: &quot; & rs!fields(fld)
next
rs.close
close a

Hope this helps,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[AJParkinson]

Sorry for the delay in replying you guys, temporarily been pulled onto another project, will try what you've suggested and come back in a while.

Andrew