473,325 Members | 2,816 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

How do I add leading zeros in an amount field and remove the decimal point?

I'm sure there's an obvious answer for this, but it's not coming to me. I have a make table query that figures a calculated amount for one column. I need to convert this table to a text file with no spaces or delimiters. The calculated field needs to be 8 characters with no dollar sign and no decimal point. How do I get the leading zeros in the file and the decimal point out?
Dec 15 '09 #1
5 3581
ADezii
8,834 Expert 8TB
How about?
  1. Posting the Table Name
  2. Posting the Field Names and their Data Types
  3. Post some sample Data
  4. Example of how you would like the Exported Data to appear
Dec 15 '09 #2
Currently there are three columns in this table. The first column is the EmpID which is a text field with 9 characters. The second column is SAM2down which is 10 characters. The third column is dedamt which is a calculated field. Its format is a number field with a double field size and Auto decimal places. Below is some sample data from the table.

EMP_ID SAM2down dedamt
999999999 MSECCMSECC 3
888888888 MSECCMSECC 5
777777777 MSECCMSECC 16
666666666 MSECCMSECC 40
555555555 MSECCMSECC 1
444444444 MSECCMSECC 7.5
333333333 MSECCMSECC 2

Here is what I would like it to look like in the text file.

999999999MSECCMSECC00000300
888888888 MSECCMSECC00000500
777777777 MSECCMSECC00001600
666666666 MSECCMSECC00004000
555555555MSECCMSECC00000100
444444444MSECCMSECC00000750
333333333 MSECCMSECC00000200
Dec 15 '09 #3
hjozinovic
167 100+
stateemk,

I would first create such field in query and then take it out to a text file.
To create such field you can use expression in your query like this:
Expand|Select|Wrap|Line Numbers
  1. FinalString: [EMP_ID]&Format(Fix([dedamt]) & Right(Format([dedamt];"#,00");2);"00000000")
Please note here that i'm using symbols ; and , as it is adjusted to my local settings (Croatia)
You might need to replace some of those with your own smbols, but you should get the result.
regards,
h.
Dec 15 '09 #4
ADezii
8,834 Expert 8TB
Assuming your Table Name is Table2, the following code will Export the contents of the 3 Fields in this Table to a Text File named Results.txt in the Root Directory of Drive C:, namely (C:\Results.txt). You can change the Path of the Constant (conOUTPUT_FILE) to any Folder/File name you like. There will be no Delimiters between the Fields, and no Decimal Points in the Calculated Field. The results of the Calculated Column will be as you requested. You can just as easily run this code against the Query itself and not Create the Table, if you so desire.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3. Const conOUTPUT_FILE As String = "C:\Results.txt"
  4.  
  5. Set MyDB = CurrentDb
  6. Set rst = MyDB.OpenRecordset("Table2", dbOpenForwardOnly)
  7.  
  8. Open conOUTPUT_FILE For Output As #1
  9.  
  10. With rst
  11.   Do While Not .EOF
  12.     Print #1, ![EMP_ID] & ![SAM2down] & Format((100 * ![dedamt]), "00000000")
  13.       .MoveNext
  14.   Loop
  15. End With
  16.  
  17. rst.Close
  18. Set rst = Nothing
  19. Close #1
Dec 15 '09 #5
Thanks for the suggestions. I will try tomorrow to see what happens and let you know.
Dec 15 '09 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: david | last post by:
Hi, I have an application as follows: MySQL database Back-Eend linked to MS Access Front-End and ASP Web Application. I require users to enter Serial Numbers such as: 0105123567 (10...
5
by: samik_tanik | last post by:
I need to export a datagrid to Excel. I could did this. But, also need to keep the leading zeros in the data. How can I acheive this? Any help would be appreciated. -- Thanking you in...
1
by: Jean-michel | last post by:
I need to convert a decimal field to char() but also trim the leading zeroes. Any idea? I could not find any function to do that.
1
by: mmmgood1 | last post by:
Help, I'm linking an excel spreadsheet in access and I have datafields with leading zeros (01021). When the file is linked in access, I get a #num in the field with the leading zeros. The zeros...
6
by: Clint Stowers | last post by:
Using A2k Exporting a query to a CSV file. The problem is any text fields (i.e. 000345) lose any leading zeros. Exporting to an excel file this problem does not exist. Tried to create a...
5
by: OneDay | last post by:
I've got a field that has some old data with text in it, but all forward data will be a 3 digit number. But many of the numbers are still only 2 digits. I would like to force the leading zero in...
2
by: chris | last post by:
Hi, I have a simple ms access application that allows you to scan barcodes in to a form which stores them in the database. The barcodes are 6 digits in length e.g. 555666 but my handheld...
9
by: Chester | last post by:
I'm working on an app that records data collected by service technicians (VB.Net front-end, SQL Server 2000 back end). The technicians need to record numbers with varying scale and precision. For...
0
by: Monty | last post by:
Hi All, I am having a problem with leading zeros being stripped from fields in a CSV file when I bring them in using Jet/OleDB. In VB.Net/VS 2008, I am accessing a CSV file like so: sSQL =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.