The way numbers are stored

[Guest]

Its Friday afternoon and I have drawn a huge blank. So any help would be
greatly appreciated.

I have stored some values in a database for prices of materials, VAT and
Total etc.

The values are stored like normal currency eg 32.56, 1.99, 500.00 However I
need to convert them to be stored as a 9 digit number with no decimal place.

eg:
32.56 would be stored as 000003256
1.99 would be stored as 000000199
500.00 would be 000050000

Thanks in advance

Ernest

 

[Baz]

Its Friday afternoon and I have drawn a huge blank. So any help would be
greatly appreciated.

I have stored some values in a database for prices of materials, VAT and
Total etc.

The values are stored like normal currency eg 32.56, 1.99, 500.00 However I
need to convert them to be stored as a 9 digit number with no decimal place.

eg:
32.56 would be stored as 000003256
1.99 would be stored as 000000199
500.00 would be 000050000

Thanks in advance

Ernest

Why?

If some other cranky system needs the numbers exported to it in this format,
or some weirdo wants to see them on a report or the screen like this, then
the thing to do would be to reformat them in the output. To actually store
them in such a useless format would be madness, particularly since, with all
those leading zeros, what you would have is no longer a number, it's a text
string.

Anyway, wherever or however you do it, something like the following will
create the required format:

Format(Int(MyCurrencyValue * 100),"000000000")

 

[Dirk Goldgar]

Its Friday afternoon and I have drawn a huge blank. So any help
would be greatly appreciated.

I have stored some values in a database for prices of materials, VAT
and Total etc.

The values are stored like normal currency eg 32.56, 1.99, 500.00
However I need to convert them to be stored as a 9 digit number with
no decimal place.

eg:
32.56 would be stored as 000003256
1.99 would be stored as 000000199
500.00 would be 000050000

Thanks in advance

Do you really need to change the way the numbers are stored, or do you
just need to change the way they are displayed, formatted, or extracted?
Numeric data types just store the numbers, not leading zeros or a fixed
number of digits. For that, you'd need to use a Text field, and then
extra code to control data entry and interpretation. Maybe if you
explain what need is behind your question, we can suggest a good
solution.

 

[Chaim]

Do you want to store the numbers this way? Or display them like this? If you
store them this way, you are storing them as text. If you are storing them
both ways, that's a problem.

Simply storing as numbers and formatting to appear as you wish when you
display them is probably a better way to go. You can use the FORMAT()
function to take a number and display as a 0-prefixed nine character whole
number using: FORMAT (<your number> * 100, "000000000"). You have to
multiply by 100 to get rid of the decimals.

Good Luck!

 

[Guest]

Thanks for the quick reply Dirk I do need to change the way it stored as
it is exported into a text file, for someone else to upload. And they
require all my values to be in a nine digit field.

I guess it will make more sense if I explain the whole process. I get an
excel spreadsheet output from my program which I then use macros to put into
the format required.

This is then imported into Access. I will then use Access to export as text
file and use Fixed Width export specifications. This text file is sent to
another company for them to upload.

This company require me to show £1.99 as 000000199 in the text file they
upload. So I could either change it in excel or access.

Thanks in advance

Ernest Lai

 

[Baz]

Thanks for the quick reply Dirk I do need to change the way it stored as
it is exported into a text file, for someone else to upload. And they
require all my values to be in a nine digit field.

I guess it will make more sense if I explain the whole process. I get an
excel spreadsheet output from my program which I then use macros to put into
the format required.

This is then imported into Access. I will then use Access to export as text
file and use Fixed Width export specifications. This text file is sent to
another company for them to upload.

This company require me to show £1.99 as 000000199 in the text file they
upload. So I could either change it in excel or access.

Thanks in advance

Ernest Lai

Ernest,

You do NOT need to change the way it's stored. Create a query based on the
table. In the query, use the formula I gave you earlier to reformat the
currency fields. Then, export the query instead of exporting the table.

 

[Chaim]

Use the format function that Baz and I suggested for your output to the text
file. Create a query that applies FORMAT() to the currency field and use
that as the table that the Export Wizard writes out for you. You can play
with the settings of the Export Wizard to see what you get.

But the key is to realize that the saved query can be used in place of a
physical table.

 

[Dirk Goldgar]

Thanks for the quick reply Dirk I do need to change the way it
stored as it is exported into a text file, for someone else to
upload. And they require all my values to be in a nine digit field.

