when export get an Apostrophe " ' "in front of some fields

[Bullsandbears123]

When I use the


DoCmd.TransferSpreadsheet acExport, , "myquery", strpath, True, ""



I get an extra apostrophe in front of some fields. I'm not sure why. The particular fields are text fields, does that matter? How can I get rid of the extra apostrophe?

I get...

'mystring
'mystring1
'mystring2
'mystring3

instead of

mystring
mystring1
mystring2
mystring3


What could cause such a weird thing?
THANK YOU!!

 

[Bullsandbears123]

extra info....

I think it might have something to do with the fact that the field has a lookup using a combo box list from another table. But when I view the values in the table they look fine.

THANKS

 

[PHV]

In excel, the single quote in front is saying the following should be considered as text even if made only with digits.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

To answer the second part of the question, you can stip of the leading single quote with code...

=iif(left(YourString,1)=chr(39),right(YourString,len(YourString)-1),YourString)

Richard

 

[Bullsandbears123]

Thank you it's very informative. But is there a way to remove the apostrophe during export? or maybe run a command through VBA in access to remove all the apostrophes on the sheet? Thanks

 

[snayjay]

Just bumping this because it was never answered and I'm getting the same thing...

 

[willir]

Okay, PHV provided an exclaimation on why.

To update the field to remove the single quote (Ascii code 039)...
UPDATE YourTable SET YourTable.YourTextField = Right([YourTextField],Len([YourTextField])-1)
WHERE Left([YourTextField],1)=Chr$(39);


It works this way...
WHERE clause
WHERE Left([YourTextField],1)=Chr$(39)
Test for the first Character being ' or Ascii code 039

Right([YourTextField],Len([YourTextField])-1)
Update the field by excluding all but the first character. To do this, include the length of the text string, less one.

For this UPDATE query, substitute the name of your table and the field where I indicated YourTable and YourTextField.

Yes, this can be written with VBA code, but an SQL is faster.

Richard

 

[PHV]

Richard, I find the following simpler:
UPDATE YourTable SET YourTextField = Mid([YourTextField], 2)
WHERE [YourTextField] Like [tt]"'*"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

Agreed...
Mid(string,2) much more effecient than using Right
Using the Like makes more sense than using Left

I figure you must be close GMT considering when you start posting, n'est-ce pas. You are incredibly prolific!

 

[PHV]

I figure you must be close GMT
France TimeZone, but I'm used to be insomniac sometimes ...

 

[snayjay]

I'll try this but it's going to be a little bit before I get back, thanks for the input though!

~Snay

-I wish I had half the knowledge of either of you, then I'd be twice as smart as I thought I'd ever get with this stuff. Thanks so much for your service on this forum!