Topics Search

Number format in queries

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

More topics

 

Ordinal Number Format

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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.

anywho,

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

Preview
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 - ###

Potential
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

Preview
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

Preview
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

Preview
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.