I guess it will make more sense if I explain the whole process. I
get an excel spreadsheet output from my program which I then use
macros to put into the format required.

This is then imported into Access. I will then use Access to export
as text file and use Fixed Width export specifications. This text
file is sent to another company for them to upload.

This company require me to show £1.99 as 000000199 in the text file
they upload. So I could either change it in excel or access.

I agree with Baz and Chaim; so I think you could call that a consensus.
Leave the numbers stored as they are, but export a query that
formats them the way you want.

 

[Guest]

Ah sorry I was being a numpty, I see what you all mean now. Thanks very much
for the help Baz, Chaim and Dirk!

 

[Guest]

Sorry to be a pain but I am not very good at access as you already guessed.
You really have to treat me like a dummy.

I have created a query which shows all the fields in my original table.
From this query I should use Chaim/Baz's formula in the criteria? However
when I do I get : "Data type mismatch in criteria expression" Maybe it is
something to do with:

DAO Field data type Constant

Currency DbCurrency
Double DbDouble
Numeric DbNumeric

FORMAT (<your number> * 100, "000000000")
Format(Int(MyCurrencyValue * 100),"000000000")

The fields i need to format with the above formula are "Data type Number"
"Field Size Double" Im sure I need to edit <your number> or MyCurrencyValue
but I cant get it to work. (tried quite a few combinations)

Many thanks again in advance

Ernest

 

[Guest]

I have found a temp solution. To work around for now

The figures i have are always in excel and will never change. During the
macro process I will multiply the figures by 100 (to remove the decimal
places) Then I have put 000000000 into the format field.

Obviously your solutions are better but I have only started using access and
cant really get it working yet.

but I think this lil cheat will do for now, until i understand access more.
I will continue to try your formats as access seems to be much more powerful
than excel

Thank you all for your help

Ernest

 

[Guest]

grr.. ok my idea nearly worked.. it shows the numbers in the format i wanted

ie 000005554 But when it exports it exports as 5554

im gonna try and get it to work with charm's and baz formula

 

[Baz]

grr.. ok my idea nearly worked.. it shows the numbers in the format i wanted

ie 000005554 But when it exports it exports as 5554

im gonna try and get it to work with charm's and baz formula

Are you sure? How are you looking at the exported data? If you are using
Excel, I think you will find that it is Excel which is changing the
displayed format. Try examining the exported data in Notepad or some other
text editor.

 

[Baz]

Sorry to be a pain but I am not very good at access as you already guessed.
You really have to treat me like a dummy.

I have created a query which shows all the fields in my original table.
From this query I should use Chaim/Baz's formula in the criteria? However
when I do I get : "Data type mismatch in criteria expression" Maybe it is
something to do with:

DAO Field data type Constant

Currency DbCurrency
Double DbDouble
Numeric DbNumeric

FORMAT (<your number> * 100, "000000000")
Format(Int(MyCurrencyValue * 100),"000000000")

The fields i need to format with the above formula are "Data type Number"
"Field Size Double" Im sure I need to edit <your number> or MyCurrencyValue
but I cant get it to work. (tried quite a few combinations)

Many thanks again in advance

Ernest

AAARGH, NO, NOT IN THE CRITERIA!!!

Put the expression in the "Field" row of the query design grid, instead of
the name of the field that you are reformatting. Don't forget that if your
field name has spaces in it, then you need to put square brackets around it
e.g.

Format(Int([my number] * 100),"000000000")

 

[Guest]

Are you sure? How are you looking at the exported data? If you are using
Excel, I think you will find that it is Excel which is changing the
displayed format. Try examining the exported data in Notepad or some other
text editor.

-------------

I export from Access to a .txt file. Basically the costs like 1.99 are now
stored as 199 in access. I went to the design view of my table and under
fieldsize I put 000000000 in the format. So when I view my table it shows
000000199.

However still exports as 199 (viewing the txt file in notepad)

 

[Guest]

AHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH

alarm bells are ringing now give me a few mins to twinke a few things
around.
should work now

like i said treat me like a dummy

Sorry to be a pain but I am not very good at access as you already guessed.
You really have to treat me like a dummy.

I have created a query which shows all the fields in my original table.
From this query I should use Chaim/Baz's formula in the criteria? However
when I do I get : "Data type mismatch in criteria expression" Maybe it is
something to do with:

DAO Field data type Constant

