Greetings all,
I am probably just being dense.
But how do I go about entering a percentage of say 5% in a numer field formated to percentage in a table?
If I enter 5 in translates to 500%, .05 as 0!
Thanks,
Brian.
Zimbabwe.
In one of my queries I calculate a percentage based on a change from one year to the next; however, sometimes there will not be any prior year data hence the percentage is undefined (i.e. current year amount/0=undefined). To stop the query from displaying an error when this is the case I wrote the following:
CapitalSurplusChange: IIf([P/Y Operating Data]![CapitalandSurplus]=0,"N/A",(Nz([C/Y Operating Data]![CapitalandSurplus],0)-Nz([P/Y Operating Data]![CapitalandSurplus],0))/Nz([P/Y Operating Data]![CapitalandSurplus],0))
I have the properties of the field set to percentage, but the calculation does not display as a percentage but as a long decimal. If I take out the Iif the percentages display fine however there are errors for the undefined calculations (hence why I inserted the Iif). How can I amend the code above to only display the "N/A" answers as text. I know about the formatpercent function, however I am exporting this output to excel and this when using the formatpercent or formatnumber operation the data still displays as text and not a number. Any ideas?
I have a "make table" query. Inside that query there is a simple expression that divides two fields by one another to arrive at a %. Example 5/100=5%. However, the data returned is not in a % format.I know that I can go into table properties and change the fromat to "percentage", but that will only work for the current table that I have created. Once I re-run the "make table" query, I lose the change to the format, and my value again returns to a number, not a %..
Is there any way to always have a percentage returned without having to manually change the format each time that I run the query, or having to make a "delete" and "append query" ?
I have set the format of a text box (named: scrILS) to percentage. It shows on the form as 0.00%
When I try to run calculations off of it like: = [scrILS]*[totBuysFYDP1], it throws this error: #TYPE!
It seems as though the textbox is keeping it as text for the percent sign "%" is preventing any calculations against it... here's the strange part... it doesn't happen initially when I open the form... the calculations work and I get no error, but I have code that recalculates everything based on values picked in a list box... on the requery I get the error.
When I look at the watch frame for that control it shows the scrILS value as:
"00.0%" not 00.0%... any thoughts on this?
I attempted to just take the thing as a string and use a replace function against the "%" then calculating... that works, but then the initial form load throws the error.
Seems like I am missing something in the property settings, but I don't know what. Is there something that forces the format value, setting percentage as a number vs. text?... I thought that was just part of the format setting...no?
Hi,
I have a query with 3 fields - PaymentAmount and PaymentTotal. The third field is a percentage of the two e.g:
PaymentAmount = 10000
PaymentTotal = 12000
PaymentPercentage = 83.333333333333
In my form I would like a text box to display the two combined i.e. £10.000.00 (83.33%).
At the moment it displays 10000 83.3333333333333. I can use £#,##0.00 to format the current half, but how can I then format the percentage to display 83.33%?
Thanks
How can I change with VBA the Y axis of a graph to percentage?
sqlstr = simple sql giving with date, value
Graph47.RowSource = sqlstr
I can give the axes min/maximumscales
.Axes(2, 1).MinimumScale = frm_MinscaleY
set the colors and weights
.seriescollection(1).Border.Color = graphcolor
.seriescollection(1).Border.Weight = Lineweight
.seriescollection(1).MarkerSize = MarkerWeight
.seriescollection(1).MarkerBackgroundColor = graphcolor
But everything I try to set the axis to percentage does not work... No failure message, but nothing happens. I have tried multiple things:
.seriescollection(1).datalabels.numberformat = "0,0%"
.seriescollection(1).datalabels.numberformat = "#,#%"
.seriescollection(1).numberformat = "percentage"
.seriescollection(1).numberformat = "0,0%"
.SeriesCollection(1).tickLabels.NumberFormat = "%"
and a lot more....
I have a text percentage that reads 28.0%. I want to convert to a numeric percentage that reads either 0.28 or 28.0%, preferably the latter. The Val function returns an error.
View 3 Replies View RelatedI have two fields CountOFAppeal Description and Amount Solicited.
I just want to get the percentage of response.
So this is what I used:
=[CountOfAppeal Description]/[Amount Solicited]*100
When I go into the properties to format it to % and add decimal places I get a crazy number
2520.46% When it should be 25.20%
When I change the format to general number the decimal point is in the right place 25.20.
I have a table with the name of a report, the items on the report, and the number of items on the report. I would like to count the instances of the names of the individual reports that appear on another table and then divide that count by the number of items on the report.
View 1 Replies View RelatedI have been asked to create a database that creates orders, debits stock etc for my company. Now I have been asked to put in some kind of discount table with percentages for each individual item for each individual customer. What would the best way of doing this?
I figured in the Customer Table I would have a code such as 'CustomerADiscount' and 'CustomerBDiscount' etc. and then in my table maybe
Product A - CustomerADiscount - 10%
Product A - CustomerBDiscount - 20%
Product B - CustomerADiscount - 5%
Product B - CustomerBDiscount - 7%
This link these values up within a query? Would this be the best way of doing it?
Is Access able to determine a percentage in "10%" or "5%" or do I need some type of formula for it instead?
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
When I try to transfer (ctrl c + v) data from a table in Excel to a table in Access it loses format.
Exemple: $ 1.000,00 (Excel) become 1000 (Access).
I need to keep the format to make sure that the code works properly.
Hi All,
The following code is supposed to insert a date formatted as dd/mm/yyyy into a table, when i view the mySQL string in the debug window it shows the correct format, however when i open the table it displays the date as mm/dd/yyyy.
The table's date field userdate is formatted as a short date field with an input mask of dd/mm/yyyy.
My computers date settings are set up as dd/mm/yyyy as well
Any ideas on hows to correct this?
Thanks in advance. :)
Public Function Test()
Dim mySQL As String
Dim dtDate As Date
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb()
dtDate = Format(Now(), "dd/mm/yy")
mySQL = "INSERT INTO tblCurrentUser( userdate ) values (" & "#" & dtDate & "#" & ");"
Debug.Print mySQL
'*The debugger shows* INSERT INTO tblCurrentUser( userdate ) values (#04/05/2005#);
' However the value in my table tblCurrentUser is05/04/2005 is in mm/dd/yyyy format
Set qdf = db.CreateQueryDef("", mySQL)
qdf.Execute
End Function
Here is my problem:
Let's say that I have a table with a field name of "Phone Number". I input about 200 #'s in the following format: xxx-xxx-xxxx. I decide that I want to use the an input mask to create (xxx) xxx-xxxx. I input 50 more #'s. The previous 200 have the old format while the 50 recent #'s have the new format. How can I update the entire field from the old format to the new without reentering the data? Or can I? If I can, will the change also apply to any forms created off of this table?
Thanks
The transfer spreadsheet function only allows one to export a table from the database to an excel file format. I want to output the table to a file in csv format using a simple function. Is this possible ?
View 1 Replies View RelatedI know in creating columns in a table where I have date/time you can select long short, etc. But, I want my column name to be YR and the values of 2014, 2015, 2016, 2017 all the way to 2020. So I end up with 7 rows. Then another date related table that houses the months. It will have a column name of MTH and the values Jan through Dec so a total of 12 rows. I have tried in format putting YYYY and MMM but when entering the data, it gives me an error saying I have to convert, click the calendar and choose a date. I don't want to choose month, day and year. I just want the year.
View 2 Replies View RelatedHi guys, hope you can help.
I have two fields (that I'm interested in) in my table: Insurance Policy End Date and Reminder Date.
I would like the default value in the reminder date to be exactly six weeks before the value entered in Policy End date. I'm not at all sure how to do this with the expression builder. Could you guys nudge me in the right direction?
Eternally grateful.
I guess this is pretty fundamental but I cant get it right! I want to search for a member by post code so I need to have validation for my table field post code so that when my parameter query accepts the post code input records will be returned covering all input types e.g. Users can put in CF72 (space)9AD or CF729AD and still get the same records or alternately how do I make users enter Post Codes with the correct UK spacing i.e 4 characters a space then the remaining characters?
View 7 Replies View RelatedA lady at work created an Access database that imports a dBase 5 file and when I go to import this file on User #1 PC it gives the error;
"External table is not in the expected format."
I can use the same Access database and same dBase file to import (stored on a shared network drive) from User #2 & #3's PC's and all works well. The only problem is that it is User #1 job to do this.
I unloaded Access 2003 and reloaded it and still the same results. All 3 PC's are identical via hardware and software.
It use to work until this past Tuesday when it started to give the error.
My steps are:
open the "Sales" Access database
select the prices table
Get External Data / Import and then select Prices.dbf
Then the error message comes up.
I am not an Access database guy and could use some help!
Thanks
WhooTAZ
I have set the Left([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],8) AS [DISTRIBUTION DT] field property to Short Date, 99/99/0000;0;_ . When I run the query I get that field formated as a short date, but when I try to create a table from the same query the data returned is no longer in the date format. Is there something I can do to have the make table results look like what I get when I just run the query without creating a table?
SELECT
DSERDBA_RECEIPTS.RCTH_ID_CASE AS [CASE],
DSERDBA_RECEIPTS.RCTH_DT_RECEIPT AS [DT RCTH],
Left([DSERDBA_RECEIPTS].[RCTH_RECEIPT_NO],8) AS [DISTRIBUTION DT], DSERDBA_DISBURSEMENTS.DSB_DT_DISBURSE INTO [TABLE 1]
FROM [UNIVERSE OF CASES] INNER JOIN (DSERDBA_RECEIPTS INNER JOIN DSERDBA_DISBURSEMENTS ON DSERDBA_RECEIPTS.RCTH_RECEIPT_NO = DSERDBA_DISBURSEMENTS.DSB_RECEIPT_NO) ON [UNIVERSE OF CASES].STAT_CASE_ID = DSERDBA_RECEIPTS.RCTH_ID_CASE
WHERE (((DSERDBA_RECEIPTS.RCTH_ID_CASE)="20028570P"));
How can I do this? There doesn't seem to be an member of Field called format. Why is this? I want to make the display on a Boolean field, in a Table made from a Make Table Query, to Yes/No, rather than default 0/-1.
View 7 Replies View RelatedMy Access DB has two tables linked to excel spreadsheets. Excel is the only way I can acquire this data from another source. The Excel spreadsheets update themselves on a regular basis.
My problem is that one of the fields in the spreadsheet needs to be formatted as a date and I have been unable to get Excel to handle this automatically.
Is there some way that I can date format a field in a table that is linked to an Excel spreadsheet?
Unless I can figure this out, I have to manually be involved with each Excel Update.
Thanks in advance.
I have an issue with queries not showing sufficient decimal points, even though the underlying table does have the formatting I desire.
If I open Table 1 directly, for Field 1 I have a value of 0.1439254445378153860918842318 using a format of decimal / fixed / Precision: 18 / Scale: 0 / Decimal: 2 and it shows in the table as 0.14 until I click on the cell in which case I get the full value above.
This is how I want it to show (0.14). When I run a query on this table, not setting any formats in the query, I get 0.00, and clicking on the cell only provides a result of 0. I've tried a ton of different underlying formats on Table 1, and been unable to get a different result...
1. I am trying to extract table from Access in XML format. On completing the extraction, the XML disappears from the folder where it was stored.....
2. Also, How do i see SQL query for table in Access... the SQL view option is not seen in the ACCESS dropdown.
I wanted to assign the field "Number of magazine" with special format based on date/time format but showing only year and month in the format: "yyyy-mm".
So in property of this field in format I put yyyy-mm and in input mask I type 0000-00;;-
I also created the form based on the table containing above field and I defined format and input mask for corresponding formant in the same way like at the table.
But if I try to type date for example 2014-01 in text box of the form it comes up with the full date 2014-01-01. Why does it do like this? What do I do incorrectly?
Hi,
I am trying to email a table in text format using :
DoCmd.SendObject acSendTable, "Table", acFormatTXT, , , , "Results", "Attached are the reslts"
It sends the file in text file format, but it adds "-" & "|" characters all over the file. Is there a way that I can send the file in simple "Comma Separated" file format.
Thx,
Jatz