Queries :: Using Input From A Form As Criteria - Error When Running Report / Query
Aug 18, 2015
I have a query that uses the input from a form as criteria, which is then used in a report. The form input is a drop down based on another table. This is a sales pipeline report, and the list is a list of sales people. The report works perfect for all sales people except one. When I run it for the one, I get the following error:
"This expression is is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables".
I DO NOT get the same error when running the query by itself - so assuming there is something in the report causing this. I do have some sum formulas in the report.
Again, no other salespersons selected cause this error -- so I am assuming there is something in the dataset for this person that is causing the error.
I am trying to run a query and display the results in a report (the report side of it is childs play and not a problem). The problem I am having is that I have a search form which should allow the user to search any one of 6 fields (text boxes) or a combination of each.
If the user enters something into a field then that search criteria must match. I wanted to have it so if all fields are left blank then it will show all entries in the database (but it isn't, it shows a blank report). I also wanted it to allow partial completion of boxes.
So for instance if I have 5 customers (Jones, Jonson, Jonus, jimjonkins, Janis) and I type "Jon" into the name field then I would like it to show the first 4 records as they all contain "jon" somewhere in their name but its not, its only allowing exact matches.
I currently have '[forms]![Search_Customer]![Search_Name]'.
I have a customers list that i am always ading to .I need to run a query to see what individual customers have ordered .
I have the query that works great when i manually insert the customers Surname .but i want the combo to do this job .Is it possible ,or should i be doing something different .
How do you allow a user to enter a value in a form; have access set that inputted value to a criteria in a query? Then ill have a button to run the query which i can do
I have a form which lets the user enter a contract ID and then a combo box that has a list of reports which run against whichever ID is entered.
What I want to know if I put the ID's in a table if theres a way to allow the user to select multiple ID's in one go and run the report against them the range rather than 1 at time?
I'm thinking about utilising the check/tick boxes would this be possible? or another way?
Based on information from a earlier thread.... I created a Union query that pulls information from multiple tables and fields.
SELECT AG_B_R1 as Num FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_B_R2 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_B_R3 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_B_R4 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_m_R1 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_m_R2 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_m_R3 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_m_R4 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_e_R1 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_e_R2 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_e_R3 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll] UNION ALL SELECT AG_e_R4 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll];
And then I created another query to get the STDEV of the above query
SELECT StDev([Num]) AS StDev FROM Q_cals_ag_bme_STDEV_Union;
The result will be on a subform on my main page. How do I get my form to input the [roll] automatically and requery the subform, showing my result. Thanks
I have this select query. SELECT DISTINCTROW L160.Date, Avg(L160.Zinc) AS [Avg Of Zinc], Min(L160.Zinc) AS [Min Of Zinc], Max(L160.Zinc) AS [Max Of Zinc], Count(L160.Zinc) AS [Count of Zinc] FROM L160 GROUP BY L160.Date HAVING (((L160.Date)=[Forms]![L-160quarterfrm].[Date])); How do I build a form that would ask the user to input a range of dates for the criteria? Thanks! :o)
Hello, I have 4 input field boxes in a form: "basic hours", "overtime hours", "holiday hours" and "total hours" i would like it so that when the user enters number(s) into the other field(s) the total field will calculate (add togther the other fields). So for example if the user puts "10" in the "basic hours" input field and "5" in the overtime hours" field then the "toal field" will display the number "15". i would like it so that the "total hours" field calculates the total progressively after each number in the other fields is input (ie a running total is displayed). The user will always fill in basic hours but 1 or more of the other fields can be left blank. I think i need to do some VB on the after update procedure for each field entry but not sure what the code is?, any help would be brilliant.
I need to run a query using a list of unique values. I open a new query in design view, pick my table that I want to use, pick the field, but then in Criteria, I need to use a list of values. The list is 62 values long. Any help here would be greatly appreciated. I hope I am explaining myself thoroughly. Thanks, a_brooks
I have a simple report which i open from a navigation form, i have a textbox in the report i wish to use as criteria, on start the report will be blank until i enter a name into the textbox and requery. However im not sure how to link this and how to stop access asking for this information before the report is opened?
I have a form. On the form I have a button to run a report.The query associated with the report selects all records within a unit (field name (Unit) is used as the selection criteria).Rather than type in the unit name when the report is run, I want to select the unit that is currently shown on the form.
I maintain a grade book application that uses many queries whose results are determined by "school year". Most of these are reports and I have a combo box on the Print form for that allows the user to select the school year. The criteria field of the several queries derived by school year is:
[Forms]![Main Navigation]![Print Form]![SchoolYear] SchoolYear being the combo box control.
They work fine.
I have now added a function to export data to Excel. This is done in a VBA module and I am using a query to select data for the record set I use to write to Excel:
Set objRst = Application.CurrentDb.OpenRecordset(strQueryName)
When I hard code the school year in the query criteria field (i.e. "2012-2013") the process works fine, but if I revert the query to point to the print form field as above, I get an empty recordset.
The Excel export is executed from a control on the Print Form, so the form is open and the combo has data showing, just as it is when a report is run whose data is derived from a query.
When I execute the query from the VBA module, the query is not getting the school year selected on the Print form passed to it properly.
I have a table which holds information on audits that have been carried out on staff member's actions. The 'Supervisor' field is populated via a combo box which is linked to a separate table (tblSupervisors).
I am now trying to build a query to allow me to extract all audits that have been carried out on a specific supervisor - rather than the criteria to be [Enter Supervisor Name] and allowing text entry, I thought it would be better to have form that pops up with a combo box that is used to select the supervisor (from tblSupervisors);
So far:
- Form "Supervisor_Select" is created, and has a combo box that looks up from tblSupervisors
- Macros as specified in the instructions are created (Open Dialog, Close Dialog, OK and Cancel)
- Query is done, all bar the criteria expression on the desired field.
- Module is created as described in the instructions, and is called "Supervisor_Select"
I have tried putting the following in the criteria;
[Forms]![Supervisor_Select]![cboSupervisor], however I think I am missing the bit where the query opens the "Supervisor_Select" form?? Will this only work from a button where the on click event runs the 'Open Dialog' macro and then runs the query?
The problem is lately; while testing I am running multiple queries in a routine. When it fails; its hard to identify which query has the problem. So I hit control break; debug and try to find it. After I fix it; I debug and reset; i get this continuing hourglass thing in the form of a spinning circle until I close and reopen the database. I think I need better error handling but not something really complicated because I need to put it in quite a few routines throughout the database.
I have built a database with only my department in mind that tracks three types of documents; Which works fine. Now some of my fellow department heads desire to use what I have built.
This is not a problem as their data structure is the same. The difficulty lies in changing the 30 queries. In the queries I hard coded my department number in the receiver (department field) criteria.
Additionally, my HQ is requesting me to perform some analysis on the other department's data. So other than manually changing the department number each time; Can I use a form or report to modify these 30 queries? I attempted to use a combo box but it would not hold the value when I closed the form.
I saw this thread today, http://www.access-programmers.co.uk/forums/showthread.php?t=102036 , but am not sure it will do what I want.
I want to create a form that allows a user to enter criteria that will be passed to an existing make table query. suggestions on a user friendly book on Access 2010 programming, I'd be really interested. I'd like to be able to do more with Access 2010.
I am running a query that keeps asking for me to enter data for some fields before it opens the query, which I don't. When the query opens, the data in the fields is there.? Why? This is what is written in the field (query):
Total Jumbos: nz([pathjbo50],0)+nz([esjbo50],0)+nz([f1jbo50],0)
The quote "Total Jumbos:" is my label. The rest is my expression. This setup works on a few other columns I entered but this one (and a few others) don't. Any suggestions would be appreciated.
FYI: The query information is gathered from a table populated by a form. The label "Total Jumbos" is not on the original table but I have other labels in the same query that are also not on the original table that do work.
Please except apologies if I sound a bit ignorant with this but I'm complete novice with Access...
Basically I have a table with the fields Name - Date - Location I need to extract info from the table based on the location field.
i.e. return Name if a specified location exists and another specified location does not. This was real pain in itself and in the end I had to run two seperate queries which returned NAmes where each of the chosen locations exist. I've then created an additional query which compares the two sub-queries and iliminates Names that appear in sub query 2.
I do hope this make's sense I'm starting to confuse myself.....
Anyway, my problem is with the date field... I have to specify a date in order to get the correct info as the results may change on a daily basis... i.e. somebody may use both locations one day, but just the one location the next. However the eventual report which I need to generate is based on criteria over the previous 7 days, although if I use this criteria in my query in completely messes up the results.
So, what I was hoping that I could do is somehow automate my query to run 7 times (once for each of the previous 7 days) and then to combine the results of the 7 queries into a single query or report...
Failing that, could anybody think of any simple way of achieving this... or will be a case of having settle for a seperate report for each day?
Any help would be hugely appreciated... once again apologies for the explaination... It porobably makes no sense at all.
I have an MS Access 2003 front-end (queries, forms, reports) connected to a SQL server back-end database (SQL Server 2000).
When I try to run an append query against any of my SQL tables with a unique identifier, I get the error...
"Explicit value must be specified for Identity Column it table 'table1' when IDENTITY_INSERT is set to ON (#545)"
I am trying to create a duplicate record process for my users, by appending data using append queries to the same table. I used the MS Access built in wizard to duplicate the main record on the form, but was going to have to use append query code to duplicate the sub-form records.
Is there anyway around this error, or am I stuck as far as writing code to run this append query on the fly?
I am modifying some queries in a DB which is only 411,000 KB. It still has plenty of room left before reaching 2GB. When I go through and test the queries, some run fine with no issues.Then I get to one of the last queries and get this error: "The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage on the disk to store the query itself."
I still have about 1.5 GB of RAM available when this query is running, so it doesn't appear to be a RAM issue, and the DB still has plenty of room left to store the result set of this query.
FTA # Date Attended Last name First name assigned person......
Each week I need to generate a report that shows the people that attended for that week, grouped by the assigned person. I only want to show the data for a specific "date attended" (i.e. that days date). I figured I first need to generate a query that only returns the data for that specific date in the "date attended" column. I did that but it is manuel by using the criteria field.
Is there an easy way to type in the date that I want the query to use as the criteria?
I've been reading up quite intensively on using QueryDef's collection to try to pass a Parameter to a Query in order to then Open a Report. But can't seem to get it to work. I've been trying to write an SQL statement that the VB will accept in code but never got it to work. Depite copy/pasting the SQL from the Query Access creates. regardless.. I really need some help with this one. Thanks. Procedure follows. Get error that Report not found... The name is correct... I just want to pass the integer (index) from the ListBox into the Query then Open the Report... Thats all...
Private Sub previewReport_Click() On Error GoTo Err_previewReport_Click Dim intSelectMonth As Integer 'The Index of the Item Selected in Form List Box Dim db As Database Dim smReport As Report Dim smQuery As QueryDef Dim month As Parameter
Set db = CurrentDb Set smQuery = QueryDef![GetReviewsMonthQuery] smQuery.Parameters![rvMonth] = 9 'Using 9 as September just for Testing Set smReport = Reports![Review Schedule by Month/Analyst]
DoCmd.OpenReport smReport, acPreview
Exit_previewReport_Click: Exit Sub Err_previewReport_Click: MsgBox Err.Description Resume Exit_previewReport_Click
How do I get a query to run upon opening a report. My report has a few fields from a query that once the user inputs information the query is obviously outdated. Since it needs to be as simplistic and user friendly as possible I won't ever have them run the queries themselves so I'm trying to have them run to be updated for the report when the user goes to open the report.
I am trying to get my VBA code to dump a query once the user pushes a button. I have the following code to call up the Excel app.
Code:
Option Compare Database Private Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long
[code]....
The qry_PP_Errors_Union is a Union query. In this query there is a date field. I would like to be able to to use that date field as a parameter. So I have written this VBA to prompt the user for a Begin Date and an End Date.
Now the part that I am missing is that I am not sure how to make the "strBegindate" and "strEnddate" the criteria for the union query.
The following is the SQL for my union query.
Code: SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, UICError AS Error, "Update and Internal Correspondence" AS Category FROM qry_PP_UIC_Error UNION SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, BIDError, "Bids" FROM qry_PP_Bid_Error
[Code] ....
Without the criteria, my code works for dumping everything out into Excel. However, dumping all the data results in a 7 mb Excel file that requires manual deletion of the information that is not pertinent.