Currency DbCurrency
Double DbDouble
Numeric DbNumeric

FORMAT (<your number> * 100, "000000000")
Format(Int(MyCurrencyValue * 100),"000000000")

The fields i need to format with the above formula are "Data type Number"
"Field Size Double" Im sure I need to edit <your number> or MyCurrencyValue
but I cant get it to work. (tried quite a few combinations)

Many thanks again in advance

Ernest

AAARGH, NO, NOT IN THE CRITERIA!!!

Put the expression in the "Field" row of the query design grid, instead of
the name of the field that you are reformatting. Don't forget that if your
field name has spaces in it, then you need to put square brackets around it
e.g.

Format(Int([my number] * 100),"000000000")

 

[Baz]

-------------

I export from Access to a .txt file. Basically the costs like 1.99 are now
stored as 199 in access. I went to the design view of my table and under
fieldsize I put 000000000 in the format. So when I view my table it shows
000000199.

However still exports as 199 (viewing the txt file in notepad)

Oh dear, Ernest, you are going backwards now.

Putting a format in the Format property on the table only changes the way
the field is displayed, not the way it is stored.

As indicated before, you really need to get this working by exporting a
query. See my other recent post about where to put the formatting
expression in the query. If you still can't get it working, do this:

1. Create a query based on the table. Don't worry about the fields that
need reformatting, just put the fields into the query as they are.
2. On the View menu, click "SQL View"
3. Copy the text in the SQL window, and post it here. Indicate in your
post which are the fields that need to be reformatted.
4. I will then modify it to what you need, and repost it so that you can
paste it back into your SQL window.

 

[Guest]

Thanks baz for the help, I did paste it into the field so the query table had
bits renamed to Expr1 - Expr4. However exporting this query still didnt show
the 9 figure digits (000000199 exports to 199)


I will paste the SQL below for you. You will notice i have 4 fields with
your formula in. They are not in order (4 3 1 and 2) However the fields are
"Labour Value" "Material Value" "VAT" "Invoiced Value"




