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.
TRANSFORM Count([Copy of qryDailyCountOfValidations].Staff_Number) AS CountOfStaff_Number SELECT [Copy of qryDailyCountOfValidations].PatternStartDate, [Copy of qryDailyCountOfValidations].[End Time] FROM [Copy of qryDailyCountOfValidations] GROUP BY [Copy of qryDailyCountOfValidations].PatternStartDate, [Copy of qryDailyCountOfValidations].[End Time] PIVOT Format([Start Time],"Short Time");
it shows the number of employees that start a shift at a particular time and the time the shift ends, on any day.What i need to show on a graph is the count number of employees starting at x time and plotting the number of hours the employees are on shift until they leave or another employee starts....
-the y axis is the count of employees -the x axis is the start time of their shift
10 9 8 7 xxxxxxxx 6 x x 5 x x 4 xxxxxxxx x 3 xxxxxxx x 2 xxxxxxxxxx 1 0 00:00 03:15 05:30.........................23:59
So the above mock up says 3 employees start at 00:00 and continue until 03:15 then another employee starts, then 4 employees continue 06:00, then 3 more employees start, 7 all together, then 5 employees leave later etc...
-the level of detail needed is to plot for every 15 minutes. -The [end time] is not strictly needed, just included to show what columns i have available
achieving this on a graph on an access 2010 report..i have updated a test database so you can see what the data looks like.
I am trying to generate a report that is based off of a query. The query has a form filter that it needs to filter the data. I keep getting a jet engine error and couple others.
The form has year, start week, and end week on it. I can get the query to work fine. When I try to open the report, Access says it doesn't recognize the " [Forms]![frmUptimeFilter]![StartWeek] " as a valid field name or expression.
I have a form built from a table and in a specific cell I need an equation but can't figure it out yet. I have several different fields and I need 2 fields divided to get the result. i.e. Total_Actuals divided by SA_CDI_Money = MoneyPercentage. Any ideas how I can get this to work?
I have three sources of data which all equate to a reconciliation of the same data e.g. the same record of a stock holding is held on three separate systems, which must all be the same.
I am trying to build a an equation in a query field, which compares each quantity:
Qty Diff: [Dealer Qty]-[Pulse Qty]-[SWFAL Qty].
If the quantities are all the same, then I want to return a Zero. If one or more of them are different, I want that difference to show. Variance of the above equation that would do it?
Hi, I wish to use the results of a crosstab query in another query which will make table. The trouble is the field names are not always the same in the crosstab. The results are coming from a normalised table so if there are no records for a particular field then that field will no appear in the crosstab.
I need to make my 'make table query' (qryTabletImport_CollarMakeStaging) account for the missing fields names when it tries to make the new table. Since I have made the make table query with all availble field names I get this error when some are not present.
<the jet engine doesnot recognise 'qryTabletImport_CollarConvert.Hours' as a field name or expression>
(because the field Hours doesn't have any data in the normalised table)
What I am wanting is to be able to build dynamic form content/elements based on entries on a table. This is for a gym membership system. What this form is going to be used for is to allow the front desk to scan a membership card which then performs a search on the database. Part of this search is going to be on a table that contains various add-on classes, tanning sessions, etc that a member can add beyond their base membership.
On the left side of the form, I will display a picture of the member and their name. What I am wanting to do on the right side of the form is to build a dynamic list of the add-on perks they are enrolled in.
I know I could hard program elements on the form to be visible or invisible, but that would be static and leave gaps when I have to turn things off b/c that member isn't enrolled in that class, etc.
Is this even possible with Access and VBA? I know I could do this sort of thing with a webpage using PHP, PERL or whatever. I don't know the limits of Access Forms and VBA.
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 have a form that shows a list of all of my records in my database. I want to be able to click a button called "Report" and have that print a report that has all the records I have filtered on my form. I have a report in the format that i want it in, however, currently it prints every record and not just what is shown on my form. (The form is dynamic and I want the Report to be dynamically based on the form) HELP PLEASE!
Company Product Sales 1 A 5 1 B 2 1 C 7 2 A 8 2 B 1 2 C 2
I want a query to produce a table showing Products A though D which is the sum of Products A and B. Result:
Company Product Sales 1 A 5 1 B 2 1 C 7 1 D 7 2 A 8 2 B 1 2 C 2 2 D 9
I have many rows and many summations and the current union query and supporting queries are pretty messy and not easily changeable. What I'd like to do is have a third table which would hold the summed product names and the formula used to get to it (Product D, A + B) and have a query that can be dynamically based off of that table. Do I need to use VBA? Does anyone have a better idea? Any help or even a good search term would be appreciated!
Hi guys, I am trying to create a form or subform that should be created after a button is pressed and some complex filetering is occured and records are saved in a multiple arrays so I need to generate a form and controls according to those array values. Please help.
Actually I can not get data directly out of the query its just too complex bla bla bla. Finally I have three arrays each array has same number of values and I want to generate a form on the basis of the values of the array. Please Help me. I have tried creating form but Could someone please show me the right path.
I have a large master table with hundreds of fields. Say, for example, in the master table, they are listed as "A1", "A2", "A3", "B1", "B2", "B3", "C1", "C2", "C3", etc. The form for this would draw every field, with a label matching the field name.
I have a second table that lists all the same fields, but has two extra columns. The first column's values will have what is known as the "friendly name" of the field. So for example, this column would have values of "A one", "A two", "A three", "B one", "B two", etc. The second column's value is whether or not this form should show this field, a simply yes/no field. This form should draw only the fields that are listed as yes, with the friendly name as the label value.
How do I dynamically draw this form on the fly, depending on the table values, while making sure that there is no extra spacing? Specifically, I can't just turn a field visible or not, I need X/Y coordinates to actually change depending on the field values ("yes" or "no"). Ideally, I would start on one line, continue to draw fields until "x" width was reached, then move to the next line, lather/rinse/repeat, and just continue on until there were no more fields that needed to be drawn.
Hiya guys, I need to create a form that works exactly in the same way as a query works, so there's a criteria text box on the top of the form and a list box displaying all results. I know a query does this but i need a user friendly way of displaying this information.
The search is for customer names and i need to display this information in a user friendly way because my users will need to search the database before deciding whether the customer has been inputted into it or not.
I tried to create this using a query having the criteria as the form's text box value (as an expression) but when I input values into the text box the query does not update unless i reopen the query in edit mode and perform a search manually.
My email is aabaaiaaa@gmail.com if any of you guys have any ideas! Thanks
I have created MS Access forms that are dynamic using VBA. As a part of the VBA I open a from in design mode assign values to form controls, save the form and then open the form in normal mode.this works great as an mdb. However when I created an MDE in 2010 I can not open my dynamic forms. I assume because the VBA wants to first open the form in design mode, assign values and the open in normal mode.My goal is to create a version of my access database that will not allow users to view my code.
Find the link by tacking on the following to this site's primary [URL] .....
It was, essentially, a type-and-show-as-you-go search functionality for an Access Form. It works great, except that I'm experiencing intermittent slowness when using the form. As I type, the characters appear very slowly and experience a significant lag time.
I only have about 70 records so far, so I'm pretty sure the slowness is not being caused by an overabundance of data. At other times, it goes super fast without any lag.
I'm a BA so i'm converting my excel issues list into a database with a front end where i can create new issue records this uses a form that sits on top of the issues_table.
Howver, i have created a table called Projects and a table called Test_Plans
Each issue is logged against a project and a test plan. Lets say ProjectA has TestPlan1 and ProjectB has Testplan2.
On my issues form, i can select ProjectA in the project field.
In the Testplan field i can select EITHER TestPlan1 or Testplan2
Testplan1 is the only valid entry.
How do i
a) Once I have selected ProjectA in the Project field only display Testplan1 in the Testplan field?
or
b) If the user tries to enter testplan2 for it to error?
I've tried creating a query and linking the controlsource field to it but it doesn't like that!
I am using John Big Bootys Dynamic Search Form (built in Access 2003) as it totally met my search requirements, however when typing anything into the search box it is realy slow, taking several seconds for each letter to show in the box.
This has always been a problem now and again, however some of us have upgraded to Office 2010, (although still using the 2003 built Db as we won't move to a 2010 version Db untill March next year) and now the problem is even worse that ever. Everything else seems to work really well except for this search function, however it is the search function that is used more that anything else.
