Text file specification Field Separator Matches

[CD Tom]

I have a customer in the Netherlands that is getting the following error "Text file specification Field separator matches decimal separator or text delimiter" This happens when I try and run the DoCmd.TransferText acExportDelim command. I know this has something to do with the settings on his computer regarding the separators. Is there any way that I can switch settings in my program to accommodate this. I'm probably not very clear on this but I don't know exactly what settings they use. Any help would be appreciated.

[NeoPa]

You're probably talking about the fact that they use '.' and ',' where we use ', and '.'. 500,000.00 would show as 500.000,00 over there, and in various countries in mainland Europe.

I don't know for sure, but you can try escaping the character in your use. You haven't shared that with us, I expect it's in your spec, but if you can, try "\," instead of ",".

[CD Tom]

Ok, I'm sure you are correct in the way Europe uses the ',' and the '.' now here's where I show my ignorance I don't have a spec setup in the transfertext command. I have never set one up so I'm at a loss as to where to go. If you could point me the direction where I could find some information I would appreciate it.
Thanks

[NeoPa]

You set it up most easily by doing an import manually and saving the spec within that process.

In 2003 it's under File | Get External Data | Import...

[CD Tom]

I was just looking at how to do this and I'm finding that Access 2007 doesn't give me the options to change any of the options. I see how to do it in Access 2003 but is there some way to do it in 2007? Sorry I should have mention 2007 in my earlier questions.
Again thanks.

[NeoPa]

I expect so Tom, but I don't use 2007 (or even 2010 yet) so I can't direct you.

[ADezii]

This is how you Save a Specification in Access 2010, the process cannot be that far off from 2007.

1. Click on External Data Tab.
2. Under Import & Link, select Text File.
3. Browse to your Text File, then OK.
4. In the Import Text Wizard Dialog, select the Delimited Option.
5. In this same Dialog, click on Advanced which Opens the Import Specification Dialog. Set your Import Options, then Click on Save As...
6. Enter a Name for your Specification, then OK.
7. Click on OK to Close the Import Specification Dialog.
8. Click Next on the Import Text Wizard Dialog.
9. Set other Options appropriately.
10. When prompted to Save Import Steps, check Save import steps - this will enable you to bypass the Wizard next time.
11. Click Save Import.

[CD Tom]

I've tried the Specifications and was able to get into and tried to change the option to the "\," I put this in the decimal area because when I tried and put it in the text separator I would get an error message. However that didn't fix the problem, I've changed my computer settings to Netherlands(dutch) to test this and I still get the error "Text file specification Field separator matches decimal separator or text delimiter" here's the exact statement :

DoCmd.TransferText acExportDelim, netherlandsspec, Vtable, apppathx, True

Thanks again

[NeoPa]

I suspect you actually changed it to "Dutch (Netherlands)", after searching around aimlessly for the other :-D

Fortunately (or more likely unfortunately in this situation), when I ran mine it continued to behave exactly as it did using my normal settings - English (United Kingdom).

[CD Tom]

Thanks again for all your help. I think I'll see if I can change the settings in the language setup on his computer. He is using this computer only to run this application and maybe changing to English(united Kingdom) on his will work.

[NeoPa]

That's a possible approach, and can certainly be used if all else fails, but my checking leads me to believe the fault lies in the spec itself. Unfortunately I don't have the database to check exactly what's what (nor could I even with a 2007 db), so it's your choice how far down that path you go before falling back to the Regional Settings solution.

[CD Tom]

It seems like there are not a lot of options in the specs settings. I can only see the text delimiter and the decimal. If the text delimiter is a "," and the decimal is a "." if I change the decimal to a "," then the text delimiter is the same. My application is also in Australia and I haven't heard of any problems they are having. I might contact one of them and see how their computers are set.

[NeoPa]

I must admit that I can see no obvious scope here either. I just know I'd explore it further if it were me working directly on the project. That's my own personal approach though, of course. There's no reason why you should feel the same way. We all work in our own ways, and I find that way is good for me.

[CD Tom]

I'll probably keep looking at it but will not spend lots of time as this is only one case where I'm having this problem. Thanks for all the advise and help.

[NeoPa]

No worries :-)

Glad to be able to.