Topics Search

Number format in queries

Number format in queries
Views: 18
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:

Similar posts...


Ordinal Number Format

Ordinal Number Format Icon
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

Define column format as output of a UNION QUERY Icon
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

Number format issue Icon
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

Change format from text to date in Access table Icon
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

Access refuses to format number as number - formats as date instead Icon
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?!

Report which only counts

Report which only counts Icon
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 to Date format

number to Date format Icon
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

Number showing in wrong format

Number showing in wrong format Icon
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

Double vs. Decimal Data Type - Access 2010

Double vs. Decimal Data Type - Access 2010 Icon
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?

output of queries like to have rounded numbers

output of queries like to have rounded numbers Icon
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