I have set up a form on which there is a graph which draws data from a query. I have set up a text boxes to take in the Y axis min, max and interval values so the user can customize the graph according tot he range coming out of the query. This all works fine and is perfect, however, i also need a report and set up a report with the same graph which can be printed to pdf, however, i cant get the y-axis to adjust like i do with the one on the form,
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:
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?
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.
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?
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%?
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.
i have two charts in ms access and i want to combine these two charts in one chart. do you know how i can have two y-axis in ms access charts with two fields for data area.
I have inserted a bar chart onto a form using a totals query. The query is grouped by days on stock, eg. '0-30', '30-60', '60-90' etc. which is therefore the labels on the x-axis.
The chart displays the correct data, however, the chart automatically displays the categories on the x-axis in alphabetical order... '0-30', '120-180', '30-60' etc.. Is there anyway to adjust this order to be eg. '0-30', '30-60', '60-90' etc?
I have a pivot chart based on a crosstab query. I would like the items on the category axis (x-axis) to appear left to right in the order that they appear in the query results.
Some specifics: Tables: tblFreq FreqID (PK, Number, Range 1-7) Frequency (text)
tblResp RespID (PK,Number, Range 1-5) Response (text)
tblResults ResultID (PK,AN) FreqID_FK RespID_FK
Query: TRANSFORM CInt(Nz(Count(tblResults.ResultID),0)) AS CountOfResultID SELECT tblFreq.Frequency FROM tblResp INNER JOIN (tblFreq INNER JOIN tblResults ON tblFreq.FreqID = tblResults.FreqID_FK) ON tblResp.RespID = tblResults.RespID_FK GROUP BY tblFreq.Frequency, tblFreq.FreqID, tblFreq.FreqID ORDER BY tblFreq.FreqID PIVOT tblResp.Response;
[code]...
Which I suppose is alphabetically ordered.I am unable to use OrderBy in the forms property sheet because tblFreq.FreqID is not an available field, even though it's an expression in the query.
Dim FrmGraphObj As Object Set FrmGraphObj = Forms![frmE Weekly Efficiency]![gph_WeeklyEfficiency].Object.Application.Chart FrmGraphObj.Axes(xlValue).TickLabels.NumberFormat = "0%"
I continually receive a runtime error 1004 " unable to get tick labels property of the axis class"
if I remove this code, then I error on the following code:
Dim FrmGraphObj As Object Set FrmGraphObj = Forms![frmE Weekly Efficiency]![gph_WeeklyEfficiency].Object.Application.Chart If FrmGraphObj.SeriesCollection(2).HasDataLabels Then
also a runtime 1004: "unable to get the seriescollection property of the chart class" on the last line above
searched this forum and found:
If your chart is in a form (or report), you have to:
1) refer to the form (or report) name (Form_Charts)
2) refer to the name of the object frame holding your chart (.Graph1)
3) refer to the object within the frame (.Object)
4) refer to the application that created the object (.Application)
5) refer to the actual chart itself (.Chart)
6) refer to the axes collection and select the axis you want to reference - in this case the category, or X-axis (.Axes(xlCategory))
I made the assumption, that I would just replace xlCategory with xlValue for the Y-axis. So I'm back to:
Set FrmGraphObj = Forms![frmE Weekly Efficiency]![gph_WeeklyEfficiency].Object.Application.Graph With FrmGraphObj.Axes(xlValue) .TickLabels.NumberFormat = "0%" End With
Same error....
Looked in the Microsoft Graph Visual Basic Reference and it indicated:
"Tick-mark label text for the value axis is calculated based on the MajorUnit, MinimumScale, and MaximumScale properties of the value axis. To change the tick-mark label text for the value axis, you must change the values of these properties."
I reset my code to call these 2 functions prior to changing the number format.....
Public Sub txtMaxPercent_AfterUpdate() Dim FrmGraphObj As Object Set FrmGraphObj = Forms![frmE Weekly Efficiency]![gph_WeeklyEfficiency].Object.Application.Chart FrmGraphObj.Axes(xlValue).MaximumScale = txtMaxPercent End Sub Public Sub txtMinPercent_AfterUpdate() Dim FrmGraphObj As Object Set FrmGraphObj = Forms![frmE Weekly Efficiency]![gph_WeeklyEfficiency].Object.Application.Chart FrmGraphObj.Axes(xlValue).MinimumScale = txtMinPercent End Sub
now I am receiving error 1004 again, this time it states "Unable to set the minimumscale property of the axis class" erroring on this line....
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?
I created a number of graphs on forms that graph GamesSold vs RDate. If a game has no sales, there won't be any data. Is there a way I can add a label that will automatically display "Game is not yet released" and make it dependant upon the graph having data, and invisible if it does?
I'm trying to have a linked Excel chart in Access form. What I've done so far is create a chart in Excel and Paste Special>>Linked into Access.
I also have code inside Excel that will update chart data, it works fine.
Then I have code in Access that calls the code in Excel to update the data.
The data gets updated fine and the chart in Excel gets updated but the chart in Access only gets updated if I close and open the form again.
Here is the code that will update the Excel Data
Public Sub Import_VRSS_Graph_Data(strDayType As String, strTimeBand As String, strEntrance As String, Ws As Worksheet) ' Create a connection object. Dim cnPubs As ADODB.Connection Set cnPubs = New ADODB.Connection ' Provide the connection string. Dim strConn As String
I have a linked Excel graph in my form. The process I go though to update them is:
User clicks on button Excel opens up Procedure in Excel runs that updates the data and the graphs Excel Closes
The user does not see any of that. This process works fine. But the linked graph does not actually update in the Access Form. To do that I run the following code:
Code: Set ctl = Me!OLEUnbound_pream With ctl ' Enable control. .Enabled = True ' Set Locked property to False. .Locked = False ' Set Verb property to activate for editing, but not visible .Verb = acOLEVerbShow .Action = acOLEActivate End With
The problem I get is that once .Action = acOLEActivate is ran, Excel opens. I don't understand why it does that and how do I close it.
I'm currently working on a project that calls for a graph that is based on an equation which the users can change while in the database.
the equation is basically an annual compound interest formula: 65*(1+x)^([YearNumber]-1), where x is the rate of change as a decimal, and what the users can change.
I've tried to use a simple query with this equation as one of the expressions, where the x is displayed as [Rate as decimal], but when I do this, I get an error on the table: the database engine does not recognize [Rate as Decimal] as a valid field name or expression.
I've also tried tying the equation to an unbound text box on a form called "RateasDecimal", with a button that opens the form with the graph on it, but I get the same error - except [Rate as Decimal] is replaced with "forms!form1!RateasDecimal".
is it possible to create a graph like this in access.
I have a list of dates in the mm/dd/yyyy format and I am looking to get it into the fiscal format of yyyyww. I am able to do this with the datepart and format functions, but I need to make it so that the fiscal month begins in January but the first week starts if there are three or more days in the week. For instance if Jan 1st is a Friday then this stands as the first fiscal week, if it is a Saturday then it does not count as the first week.
datepart and format functions have the Use the first week in the year that has at least 4 days for the firstweekofyear option but I need it where it has at least three to make it work.
I'm new to access programing and have been working on a handover form for shift workers, as part of the handover we have to include call volumes forcast and actual calls received over a period.
I have a form which links to my table (Handover) the two fields are named, "239 Forcast 18:30-00:00" & "239 Actual 18:30-00:00" I then want to add a third column with percentage variance to show wether the percentage of calls is higher or lower than forcast.
I manually input the data into the forcast and actual fields and want this to then populate a textbox for the variance percentage next to this.
So far I have tried: =Sum(100-[239 Forcast 18:30-00:00]/[239 Actual 18:30-00:00]*100)
The above sum seems to be a little unstable and when switching to view different handovers the value in the variance stays the same, regardless of the figures in the mentioned fields.
I am trying to add a percentage sign (%) after a number I get from a table, thus the number 1,5 should display 1,5% and NOT 150%.
I am using the Format Property of the text field.
I tried #,##% which converted the value to a percentage (1,5 became 150%) Then I tried #,##"%" and #,##\% Access ignored both and changed the value in the property field to #,##% Using the Format function is not an option because it is a bound field which should be editable.
I have a problem when i create a pivot table . X axis and Y axis is show the number of ID (Primary key value) instead of owner name. How can i change this ? I high light with red rectangle . I want name there , not number ( this number come from table primary key).
Hi all, I want to create a pivotchart from a query, embed it in a form and use a combobox in the main form to allow the user to select the x axis value. So in a query with fields city, state, country, etc the user would be able to chose one of those fields as the category(x axis across the bottom) to display data. Is this even possible? If it is, how do I do it? As it is now I can add multiple categories to my chart- but it just makes a big mess of the chart. I can embed a chart in a form and use a combobox- but it allows me to choose from within a field- but not from among fields. Also- how do I access the code to manipulate the chart? Thanx in advance
