Data Types

[wazza]

Hi

Im using a query to extract data from one table to append into another.

i then query from the second table to perform calculations and to display results in a list box... the results i want to see are numbers with 1 decimal point

I have tried setting data types to decimal, double, fixed 1 etc - for all the tables other than the first source table

i still cannot get the results i want - some figures will apprear fine others are 3.6666666666 etc

pls help -

 

[maxmangion]

try Format([fieldname], "0.00")

 

[Mile-O]

Just set the format of the field (in its proeprties) to be 0.0

 

[wazza]

Ive tried various properties for the field in the table which is displayed in the list box.

in the table im seeing 0.0 formats but in the list box 3.6666666 for some values and 0.0 for others

I havnet used : Format([fieldname], "0.00")

i simply typed this into the field format properties (is this correct)

??

 

[wazza]

Field decimal property ....??

im using the field decimal property as 1 - however when data is appended to this table it doesnt display it as 1 .... 0.00000000

this figure is created using a calculation does it inherit it to the updated table or something?

i tried even changing the calculated field property to decimal 1 but still 0.00000

its urgent i get this sorted.. pls help?!

 

[maxmangion]

is it possible that you upload your sample ?

 

[Mile-O]

Ive tried various properties for the field in the table which is displayed in the list box.

The formatting of the field in the table applies only to the table. The listbox is not the table and therefore the format does not apply.

You shouldn't base a listbox (or a combobox, form, report, etc.) on a table. Leave the table to store the data.

For the aforementioned objects, use a query. In the query you can format the field, sort fields, and only select the necessary fields (cutting some time off the time your database wastes doing stuff).

 

[wazza]

sorry - didnt make it clear, i have queried from the table using a cross tab..

how do i specify the format for a field using a query?

 

[wazza]

ive found how to specify the field format in the query - i cant find any changes in the sql after the update - im using sql in vb, how can i specify the field format to show 1 decimal if required, if not then just the whole number

 

[Mile-O]

Format(myField, IIf(CInt(calculation) = calculation,"0","0.0")

 

[wazza]

sorry to ba a pain... could you explain the formula please

- whats the [calculation] bit?

- do i use this in the select - part of the sql ?

 

[wazza]

my sql is:

ListBox.rowsource = ("Transform sum(TotalPoints) AS Total"
SELECT allmyfieldcolumns
FROM mytable
Group BY
oRDER BY
Pivot myDay



i want to change the format of the TotalPoints which is displayed as Total

- do i use this statement in the first transform line - how?

 

[Mile-O]

could you explain the formula please

- whats the [calculation] bit?

- do i use this in the select - part of the sql ?

Format(calculation, IIf(Int(calculation) = calculation,"0","0.0")

Format() function - changes the presentation of a value
IIf() function - provides an value based on an either/or situation
Int() - a function that truncates a decimal value to an Integer (where possible)

calculation - an expression


What the expression above does:

It gets a value and determines whether it has decimal places. If it has decimal places it formats the evaluated value as a number with one decimal place. If there is no decimal places then it formats it to have no decimal place.

i.e.

calculation = 5/2


Format(5/2, IIf(Int(5/2) = 5/2,"0","0.0")
=> Format(2.5, IIf(Int(2.5) = 2.5,"0","0.0")
=> Format(2.5, IIf(2 = 2.5,"0","0.0")
=> As 2 does not equal 2.5 then it returns "0.0" as the Format

Format(4/2, IIf(Int(4/2) = 4/2,"0","0.0")
=> Format(2, IIf(Int(2) = 2,"0","0.0")
=> Format(2, IIf(2 = 2,"0","0.0")
=> As 2 equals 2 then it returns "0" as the Format

I'd use it in an SQL statement like this

SELECT Format(myfield, IIf(Int(myfield) = myfield,"0","0.0") AS Example
FROM MyTable;

 

[wazza]

im using a cross-tab query

TRANSFORM SUM(FIELD AS TOTAL) ......
select
from
group by

The field i want to format is the value being totalled... i cant get the vb in the previous post to work... my results are just an empty listbox

ive tried various was to use the vb... i.e.

TRANSFORM SUM(format(FIELD AS Total)) ......
select
from
group by

PLS HELP

regards