Access 2010 format a field in a table. 2

[faxpay]

In Access 2010 I have a db table with a field that has only numbers imported into it. The datatype is "NUMBER". I have been struggling trying to format as below:

NUMBER 4562359 format to 45623.59 The right 2 numbers always the cents.
NUMBER 4562300 would be 45623.00

I tried to do this in Access by formatting in design mode and also tried with an SQL statement. My SQL has to bring up 9 fields but this one field has to have this formatting. If I multiply every item in that field by .01 I get the right result but the problem lies in that I need to bring up 9 fields with that one having .01 applied and still have the same field name for that column.

Stuck!!
Thank You
Tom
tnfaxpay

 

[Andrzejek]

You can Format your NUMBER - which will convert it to String - when displaying it to the user, so even the Integer can be displayed this way:
[tt]
Dim i As Integer
i = 123
Debug.Print Format(i, "#.00")
[/tt]
and you get: [tt]123.00[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

Formatting changes nothing in the data.

You must actually divide each value by 100!

 

[SkipVought]

Is this field supposed to represent dollars currency?

 

[Andrzejek]

I little clarification to what I posted.

Yes, you need to keep the data in your table as (Number) 45623.59 and 45623 (which will not keep it as 45623.00 ) but you can display it to the user with formatting Format(YourNumber, "#.00")

This way your data in the table is 45623, and users see: 45623.00


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

@Andy: but his number is not 45623. Rather it's 4562300.

I'm guessing that this field represents CENTS or HUNDREDTHS so they can do integer arithmetic. Then the final display result can be divided by 100 (or multiplied by one hundredth)

 

[faxpay]

I see all your answers but don't see my solution. Maybe this is more of an SQL question? Anyway the NUMBERs I am trying to format are a column of dollar and cents amounts. The 2 right most digits are cents. I don't want any dollar sign. So then 45678
would convert to 456.78.

I can either accomplish this in Access in design view with the Format property so that when loaded into my dataviewgrid object in THE VB2010 Form it appears correctly, or I can accomplish this in my SQL statement in VB2010 when I load the records into the dataviewgrid. I HAVE BEEN UNSUCCESSFUL IN BOTH APPROACHES.

Any suggestions or solutions are appreciated.

Thank You,
Tom
tnfaxpay

 

[PHV]

I can accomplish this in my SQL statement
Sure, divide the number by 100 in your SELECT clause.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[faxpay]

Hi PHV,
My SQL statement has to bring up 9 field columns. Only the one column has the divide by 100 applied to it. Cannot make this work in SQL. I can get an additional column that comes up with the field name expr1000. Any help?
Tom
tnfaxpay

 

[PHV]

What is your actual SQL code and which column should be divided by 100 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[faxpay]

Hi PHV,
Statement as follows:

SQLStr = "select EIN, PyrTyp, EFTnumber, SetlDate, TaxForm, CreatedDate, TaxPeriod, PymtAmt, PySt " &
"from Eftps3 " &
"where EIN = " & mEINholder

The field PymtAmt is the one needed to divide by 100.

Tom
tnfaxpay

 

[PHV]

So, what about this ?
SQLStr = "select EIN, PyrTyp, EFTnumber, SetlDate, TaxForm, CreatedDate, TaxPeriod, PymtAmt/100 As Amount, PySt " &
"from Eftps3 " &
"where EIN = " & mEINholder

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[faxpay]

Hi PH,

That's good except for numbers such as 153000, and the like, are converting to 1530 whereas should be 1530.00
Dropping last 2 zeros

That's progress though.

Tom
tnfaxpay

 

[PHV]

So, what about this ?
SQLStr = "select EIN, PyrTyp, EFTnumber, SetlDate, TaxForm, CreatedDate, TaxPeriod, Format(PymtAmt/100, '0.00') As Amount, PySt " &
"from Eftps3 " &
"where EIN = " & mEINholder

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

1530 is very equal to 1530.00!

Formatting cents is merely a formatting display issue.

What are you doing with the resultset?

 

[faxpay]

I am going to get a total from the resultset and display a Total in my vb2010 project. It would just be more readable for the user.
Tom
tnfaxpay

 

[SkipVought]

Then 1530 is just fine from your query. Somewhere else you can use the Format() VB function to format a display value any way you want.

 

[PHV]

Did you try my suggestion timestaped 29 Aug 14 19:12 ?

 

[faxpay]

Hi PH,
I missed that timestamp thread "Did you try my suggestion timestaped 29 Aug 14 19:12 ?" That fixed it. I did not know you could use Format in a SQL.

Thank You. Learned a lot.

Tom
tnfaxpay

 

[Andrzejek]

I suggested to [tt]Format[/tt] it in my original post on 29 Aug 14 8:48, but you didn't like it :-(

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.