Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2004
    Location
    West Of Atlanta
    Posts
    12

    Unanswered: Adding a . in at the proper place in a number field.

    I am importing some data from a flat file.

    Several fields I am importing are dollar amounts.

    The fields come in with NO . in them at all.

    So an ammount of $35.72 might look like this.

    000000003572

    I have tried a few things to get it to display properly.
    I have tried some things to get it to import properly.

    The best I have been able to do is get it to display (using my example) is...

    3572 (three thousand seventy two dollars)

    I do not see where I am to make this correction.

    What is the PROPER way to accomplish this?


    TIA

    Paul

    (BTW great forum here)

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    As long as it is always 2 decimal places, in other words $35.70 shows as 3570 and not 357, you can import the file the way you are doing and then create an update query and have your [Value] field updated to '[Value]*.01'.

    TD

  3. #3
    Join Date
    Nov 2004
    Location
    West Of Atlanta
    Posts
    12
    Have not done an UPDATE query yet, however I understand what I need to do now. Thank You.

    Does an UPDATE QUERY actually UPDATE the original data? Or is it just in that query?

    Paul

  4. #4
    Join Date
    Nov 2004
    Location
    West Of Atlanta
    Posts
    12
    My field was a text field and did not update well....

    Does it need to be an interger, curency or what. Still Playing with it right now.


    This is fun!

    Paul

  5. #5
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    It actually updates the data in the table. If you imported your data into a table we'll call 'ImportedTable' and the data that you expected to have 2 decimal places went into a field we will call 'Value', you would just create a query in design view, select your table called 'ImportedTable', change the query type to Update Query (the drop down button on the tool bar to the left of the exclamation), put your field called 'Value' in the first column, in the 'Update To' box put "[Value]*.01" without the quotes, and then click the Run button on the toolbar (Exclamation). This will update the Value field in your table. Only do this once or it will take it times .01 again.

    One note: The field may have imported as an integer (probably Double), which will be a problem when you want to have decimal places. Prior to running the update query, go into Table Design for 'ImportedTable' and go to your 'Value' field and change your Field Size to Double if it isn't already set to that.

    TD

  6. #6
    Join Date
    Nov 2004
    Location
    West Of Atlanta
    Posts
    12
    To let you know how "GREEN" I am at this.

    I just figured out that when you gave me the example of [value], I was to change that to the field name.


    I have it set a curency now and it seems to be allowing me to update it ok.

    I will run it through the paces a bit to see if all is well.

    I hope my questons are not too simple for this forum. It seems that everyone here has a level of understanding so much greater than I do on this subject.

    Thanks,

    Paul

  7. #7
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520

    curiosity

    Just for my own curiosity could not the RIGHT statement in Access be used to accomplish this.
    Darasen

  8. #8
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Darasen,
    That would be another way to do it, but I don't think it would be the best approach. If you used a RIGHT() function, you would need to treat the field as a text field. You would also have to know that it is a fixed number of decimal places, which you also need to know for this calculation. Since you know the fixed number of decimal places and it is a numerical field, the mathematical calculation is better than a text string calculation. Also, if you want to update the field in the table, it would need to be a text field, which would provide you with a field that displays a proper decimal place, but the information wouldn't be as useful as it would be when it is in it's intended numeric status; ie: grouping on a customer or product and summing the Value.

    Paul,
    Your question is not too simple for this forum. There are all levels of knowledge and experience on this forum, and I've found the members to be very helpful. I initially came here looking for an answer and saw that I had the answer for some of the other questions here. I was glad to help.

    TD

  9. #9
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    Thanks Buckeye I was unsure. Hence the question I have not used RIGHT or LEFT before and was curious. I do not think they are actually SQL but come from Access' roots in BASIC. So I think I remeber reading.
    Darasen

  10. #10
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    Use this in the format option in table design of the mentioned field when the field is a number

    #.###,00

  11. #11
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    1. Paul's location is 'West of Atlanta' which is in the U.S. where we use a comma as a thousand separator and a period for a decimal point, therefore the format that you mention would need to be '#,###.00'.
    2. Changing the format of the field will not achieve the desired results. Paul said the decimal point is missing in the flat file he is importing. By changing the format the result for 3572 will be 3,572.00 when the desired result is 35.72.

    TD

  12. #12
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    1. Paul's location is 'West of Atlanta' which is in the U.S. where we use a comma as a thousand separator and a period for a decimal point, therefore the format that you mention would need to be '#,###.00'.
    2. Changing the format of the field will not achieve the desired results. Paul said the decimal point is missing in the flat file he is importing. By changing the format the result for 3572 will be 3,572.00 when the desired result is 35.72.

    TD

  13. #13
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    some habits never change :-))), but you are quit right.

Posting Permissions

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