Chart Based On Form Based Parameter Query
Mar 22, 2006
Hi all,
stuck on this, not sure if it can be resolved....
I have a parameter query for which the parameter is based on a form field entry (i wanted to avoid the dialog box popping up).
No problems with this....however, I want to create a chart based on the same query however, i get the following error message because it does not recognise the query parameter.
Error Message: The Microsoft Jet database engine does not recognise '[Forms]![frmChooseDTDate]!DateOccured' as a valid field name or expression.
Can this be resolved?
Help most appreciated!
regards to all
K
View Replies
ADVERTISEMENT
Mar 20, 2013
I have a form which uses a parameter based query to present an individual senior doctor with a list of names for of individual juniors to provide an assessment report on. When the first form opens the user enters their RespondentID.
Once senior has decided which names to comment on I have another form which has the questions to be answered which is opened by clicking a button on the first form.
How can I pass a parameter from the first form to the second so that only the records relevant to that senior doctor are displayed? The underlying table has 60 senior doctors and 20 junior doctors. The senior doctor is identified by the field RespondentID in the first form. I've tried putting a WhereClause in the FormOpen command but I still get a dialogue box asking for the parameter RespondentID when the second form is opened.
View 2 Replies
View Related
Nov 14, 2012
My form-based search mechanism uses controls to set the parameters for the query data source.
I have one field call quantity in stock. I could you a Between and And method to allow the end user to input the stock quantity they want.
HOWEVER, i would love it for the user to first select the Comparison Operator (e.g. > , <, >=, <=) from a combo box and then in an adjacent text box, enter the quantity.
The expression i entered in the query goes something like this.... Forms![frmSearch]![cboRange] & [Forms]![frmSearch]![txtQuantity]
When i try and run this, i get the message "THe expression is too complex to be evaluated".
View 5 Replies
View Related
Aug 12, 2015
I have a graph chart and my row source type is a Query. So I retrieved data from the Query that I built.
However , I want to get the data from the uploaded excel file.
I have a form that will prompt user in the beginning to upload the excel file and replaced the old table in Access- "CrossSystemData"
Basically , I want to pull data from Excel file , Run the query(Data from excel file) then excute to "display a chart based on my query" ... Is it possible ?
Query SQL: "PolyWrongRegInsCount"
Code:
SELECT GROUPING.INSTITUTION, Count(*) AS NO_OF_GROUP
FROM (SELECT DISTINCT CATEGORY, GROUP_NO, INSTITUTION, IIF(Left(INSTITUTION,1)="P","POLYCLINIC","HOSPITAL") AS INS_TYPE FROM CrossSystemData WHERE INSTITUTION NOT LIKE "*UNKNOWN*") AS GROUPING
WHERE (((GROUPING.CATEGORY)="WRONG REG") AND ((GROUPING.INS_TYPE)="POLYCLINIC"))
GROUP BY GROUPING.INSTITUTION;
My Chart SQL :
Code:
SELECT PolyWrongRegInsCount.[INSTITUTION], PolyWrongRegInsCount.[NO_OF_GROUP]
FROM PolyWrongRegInsCount;
View 3 Replies
View Related
Mar 20, 2013
I have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.
View 3 Replies
View Related
Jul 1, 2006
I have a listbox on a form and I want to refer to the second column and use this value in a parameter query.
I'm using the syntax
Forms!formname!listboxname.Column(1)
but it doesn't work. I have attached the problem.
View 3 Replies
View Related
May 16, 2013
Within a query, I'd like to reference another query field based on a date specified as a parameter.
In my query, there are fields for each month: [January],[February], etc.
I have a field titled [Current Month], based on the parameter [As Of Date]. So if when running the query, the parameter pops up and I type 5/6/2013, it knows that the month is May. I know how to return May in the current month field (format([As Of Date],"MMMM"). But how to I return the value that is in the May column?
View 3 Replies
View Related
Sep 22, 2014
I'm trying to export a report based on a query which has a parameter.
this parameter has to come from the recordset.
now if i run the procedure it asks me for the parameter.
How do i get it to take the parameter from the recordset?
it should take the column 'Company#' from the recordset
here is what i have now:
Public Function mOutstandingInvoices2()
On Error GoTo mOutstandingInvoices2_Err
Dim rst As Recordset
DoCmd.SetWarnings False
'On Error GoTo BREAKOUT
[code]....
View 10 Replies
View Related
Nov 19, 2012
i'm creating a search form giving the end user a range of controls to use when filtering/searching data. See the image.But, i think my range search (using the textbox) to put in a lower and upper limit...is preventing this from working. In fact, when i put data into all the controls, no data pops up in my subform.
My query data source can also be seen...showing you how i've handled teh null entries. (i need to put in a null 'handler' for the two textboxes?)
View 2 Replies
View Related
Jul 31, 2013
I have a parameter query that contains information on a list of people and contains 3 checkboxes: alumni, parent, business
In this query, I am trying to use parameters to filter the results based on these three fields i.e.
true, false, true would return all records where either alumni, business or both are true, and parent can be either true or false.
false, true, false would return all records where only parent is true, and the other fields do not matter.
View 2 Replies
View Related
Feb 23, 2014
When creating a query I have set up a parameter query as instructed (in this case searching for a name) but when I run the query it returns the error 'this expression is typed incorrectly etc.'
I have worked out that this must be due to the fact that the names are not typed in by a front-end user but need to be chosen from a dropdown list (which i have stored in a separate table) - So, the query seems to fail based on that.
View 10 Replies
View Related
Mar 5, 2014
I try to develop an access 2010 Forms that contains 1 or 2 charts. So my problem is, in my form I have 1 combobox and 5 unbound textbox. Inside Event OnChange combobox, there is function DCount that will setup value to the 5 unbound textbox. Until this point, the 5 unbound textbox is have its value.
Now I would like to create a chart that value are based on the 5 unbound text. How can i accomplish this ? i see in many articles, a chart row source is link to a cross tab query.
View 4 Replies
View Related
Dec 22, 2014
I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
View 14 Replies
View Related
Jun 8, 2005
Hi,
Based upon the parameter given i want to run different queries
i need to do the below in one query.
PARAMETERS Proj Text( 30 );
If proj="All"
Select * from TbProj
else
Select * from TbProj where Proj_ID=proj;
I want to build an access query like above.
I don't know whether it supports using IF statements.
how to do the above.
Thanks.
View 1 Replies
View Related
Dec 12, 2014
I have a totals query that shows results in a chart. It takes a parameter to limit results, by a combobox in a form.
Parameter in the query includes the OR "*" expression, in case someone wants to get the results unfiltered.
The Combobox in the form, has an AfteUpdate event that opens the chart (form) every time its value changes, by the [DoCmd.OpenForm "ChartForm" , acNormal] expression.
I don't know how to make it open the ChartForm when no parameter is selected in the combobox.
View 13 Replies
View Related
Jul 2, 2015
I have to print a label quickly every time that the product hit the warehouse. The label has been created as a report linked to the query that will provide the info to the report. In order to make this report printing as quick as possible the idea is to scan the sample id from the product and once the label is printed scan the next sample and an on.
I'm not an expert on VBA but I have created the following scrip but the reports doesn't pop up.
Here is the code:
Dim SampleID As String
SampleID = InputBox("Enter Sample ID")
If SampleID > 0 Then
DoCmd.OpenReport "rptGRM_QuickPrintLabelDymo", acViewPreview, , "[Sample]=" & SampleID
Else
DoCmd.Close
End If
End Sub
View 8 Replies
View Related
Jun 15, 2015
I am editing a database that provides the option of creating custom reports, where the user can input a date range of their choice and receive aggregate data for that time frame. Although all of the numbers in the report are correct, I am having trouble with a chart that I inserted into the report.
Specifically, if the date range requested spans 2 calendar years (i.e. April 2014 through January 2015), the data for January 2015 appears at the beginning of the year (so the chart x-axis is for Jan through Dec, and the Jan 2015 data is showing up in Jan (as if it was 2014, not the end of the given range in 2015). When I try with smaller time frames within a calendar year, it adjusts just fine (i.e. shrinking the window so just March-May is displayed on the graph).
How to adjust the axis so that it properly records the data range- so that it would start the axis with April and end in January, for example?
View 2 Replies
View Related
Jul 31, 2014
I have a Main form, and a subform which lists client details. On the Main form I have an unbound field. I want to be able to type a word into this unbound field and have it display all company names that have this word in them. ie. I type "Ltd" into the unbound field and it displays all companies with "Ltd" in the title.
I have created a query that does exactly this (Like ("*" & [Enter Word] & "*")), it displays a dialog box and I type in "Ltd" and it displays all relevant companies.
I have tried everything I know to make this work when I use the unbound field on the Main form, but I've had no luck.
View 3 Replies
View Related
Mar 14, 2007
I'm working in Access 2003
I have a table of projects done by my company, which includes
-organization name
-project year
-service 1 provided
-service 2 provided
-service 3 provided
-service 4 provided
-project fee
-project consultant 1
-project consultant 2
-project consultant 3
I've created a Query Based Form where you can enter in any combination of search criteria (e.g. org name and btwn 2001-2004) and the button press runs a query that returns a table of projects that match all the criteria you've entered.
I've figured out (I have no formal Access or programming training) what to set the criteria as in the query so you can get info by entering org. name, project year (with start and end dates) and/or project fee (with bottom and top amts).
The issue that I'm having is that I want to be able to enter a consultant name into the Form, and have the query return any projects that have that consultant listed a either project consultant 1,2 or 3 - and the same idea for the service provided. Right now I can only get the query to look in the 'service 1 provided' and 'project consultant 1' columns.
I had a thought that I could run 3 different queries - 1 that will look at org, date and fee, a 2nd that will look at all consultants, and a 3rd that will look at all services - and then run a 4th that would give me a table that only showed projects that appeared in all 3 of the other queries. However I haven't been able to figure this out, nor do I know if it's the best way to achieve my desired result. Right now I have the 4th (using UNION) showing all projects that show up on ANY of the 3 queries, plus the button press makes all 4 query result tables pop up, and I'd like it if only the 4th (final) query produced a visible result.
Any ideas or help would be greatly appreciated. Thanks!
View 3 Replies
View Related
Feb 27, 2008
Hi, can anyone help? I have set up a query that looks at a list box on a form for one of its criteria and then a text box on the form for the user to also specify a date. A command button on the form runs the query. Is there any way for the query to work if the date field is unpopulated (as well as having the option to specify a date)?
Any help gratefully received x
View 5 Replies
View Related
Oct 22, 2005
I have been told that all forms should be based on a query even if you are using all of the fields and not using any criteria. I would like to know what the advantages & disadvantages of this are. Could someone please explain this to me?
View 1 Replies
View Related
Feb 21, 2006
Hello I am new to access development.
I read several of the entries similar to my error:
Cannot add record(s);join key of table 'dboAssets' not in recordset.
The information was previuosly in one record name inventory. This was not normalized so following a books advice, I did. Now i am having a good deal of time putting the information back together. I moved all the information gathered into the inventory table. When I try and create the form using the form wizard it creates a subform and I need it all in one form. I can recreate the nice form, but am not getting what I missed in the join. When I preview the sql view there are lots of fields that are not in my Assets table. This is more complicated than my book explained. Any assistance would be appreciated.
View 1 Replies
View Related
Nov 24, 2006
Hi people,
i have a search form with a list box. it is based on a query. it works fine for a different project but in this project i need to search for either a perfect match for an ID, or a 'like' match for description, or a like match for the customer.
i want the list box to have the
EventID, Description, CustomerName, CustomerSurname, EventDate
i want to choose which information i enter into the unbound boxes to return results in the list box.
What formulas would i need to enter into my query as a criteria so that it works.
Thanks guys, james
View 1 Replies
View Related
Jan 30, 2008
Hi,
I've attached a zip version of my database (which is still in the early stages of development) as I think it will be tricky to explain what I need. I've studied lots of other threads but can't find the answer I'm looking for.
Basically I have a form "Goods-in" which has a subform in continuous view that lets you pick from a product list. I'm trying to limit this combo to products that relate to this supplier only, but (it seems) because the subform is based on a query I can't update it.
I have tried several approaches but got nowehere. At this stage I don't know whether the problem is with
- how I'm linking the parent and child forms
- the SQL of the combo recordsource
- my relationships
- my overall design
- the weather!
- etc.
I would be eternally grateful if someone could take a look and advise me. The items shaded grey on the forms are just my shorthand for what will be invisible in the final version.
I'm going home now and even though I may read replies tonight, I wont be back in the office until 9am.
Thanks in advance!
View 4 Replies
View Related
Jan 31, 2005
First, let me point out that I am a new Access user, so apologies ahead of time
I have a form which has a field called 'Initials'. I want this field on the form to default to whatever was last entered (into the table called 'Main Table'). I first tried setting the default value with a DLast command, which seemed to work for a day or so, but then it returned values from mid-table - very strange
I then set up a query which does return the value of the last Initials entered (cunningly called 'Last intials entered'), but I can't get the field in the form to default to this value - it comes up with #Name?
Can anyone help, or have I not explained myself very well
Cheers
View 1 Replies
View Related
Jun 8, 2005
I don't know how I'm going to explain this but here it goes.
I have designed a query that counts and sums the total field of a table grouped by engineer name, which works perfectly, but on the form I am showing the resluts on I want to show the last five totals inputted into the database as well.
I am sure this must be possible some how but I can't get me head round it, any help would be much appreciated.
View 2 Replies
View Related
