Number format in queries

Is it possible to set a default so that any number that is generated in a query, either by count, sum or calculation is automatically formatted to the standard format

Sponsored Links:

Related Topics

Ordinal Number Format
I have a number field on my bound forum. The form is used to only display the records. I don't use the form for entering new record. I do that running an Update querry from an unbound form. What I need on the number field is to show the number in ordinal format (1st, 2nd, 3rd, 12th, etc.). I need to do this on the unbound form as well, so that when I enter the number, it should show the number in ordinal format. And then the bound field on the bound form should also show this number in ordinal format. When I print reports, I need this field to be in ordinal format. I have to keep this field in Number data type because it is used in calculations within queries and forms

Define column format as output of a UNION QUERY
I have a question about UNION queries.
I created the SQL by "merging" together two queries that collect data from two tables (identical data sets and format for each column of the tables) and everything works fine in terms of output. everything but the format of one column (a checkbox column).

If I execute the queries separately I get all the queried columns keeping their original format (i.e. there is also the checkbox shown correctly) in both queries.

If I use the UNION statement. I get the records merged but the checkbox column is shown as "0" and "-1" number (or binary?) column.

How to tell Access to present the output of that column as checkboxes format (as it is originally in the queried tables) and not change it to numbers/binary representation

Number format issue
My queries before a union have numbers formatted as currency. After the union query, I lose the currency format and numbers have a large number of decimal places. I've tried

Format([$USUnit Price],0.00,2),

but the results are text, not numbers. I've tried using the same format in both the union query itself and a subsequent query. I don't know what to try next

Change format from text to date in Access table
I'm having a little trouble with my Access database. The previous database manager has a table with fields for month as text format and not time or date. Unfortunately, I need to calculate the length of an event occurring (the length of flowering time from when it starts to when it ends in terms of the number of months) and I can't do that with the field in the text format. Is there a way that I can convert the data from text format to time format (only as the month, not as a full date like 1/1/2007)? I've tried to figure this out a number of ways, including update queries with new fields, but I always get an error message.

Access refuses to format number as number - formats as date instead
No matter what I do with my linked table, access thinks its a date. and its a bloody number!
ARGH. its ruining all my union queries.

I have tried forcing text-to-columns in excel, making it a number, making it general, hell, even text.

How can I force access to realise its a number?!

number to Date format
I have a number field which has date information in it. ex - 20110112 YYYYMMDD in that format. Don't ask why they didn't just make it a date format.

Right now a date conversion table is used. So when referencing that field it does a join on the number date and return the date format. Two field table. First column is 'number date format' and the second is 'return date format'. The table is manually kept up each year.


Just trying to figure out how to format that into a Date format so that I can perform date function on it using sql so that I don't have to maintain the date conversion table

Report which only counts
I need to create a report which takes the data from two queries, then shows the information in the following format.

Number of Eligible's sent: ###
Of these letters, ## have
returned with these answers:
1. Accepted Eligible - ###

Number of Potential's sent: ###
Of these letters, ## have
returned with these answers:
4. Wrong Address - ###

As you can see, one from the Eligible query, and the other from the Potential Query. each of the ### are going to be numbers which will be counted from the queries.

the first set of hash's will simply count how many files are in the queries. The second will count how many of these have an answer (which is provided in one specific field for both queries) and the last will count how many of a specific type of answer was given.

Number showing in wrong format
I have a field that is;

Type: Number
Size: Double
Format: General Number
Decimal: 0

I insert number like this: 6042001354531
And it is showing like this: 6,042001354531E+12

Why? How can I make it show correctly

Number showing in wrong format
I have a field that is;

Type: Number
Size: Double
Format: General Number
Decimal: 0

I insert number like this: 6042001354531
And it is showing like this: 6,042001354531E 12

Why? How can I make it show correctly?

number to Date format
Ex - 20110112 YMMDD in that format. Don't ask why they didn't just make it a date format.

Right now a date conversion table is used. So when referencing that field it does a join on the number date and return the date format. Two field table. First column is 'number date format' and the second is 'return date format'. The table is manually kept up each year.

Double vs. Decimal Data Type - Access 2010
I have an account number field with a number of 17 digit values. Our default for that field in our Access tables has been a double data type. That worked fine until we started getting the large account numbers.