SELECT [Import to Access].[Agreement Number], [Import to Access].[Customer
Surname], [Import to Access].[Address line 1], [Import to Access].[Address
line 2], [Import to Access].[Address line 3], [Import to Access].[Address
line 4], [Import to Access].Postcode, [Import to Access].[Appliance Type],
[Import to Access].[Appliance Make], [Import to Access].[Appliance Model],
[Import to Access].[Invoice Number], [Import to Access].[Completed Date],
[Import to Access].[Breakdown date], Format(Int([Labour
value]*100),"000000000") AS Expr4, Format(Int([Material
Value]*100),"000000000") AS Expr3, Format(Int([VAT]*100),"000000000") AS
Expr1, Format(Int([Invoiced Value]*100),"000000000") AS Expr2, [Import to
Access].[Fault Code], [Import to Access].[Authorisation number], [Import to
Access].[Referral number], [Import to Access].[Repair Agent number], [Import
to Access].[Repair agent branch number], [Import to Access].[Serial number],
[Import to Access].[Part number #1], [Import to Access].[Part description
#1], [Import to Access].[Part number #2], [Import to Access].[Part
description #2], [Import to Access].[Part number #3], [Import to
Access].[Part description #3], [Import to Access].[Part number #4], [Import
to Access].[Part description #4], [Import to Access].[Part number #5],
[Import to Access].[Part description #5], [Import to Access].[Part number
#6], [Import to Access].[Part description #6], [Import to Access].[Rejection
message 1], [Import to Access].[Rejection message 2], [Import to
Access].[Fault as reported by cust], [Import to Access].[Engineer's report]
FROM [Import to Access];

 

[Guest]

I hope by looking at the SQL you can see that I have entered it correctly.
Your formula works great and shows fine in the query table. When i goto
export as a text file, I set my specifications so fields breaks are correct.

It shows up correctly with the 9 digit fields in the window where you can
move the
field breaks

However when i push finish to export the 0's dissapear

Thanks baz for the help, I did paste it into the field so the query table had
bits renamed to Expr1 - Expr4. However exporting this query still didnt show
the 9 figure digits (000000199 exports to 199)


I will paste the SQL below for you. You will notice i have 4 fields with
your formula in. They are not in order (4 3 1 and 2) However the fields are
"Labour Value" "Material Value" "VAT" "Invoiced Value"




SELECT [Import to Access].[Agreement Number], [Import to Access].[Customer
Surname], [Import to Access].[Address line 1], [Import to Access].[Address
line 2], [Import to Access].[Address line 3], [Import to Access].[Address
line 4], [Import to Access].Postcode, [Import to Access].[Appliance Type],
[Import to Access].[Appliance Make], [Import to Access].[Appliance Model],
[Import to Access].[Invoice Number], [Import to Access].[Completed Date],
[Import to Access].[Breakdown date], Format(Int([Labour
value]*100),"000000000") AS Expr4, Format(Int([Material
Value]*100),"000000000") AS Expr3, Format(Int([VAT]*100),"000000000") AS
Expr1, Format(Int([Invoiced Value]*100),"000000000") AS Expr2, [Import to
Access].[Fault Code], [Import to Access].[Authorisation number], [Import to
Access].[Referral number], [Import to Access].[Repair Agent number], [Import
to Access].[Repair agent branch number], [Import to Access].[Serial number],
[Import to Access].[Part number #1], [Import to Access].[Part description
#1], [Import to Access].[Part number #2], [Import to Access].[Part
description #2], [Import to Access].[Part number #3], [Import to
Access].[Part description #3], [Import to Access].[Part number #4], [Import
to Access].[Part description #4], [Import to Access].[Part number #5],
[Import to Access].[Part description #5], [Import to Access].[Part number
#6], [Import to Access].[Part description #6], [Import to Access].[Rejection
message 1], [Import to Access].[Rejection message 2], [Import to
Access].[Fault as reported by cust], [Import to Access].[Engineer's report]
FROM [Import to Access];

Oh dear, Ernest, you are going backwards now.

Putting a format in the Format property on the table only changes the way
the field is displayed, not the way it is stored.

As indicated before, you really need to get this working by exporting a
query. See my other recent post about where to put the formatting
expression in the query. If you still can't get it working, do this:

1. Create a query based on the table. Don't worry about the fields that
need reformatting, just put the fields into the query as they are.
2. On the View menu, click "SQL View"
3. Copy the text in the SQL window, and post it here. Indicate in your
post which are the fields that need to be reformatted.
4. I will then modify it to what you need, and repost it so that you can
paste it back into your SQL window.

 

[Baz]

Thanks baz for the help, I did paste it into the field so the query table had
bits renamed to Expr1 - Expr4. However exporting this query still didnt show
the 9 figure digits (000000199 exports to 199)


I will paste the SQL below for you. You will notice i have 4 fields with
your formula in. They are not in order (4 3 1 and 2) However the fields are
"Labour Value" "Material Value" "VAT" "Invoiced Value"




SELECT [Import to Access].[Agreement Number], [Import to Access].[Customer
Surname], [Import to Access].[Address line 1], [Import to Access].[Address
line 2], [Import to Access].[Address line 3], [Import to Access].[Address
line 4], [Import to Access].Postcode, [Import to Access].[Appliance Type],
[Import to Access].[Appliance Make], [Import to Access].[Appliance Model],
[Import to Access].[Invoice Number], [Import to Access].[Completed Date],
[Import to Access].[Breakdown date], Format(Int([Labour
value]*100),"000000000") AS Expr4, Format(Int([Material
Value]*100),"000000000") AS Expr3, Format(Int([VAT]*100),"000000000") AS
Expr1, Format(Int([Invoiced Value]*100),"000000000") AS Expr2, [Import to
Access].[Fault Code], [Import to Access].[Authorisation number], [Import to
Access].[Referral number], [Import to Access].[Repair Agent number], [Import
to Access].[Repair agent branch number], [Import to Access].[Serial number],
[Import to Access].[Part number #1], [Import to Access].[Part description
#1], [Import to Access].[Part number #2], [Import to Access].[Part
description #2], [Import to Access].[Part number #3], [Import to
Access].[Part description #3], [Import to Access].[Part number #4], [Import
to Access].[Part description #4], [Import to Access].[Part number #5],
[Import to Access].[Part description #5], [Import to Access].[Part number
#6], [Import to Access].[Part description #6], [Import to Access].[Rejection
message 1], [Import to Access].[Rejection message 2], [Import to
Access].[Fault as reported by cust], [Import to Access].[Engineer's report]
FROM [Import to Access];

That looks fine to me Ernest. I repeat my previous suggestion: are you
using Excel to examine the exported data? If so, don't, try looking at it
in Notepad.