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!
I'm having an issue with the way my number fields are displayed on my form.
I want the number to be dislayed with 2 decimal places. I have formatted the numbers so that they are 'fixed' and specified 2 in the decimal places property but my numbers are rounding themselves down.
eg. I type 1.25 but it comes up as 1.00
Thank you in advance for your help.
I am trying to calculate the square footage on a form. The formula I have is
(WxH)/144
For example:
W = 6.375
H = 11.5
Square footage should be 0.509115
However I am getting 632.500000
The width (W) and height (H) are formatted as double. So is the square footage. Is this not correct?
Hi,
I have a problem with a table that has number formats setup as usual - #,##0.00;(#,##0.00) and formatted to 2dp. However when inputting say 1.58 into the table it will round to 2.00 and does not show any decimals except as .00. When I remove the number formatting and set the decimal place to Auto, or 1 the same occurs but no decimal places are showing.
Can anybody explain why this is happening? I have tried searching through the properties, etc. to no avail.
Thanks very much for your help,
Roly
Is there a way to format the day of the month as an ordinal number? For example instead of formatting the day as January 1, I would like to format the day as the 1st, 2nd, 3rd etc. day of the month. I need to do this in order to match the format of a contract that we want to use as a mail merge.
View 2 Replies View RelatedI am trying to get a "formatted phone" number to appear on a "make table" query. I have revised the input mask properties to format the field to parantheses around the area code and a dash after the first three digits. The query works fine as long as I run a "Select Query" but when I change the query to a "Make Table" query, the new table does not come across with the telephone number in the correct ( )xxx-xxxx format! Using the Input Mask wizard, I've tried both options of storing the data and still get the same results. I do NOT want to have to go into this new table each time and reformat the telephone number as I'm sending the table directly to a switchboard in a different database. Any ideas on how to keep the formatted phone number intact?
View 1 Replies View RelatedIs there a way to change the formatting of a numbers column to the formatting :
1 = 0001
11 = 0011
111 = 0111
1111 = 1111
But that would have to apply to this also
1 - 2 = 0001 - 0002
11 - 22 = 0011 - 0022
111 - 222 = 0111 - 0222
1111 - 2222 = 1111 - 2222
If that makes sense ....
I am trying to make sure that my double will have a comma for thousands, but will also be able to display more than 2 decimal places (probably 4).
I am struggling with what format to use - if the number doesn't have any decimal places, the point is still put in.
#,##0.####
Examples:
23.2 = 23.2
64587.255 = 64,587.255
5000 = 5,000.
How do I make a number display as "001" and not "1" in datasheet view? The field type is number not text.
View 3 Replies View Relatedhow to have a different conditional colour formatting for a plus and a minus number
If the field value is a Plus Number
The background colour will be Green
If the field value is a Negative Number
The background colour will be Pink
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).
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.
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?
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.
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 RelatedNeed 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 RelatedI am creating a line graph from a running total query to show our income from items shipped for each month. Currently I have the following Code in my query which works but it displays the month as a number in my graph and I would like it to show the Month name.
Code:
SELECT DatePart("yyyy",[ShippedDate]) AS AYear, DatePart("m",[ShippedDate]) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND DatePart('m', [ShippedDate])<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot
FROM tblJobs
WHERE (((tblJobs.ShippedDate) Is Not Null))
GROUP BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate])
ORDER BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate]);
I tried this solution, but I get an error in the RunTot field, I'm assuming because Access can't use the month name in dsum.
Code:
SELECT DatePart("yyyy",[ShippedDate]) AS AYear, MonthName(DatePart("m",[ShippedDate])) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND MonthName(DatePart('m', [ShippedDate]))<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot
FROM tblJobs
WHERE (((tblJobs.ShippedDate) Is Not Null))
GROUP BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate])
ORDER BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate]);
Do any of you know a way I can make this work?
Export Query Data with Formatting and Layout Using A Button On Form Before Or After DoCmd.TransferSpreadsheet
View 4 Replies View RelatedI have a totals query that provides an avg for each month. i'd like to be able to use a text box control (named "Date") on a form (named "Report Runner") to show only a certain month and it's avg.
I tried using this as criteria on the "MonthGroupPMC" field:
Code:
Format([Forms]![Report Runner]![Date], "yyyy-mm")
but the results came up blank.
how can i filter the results of this query to show only one month, specified by the [Forms]![Report Runner]![Date] control?
I am trying to export into a temp table (all text fields because it will be going into a text export later) and I'm having difficulty adding 0:00:00 onto the value of "ApptdateLast" for the update...
INSERT INTO cbt_Export_Temp ( TransactionType, ID, ApptdateLast )
SELECT "Add" AS TransactionType, "BC" & [TransId] AS ID, dbo_Info.ApptdateLast & " 0:00:00" AS ApptdateLast
FROM dbo_examInfo
I have a main report with 5 sub reports. There is a detail page for each company and a totals page at the end. The record source for each sub report is a Union query (combines the detailed information with the total information.
One of the sub reports displays currency amounts. The detailed reports display the currency correctly: $26,001 (no cents). The totals sub report does not display the currency formatting: 468934
When I run just the totals query the amounts display correctly ($468,934) by using the CCUR(TotalAmount) variable type conversion. The VarType for the amount field in the totals query is 5 (double precision).
When I combine the detail query and totals query into a Union query the detail amounts display correctly but the total amount is missing the formatting.
Here is the union query.
SELECT TblCompany.TblCompanykey, FormatCurrency(ProviderCostsRetrieval([TblCompanykey],1),0) AS TotalCost
FROM TblCompany
ORDER BY TblCompany.TblCompanykey
UNION ALL SELECT 9999 AS TblCompanykey, CCur(Sum(([QryRptProviderCostsDuringPeriod.TotalCost]))) AS TotalCost
FROM QryRptProviderCostsDuringPeriod
GROUP BY 9999;
I am making a classic sales over time crosstab query.
Rows: Customers
Columns: Sale months
Sales date is defined by the ETD of the order.
However, with the simple Format([ETD],"yyyy-mm") I get regular months, but I need to adjust the months to be between the 21st and 20th rather than 1st to 31st(30th).
August would be 7/21/2013 to 8/20/2013
September 8/21/2013 to 9/20/2013
Is it possible to format the columns this way?
I have a simple crosstab query:
FirstName and Surname as Row Headings.
Date as Column Heading.
OnShift as a Value (Count).
These are from the table DailyActivityLogs. Also in that table is a choice field called Weather that lets you choose the conditions that day (Weather, Work, Part Weather, Subbed).
This is the report the crosstab query generates:
What I would like to do is colour the Count fields depending on what the weather was that day. It seems possible, as Weather sits in the same table, but the field Weather isn't in the crosstab query.
Can I do this?
I have a query in my database called "Open Date" which is a date formatted field when imported as DD/MM/YYYY. The criteria is set to:
>=#06/04/2014# And <=#05/04/2015#
The query works just fine. My customer has come back and asked for the way the date is displayed to be in the YYYY-MM-DD format (i know it's not ideal but thats what they want). When i've done that using the following
Format([Table].[Open Date],"yyyy-mm-dd")
The query then returns no data.
Hi all.
I have text field with mask:
XXXXX-X
(x are digits), for example - 4 01-1, 4 01-2...
I need find field with text beginning 4 01 and max of last symbol. Last symbol +1, then again convert to text field. For example:
for example 4 01-1, 4 01-2,4 01-3 - must find 4 01-3 , then 3+1=4 and 4 01-4 format again to text.
Please, help me with formatting.
Thank You in advance.
791335.12pack. This is the object that is in one of my access fields. I need to extract the 12 and place that in another column called qty.
View 1 Replies View Related