Queries :: Formatting Whole Numbers And Decimals Differently
Nov 26, 2013
I want to print the results of a query where some values are whole numbers, some have 1 decimal place, some have 0.3333333 recurring etc. Problem is that in order to fit all the columns on a page the column width does not allow all the 0.33333... to display. So what I see is like this (there are lots of columns, I am just giving an example from one column)
891
833.5
######
I don't want to fix decimal places to 1 because then I would see 891.0 instead of 891. How can I format the number field so that whole numbers are shown without decimal place, numbers with 1 decimal place stay like that i.e. 833.5 is fine as it is. But display something like 313.666666666667 as 313.67 rather than ##### getting put in as not enough room for all the decimal numbers.
View Replies
ADVERTISEMENT
Apr 24, 2014
I have an INSERT query like so:
Code:
INSERT INTO table1 (column1, column2, column3, column4, column5)
SELECT (field1, field2, field3, field4, field1 & "" && field2)
FROM mastertable
According to the format desired by a client, column5 from table1 needs to have field1 and field2 concatenated and represented as two-digit numbers if they're a one digit (has a leading zero).
View 7 Replies
View Related
Jul 10, 2014
I have created an Access 2010 Database for tracking inventory. As parts move to each location, quantities automatically subtract from the previous location. I now realized that some parts are destroyed.
The problem is the if I simply enter a negative value to indicated that a part was destroyed at one location, it appears as a positive value in the previous location. Is there a way to make negative values not affect a specific query?
View 4 Replies
View Related
Oct 25, 2006
Hi all,
I am trying to format three fields to write to a table.
For reasons unknow they are created as text so I tried using Cint, Clng and cdbl to export thee fields as numbers so when they are exported to an excel spreadsheet they are as number fields.
This actually works for exactly that but the end result knocks off the leading zero if the value is 0910 it ends up as 910. The source table it pulls this from actually has the leading zero if the length is less then 2.
How do I get to format the fields to numbers but keep the exact value ?
Any help is greatly appreciated.
Mitch.....
View 1 Replies
View Related
Mar 24, 2008
My database is a maintenance record of our building complex. The unit/building number is the key field. The problem I am having is having access recognizing the unit numbers and building numbers, not as whole numbers.
Example: I-380, 10-380, 2-390, 13-370, and so on. When sorted, access treats them as whole numbers. I have tried two separate fields but it would be nice to have only one field and be sorted by building numbers.
I am not a coder so be gentle.
Can you help?
View 9 Replies
View Related
Apr 19, 2011
I try to modify an access 2010 template from the MS website.The problem is that the template as is programmed does not accept as input negative prices. I need to use negative prices for offering discounts on my invoices. In my country the discounts are only displayed as products with negative prices/values. We do not use % discounts.
i try to create an products with negative price and to use it as an discount on the invoice. Both the products input form and the invoice input form give me the same error : "please specify an price list that is not negative" or "please specify non zero positive prices" ( sometimes it allow me to enter but when the software try to validate the form/invoice i just entered it give me those error/messages)
I changed all the field from currency in general numbers Where do I have to change in order to remove those formatting/condition..? The template has a lot of macros.. I an not able to change those macros if there are some formatting conditions in them. If it is allowed I will post the link to the free template i use to be more conclusive..Or I can give/ upload the template I use.
the link to my data base ( dropbox)
View 3 Replies
View Related
Oct 23, 2014
How can I format the output of subtracting two times as a time value. For instance 10:00 am - 9:00 AM = 1 (where I want to show the output as "1").
View 7 Replies
View Related
Feb 19, 2014
Using Access 2007
Win 7
Total_Time: Format(nz(DateDiff("n",[Start],[End])/60))
This is my expression in a query.
I need to only show 2 decimal places in the results field.
View 6 Replies
View Related
Oct 6, 2014
I am using VBA to execute a 'Make Table' (named 'DT'). One of the fields in DT (named 'Dollars') contains numbers that have 6 to 8 digits; some are positive and some are negative.
These large numbers with no commas (or parentheses when they're negative) are incredibly hard to read.
Any VBA coding that will change both the "Format" and the "Decimal Place" Field Properties on the table I make (i.e. "DT"). I want to the Format property to read (#,##0.00;(#,##0.00)) and the Decimal Place property to read "2"). This will allow me to display a number like -10326786.41 as (10,326,786.41) which is incredibly more readable.
Is it possible to do this programmatically; I've search the internet high and low and could find anything remotely useful.
View 8 Replies
View Related
Feb 4, 2014
So, I am inserting new records into a table. The values in the decimals are being stored as zeroes.
Field Properties
Long Integer
Standard (I also tried Currency)
2 Decimal Places
When I remove the "INSERT INTO" part of the query, the decimal data is in the results. I started out not using the FORMAT function. Then I tried it using "Standard", "Currency", and "#,##0.00"
View 3 Replies
View Related
May 22, 2014
I have a statement as follows:
Code:
ohc: IIf(([oh]/[csqty])>1,[oh]/[case_qty] & " (LTSU?)",[oh]/[case_qty])
The intention was that it would add " (LTSU?)" to the calculated number where it was less than 1 (or not a whole number). Stupidm, me didnt account for fractions greater than 1 (ie 1.566). Just wanted to know how I can identify all 'non-whole' numbers in the above IIF statement.
View 3 Replies
View Related
Aug 12, 2014
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
View 14 Replies
View Related
Nov 4, 2014
When using the carriage return how do I format the data so it will export with 2 decimals?
In the example below I need the Estimated Material Cost to export in in format: 500.00 --- NOT 500.
MTLCost: [Estimated Material Cost] & Chr(13) & Chr(10) & [C]
View 8 Replies
View Related
Aug 4, 2005
We recently put together a database for a call center. The file is located on a public drive, and all users seem to be able to access the file (at the same time) and input new information.
On one particular form, I've found an interesting problem: I've placed a button at the bottom of the form, which will go to a new record when clicked. The command to go to a new record is clearly in the OnClick event, however users in the call center have to click twice in order to execute the code. I don't have to.
We're using the same form, in the same file, opened with the same version of Access.
What's wrong with this database?
Thanks for any help you can give me,
Double-Clicking,
Andrew
View 7 Replies
View Related
Feb 4, 2007
in a Dbthere are 2 fields which contain 'matching' data. (there are other columns too)
A Genesis
B Exodus
C leviticus
D Numbers
E Deutronomy
I want to shuffle both columns so they are both randomly mixed up
B Numbers
A Exodus
C Detronomy
E Leviticus
D Genesis
How do I do it?
I have explored the random function but that randomises the records but keeps each record intact.
I then want to print these two columns to a report which numbers each line - easy enough once I have the query BUT I want to print an 'answer page ' at the end of the report which will look like this:-
1 B Numbers (5)
2 A Exodus (1)
3 C Detronomy (4)
4 E Leviticus (3)
5 D Genesis (2)
I thought of appending a unique ID string to each pair and only displaying the first part of the string e.g
A@1 Genesis@1
etc and I can do this but maybe there is a simpler way.
Lastly supposing I would want to keep the order of column one and only shuffle column two?
Thanks for any advice!
View 1 Replies
View Related
May 12, 2015
I have a basic form that includes check boxes. All works well. But on my (Windows 8) machine, the check boxes display as big ugly black squares. If I open the db from any another machine (Windows 7), check boxes display as I'm used to with a nice outline and bluey tinge to the middle if not set! All machines have Access 2013 on them.
View 6 Replies
View Related
Jun 6, 2013
why access 2007 is displaying an image oddly on a 2003/2008 terminal server farm. I am reasonably sure the problem is an access on terminal server issue because if you view the image in paint, image viewer, or word it displays fine on the terminal server. I don't think it is a resolution or color issue because once again it displays fine in paint, etc regardless of color depth.
The image below shows the difference in the png graphic. The image is transparent (the blue section being the background)
[URL]
As you can see the terminal server image is pixelated.
The form the image is sitting on has a blue gradiant that goes light to dark left to right. The form properties are below
[URL]
The image properties are below:
[URL]
View 2 Replies
View Related
Dec 1, 2005
Hi,
Im trying to combine 2 fields below into one column.
ID - 001 (This is formatted in table using 000)
Sometext - "BLAH"
In the query I have written this ...
Newfield: [Sometext] & [ID]
But when I preview the query the data displays as "BLAH1" and not "BLAH001"
what should i do to correct this formatting issue?
many thanks!
View 2 Replies
View Related
Nov 23, 2014
In the Table I have in the trailer database has, among many, 2 fields called 'Maximum Length' and 'Minimum Length'. Say, the data in these fields could be 5 and 4 respectively. I would like to create a search box where I can type in any length such as 4.4 and the database would return the trailer that can fit this length. (Sot the searched value is between the Min. and Max. length) What would I need to type into the query box and the search form to type in the length?
View 6 Replies
View Related
Aug 3, 2015
I have a centralised database that imports data from several other databases at the click of a button to produce a "global" query of data.
This is presented as a datasheet form which I am then able to filter. This is a subform to my main form.
I have a button on my Main form with a macro to ExportWithFormatting to excel. I was under the impression that exportwithformatting exported the filtered data.
How to export my filtered query data to Excel and not the whole query data?
View 3 Replies
View Related
Oct 2, 2013
The data within my tables is formatted correctly and when I run a standard query on the data, it comes through the query with the same formatting. However, when I run a query that needs to total the values (whether it's sum or average) the values lose any formatting (and by total I mean the one in design view, not in datasheet view).
I then need to manually format each columns "Format" and "Decimal Places" properties to what I want. I have quite a few queries with quite a few columns, so this is very time consuming. Is there a way to do this faster without VBA? In Excel I can simply highlight multiple columns and format all of them or apply a format painter. I don't see any similar functionality in Access 2010.
View 1 Replies
View Related
Feb 4, 2014
how to conditionally format query results? I have read another forum post that says that query conditional formatting is possible.This database keeps track of projects and their associated tasks & statuses. I created different queries depending on task name( i.e. assignment date, approval date, etc.) Now, with some of the tasks, if it is overdue, it needs to be highlighted red. If it's cutting it close, should be yellow. I can't figure out how to set up a conditional formatting rule to address this. So instead I've had to create to separate queries for "red" and "yellow" rules, and display them separately in the Dashboard form.
View 4 Replies
View Related
Aug 1, 2014
Need to confirm whether the Default formatting option in the Conditional formatting Dialog won't work in datasheet view of a form. Please note that all conditional criteria are working fine but not the Default Formatting option - only in the Datasheet view (In Single Form view the default formatting is working fine)
View 11 Replies
View Related
Dec 19, 2014
Is there any code to be able to be used within a query to locate the 1,2 or 3 digit numbers?
such as 1 ,11, 111 - 2, 34, 834 etc...
View 4 Replies
View Related
Dec 31, 2013
I have a column containing an id that consists of the first two letters of a weekday followed by an incrementing number. For example, for Monday, I have "MoA1" "MoA2" "MoA3" ... "MoA11".
The problem is that when I sort my list, it is ordering it: "MoA1" "MoA10" "MoA11" "MoA2" "MoA3" etc. Currently, my order by property is set to
MID(TABLENAME.SORTFIELDNAME, 3, LEN(TABLENAME.SORTFIELDNAME))
View 3 Replies
View Related
Sep 15, 2013
I have the following code and I am trying to show a COUNT of duplicate ID numbers, so for example
ID ID_COUNT
2000 2
2000 2
2001 1
2002 1
2003 1
Code:
SELECT [DaisyServiceData-Import].*, Companies.CompanyName, Company_vs_Services.[Tariff Code], IIf([ClashForce]=True,"",IIf([ClashOveride]=False And [ClashClear]=False,IIf([DaisyServiceData-ClashExc].[DaisyServiceData-Import].[ID] Is Null,"",1),"")) AS ClashExc, IIf([Sell Price]="","",IIf([SellRateForce]=True,"",IIf([CompanyName] Is Null,"",IIf([SellRateOveride]=False And [SellRateClear]=False,IIf([Total Cost]>[Sell Price]+0.001,1,""))))) AS SellRateExc, IIf([Tariff Code]='1',[T1Rate],
[Code] ...
A GROUP BY query will not work for me as the recordset needs to remain updatable.
View 2 Replies
View Related
