Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I've encountered this before and here it is again... on a query with values like

    Longitude Latitude Name POI
    -95.499652 29.678361 Facility Name
    -97.333824 32.736567 Facility Name

    Export to .txt gives me:
    -95.27,32.34,"Facility Name"
    -98.11,32.78,"Facility Name"

    why, oh why is Access truncating the decimal values? If I run the query to output to table, the column is formatted to Double, Decimal places "Auto". Changing the format to Single doesn't help, neither does setting places to, say, 14. Access just doesn't care! to heck with all those "extra" values...

    Also, I require the comma-separate format with " qualifier on text.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Microsoft says that this behavior is "by design". The suggested workaround is to create a query that converts the values to text strings with the required number of decimal places using the Format function.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='steve_skelton13' post='763630' date='05-Mar-2009 17:00'][/quote]

    Another posibility is to change the number of decimals in your regional settings to the number you want Access to export.
    [attachment=82657:nrdecimal.jpg]
    Attached Images Attached Images
    Francois

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='763644' date='05-Mar-2009 17:33']Microsoft says that this behavior is "by design". The suggested workaround is to create a query that converts the values to text strings with the required number of decimal places using the Format function. [/quote]


    this seems a bit retarded of MS. any idea why this would ever be meaningful?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Fracois's suggestion does work altho I also find it a bit much to have to adjust the entire computer's regional settings for something that shouldn't (IMHO) have to happen in the first place. A database that can't export complete data...rather sorry!

    Thanks both for the comments and suggestions!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='steve_skelton13' post='763693' date='05-Mar-2009 19:55']this seems a bit retarded of MS. any idea why this would ever be meaningful?[/quote]
    I daren't presume to read the minds of Microsoft programmers!

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The text export in Access has always been a bit lame - the degree depends on the version. For example, 2000 will export a date field with no (midnight) time very nicely, but 2007 insists on sticking a 12:00 AM on it regardless of how you format the field. On the other hand, if you export a bit field, 2007 works fine, but 2000 insists on putting out Yes or No. Maybe one day they will realize how useful this facility is and fix it....
    Wendell

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I guess these are just some of the quirks one must be aware of. FWIW, SQL Server (and Oracle, from what I've seen) commonly combine date+time and that can also be annoying. Perhaps that's the industry standard on "real" rdbms's. on one project, I have this issue and applying CDate(date time field) will trim the time portion.

    I am really puzzled by Access's blithe dismissal of "extra" decimal values. The display in the Query shows it. What does MS have to hide????

    Anyhow, for my current project, the work around is a little ugly:

    1. Set the longitude/latitude decimal value to string
    2. Export to CSV text, set text qualifier to None (not ")
    3. Open in Excel AS a CSV file

    Since I must have
    4. <longitude>,<latitidue>,"<name>" I have to insert the quotes in the last column. Do this in Excel somehow.

    You'd think that would do it, but, annoyingly, open the CSV file in a texteditor (I use TextPad), those single quotes added in in step 4 are magically transformed to *triple* quotes. Find/Replace....

    Oy vey! All this because MS can't imagine why you'd need more than 2 decimal precision.

    I could go the route of setting the computer system decimal settings to something more than '2' but I didn't. Partly because I want to make sure the long/lat values are precisely as emitted from the geocoding parser. Padded zeroes might actually be a better solution or make no difference - haven't tested that yet. Also I am reluctant to change a global value. who knows what that might impact?

    On balance, I have to say truncating completely needed values on export is completely asinine.

    Perhaps if I need to ramp this up to a user solution some VBA I/O will do the trick. Perhaps so - not sure yet if that will be a place to go.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •