Converting Infinity Symbol to 99999 in Query

[Terry Lawson]

I'm struggling to convert '∞' to '99999' in vba.

I've written a query which works fine, but as soon as I copy and paste the SQL into vba the '∞' gets changed to '�‡', and this fails.


Suspect it might be something to do with character sets:

The code lifted from the query is

UPDATE Sheet1 SET Sheet1.F5 = '99999' WHERE (((Sheet1.F5)='∞'));

When copied and pasted into the code the result is

SQLText = "UPDATE Sheet1 SET Sheet1.F5 = '99999' WHERE (((Sheet1.F5)='�‡'))"

Any pointers as to how to get this right will be greatly appreciated.

Terry Lawson

[Homegrownandy]

Why does it need to be in VBA, Cant you use an update query?

[June7]

Right, code can run the saved query object.

How did you get the infinity symbol into query? I was able to copy/paste from your post but not from character map Symbol set.

[CJ_London]

not tested but try (((Sheet1.F5)=chr(236))


you may need to use the left function to ensure you only use 1 character from F5

got it from here
http://www.anysitesupport.com/insert...nto-documents/

[June7]

I tested in VBA immediate window, textbox, query - none display the infinity symbol.

Did queries:

SELECT Chr(236)="∞" AS Test, "∞" AS C FROM HX32;
SELECT Chr(236)=Field1 AS Test, Field1 FROM HX32;
SELECT Chr(236)=Left(Field1,1) AS Test, Field1 FROM HX32;

The infinity symbol does display but the returns in Test are 0 - False.

However, OP's UPDATE does work.

[Terry Lawson]

Thanks all for suggestions

I got the '∞' into the update query by copy and paste from the field in the table "Sheet1" that contains the symbol.

As I read this thread all attempts to use vba have failed, have I missed something?

I've solved the issue by importing the field which generates '∞', a div/0 where 0 is is that field

But if anyone would like to look further I would appreciate it, it's become an itch I must scratch!