Many, such as 62078190357102307, behave fine. That account number imports as expected.

However, if the account number ends in 199, 299, etc., such as 10024912715100199, it comes into the table as 10024912715100200 if the field format is double.

In the source DB2 table, the field is Decimal 20. If I format the Access table field as Decimal 20, all is well.

My problem is that we have the account number field everywhere in multiple databases, tables, queries and reports. It is a key field for us. Changing it everywhere would be a challenge.

Can someone enlighten me as to why the fields appear to round if they end in 99. Is there anything I can do to prevent that, short of reformatting all of my account number fields as decimals?

Linked table data formats differ for same information
I am trying to create queries from linked tables that read their information from an Oracle db where the fields are formatted to text. Thus numbers are formatted as text in the Oracle db.

Problem I have is that Access looks at the field content and then decides what format it is going to allocate to the field in the linked access table. That would be well and good if Access allocated the format consistently, but unfortunately I have a case where I need to join on three fields where these three fields from the two different tables to be linked have the identicalfield names and formats in oracle and yet once they are linked tables in Access the three fields are text, text, number and text, number, number respectively. When I try and join on thethree fields the query naturally complains.

Why does Access behave that way and is there a way in which one could get a consistent format for the fields in access linked table?.

Default number format?
When I design a new query in design view is it possible to have a default number format? I always want to see numbers in general (with the commas) format but it's kind of a pain to have to go to properties every time I create a new query

Week number wrong
I am extracting some information from my database in week format.
I have done this in the normal way of using the Format function and W.
The week number it gives me is one ahead of actual week number in my diary.
Has anyone else come across this, or know how to fix it

output of queries like to have rounded numbers
There does not seem to be a simple, obvious way, to round you output of a query:

I am getting numbers like this:

And rather have a number like this:

Sometimes I am doing a sum on a field.
Or if I am just query dumping a field.

I need a simple format process for output of my queries.

Thank you Russ - Rneuman at

Access 2007: Percent Format Issue
I am trying to have a field be a Percent Format.

In access 2007, I am reading that I should just have the properties be:

- Field Size: Long Integer
- Format: Percent
- Decimal Places: 2

However, in the table, when I enter a number that is smaller than 100 now, it automatically shows ZERO. When I enter 100, it shows it as 100% (and the same for any number above 100).

Then, I tried to just change the format to General Number, with 2 decimal places.

When I try to enter say, 0.45 - it changes it to zero. It does not show the two decimals. When I enter in 0.9, it rounds it up to

Format decimal numbers with fixed width
How can I format a decimal number so that:
. the width of the whole number is 10 characters
. 2 digits always after the decimal point
. for positive and negative numbers

Format(34.5, ###0.00) gives 34.50
This varies the number of spaces to the left of decimal point

Format(34.5, 0000.00) gives 0034.50
This gives a fixed width but not for negative numbers and it looks ugly

I want to print tables and have the columns line up
For example (I hope the numbers below do line up)
Number1 Number2

Change Format of “Number” Data Type
Access 2010 also allows user to change format of all supported data types. This post covers changing format of Number data type. To change the format of the field having number data type, open the table in Design view, right click the table and click Design View.

Convert 20020322 number into Date Format
I have two columns in Access, Entry_Date & Value_Date.

When I import this data into Access via a text file it is recognised as a number format ie 20020322. I want to show this as date format in the easiest possible way.

I want to be able to show both these columns as date format ie, 20020322 as either 22-03-2002 or 22/03/2002. I then want to be able to work out the number of days between both dates eg Value_Date minus Entry_Date for example.

Ideally I would like to update the columns and not have to insert any additional columns.

Changing Empty Field Format To AutoNumber With Macro
I have used a data definition query to add a new field to a populated table and set the format of the new field to number. The new field contains no data but other table columns do contain data.

I would like to be able to change the format of the new field from Number to AutoNumber so that it auto populates but rather than do it manually I would like to change the field format from within a macro.

I have looked at the runcommand option available as a macro action but cannot find any method to make the change from within a macro. If I had some code in a module I would be able to use the OpenModule or possibly the RunCode action in the macro but my coding skills are nil.

If there are any solution or suggestions as to how I can go about changing the field format from Number to AutoNumber from within a macro I would be most grateful for