calculation

S

Steiner

i have linked a worksheet from an excel worksheet to the access database. I
now have created a query with this linked worksheet to a table that i have
created in access. I am now going to use this table that I have made in the
query in a report, however, in the report I need to have the information in
thousands. in other words, 12,939,991 should be shown like 12,940. Can I
change this in the query without affecting my original link with the excel
worksheet. if i can what formula do i need to use so that each repective
cell is in thousands. To whom this may concern thank you in advance for your
time and help
 
A

Al Newbie

In your query for the field that needs to be in thousands divide the amount
by 1000

eg my field is called Amount - in my query this would look like

ChgAmt: round((Amount/1000),0)

HTH
In the report you would set the properties of the field to Fixed and 0
decimal places
 
S

Steiner

so i createa new field and build an expression
laurent

Van T. Dinh said:
Use a Calculated Field in the Query:

Thousands: CLng([YourField] / 1000)

--
HTH
Van T. Dinh
MVP (Access)


Steiner said:
i have linked a worksheet from an excel worksheet to the access database.
I
now have created a query with this linked worksheet to a table that i have
created in access. I am now going to use this table that I have made in
the
query in a report, however, in the report I need to have the information
in
thousands. in other words, 12,939,991 should be shown like 12,940. Can I
change this in the query without affecting my original link with the excel
worksheet. if i can what formula do i need to use so that each repective
cell is in thousands. To whom this may concern thank you in advance for
your
time and help
Click to expand...
Click to expand...
 
S

Steiner

When i use this it says that i have used invaid syntax
do i have to build an expression in a new field
keep in mind i want every cell to be in thousands
thank you in advance
 
V

Van T. Dinh

I am not sure what the question is but my guess is that you are thinking of
creating a new Field in the Table?

If this is the case, the answer is NO. You use a Query and in the Query
Grid, use an enpty Column and wnter the expression I posted. A calculation
in the Query that returns a value not in the source Table is a Calculated
Field (or Calculated Column).

If you are not sure, suggest you check your Access book. Most Access books
show these basic manipulations in the Queries chapter(s).
 
S

Steiner

well i am not trying to create a new field. Currently the cells in my field
are in millions but this makes it more difficult to read in a report soi
would like to have each cell be in thousands. So my question if whether i
have to do this calculation in a new field in the expression builder? if yes
i tryed the formula that you gave me and it does not work. This is a pretty
basic calculation (dividing each cell by 1000 but i am not able to find it in
my access book.
 
V

Van T. Dinh

* Create a Query based on your Table. Select all the Fields you need for
your Report except the number Field you want to divide by 1000.

* In the first empty Column of the Query Design Grid, type in the expression
I posted in the Field row replacing place-holder with correct Field name
from your Table.

* Run the Query and check in the DatasheetView that the extra Column returns
values you want.

* Save the Query and use the Query as the RecordSource for your Report
instead of the Table.

Check the index of your book for the / operator.
 
Top