I have a macro that runs 2 queries, they both have criteria that needs to be passed over. how can i do this? I currently have it prompt but how do i get it to pull from the form the macro trigger is in? I also have a report that runs from a query in that macro also. i dont' want the user to have to enter anything since the critereia is in the form.
thanx
I have a form I am using to either select an Equipment ID or pull all records if no id is selected. My code in the query looks like the following:
([Forms]![frmWOCommentRpt]![cboEquipID]) Or Like "**"
When I run this, however, it only does the Or part. If I select an equipment ID on the form, it doesn't just pull that. I'm sure this is a simple fix, but I can't see it. Any ideas?
Hi,
I have a form that has 4 different search options (all of them are independent of each other).
I'd like to use 1 query to pull the results without having a write 4 different queries for each criteria in the form.
Any ideas r welcome. Thanks.
Hi
Not sure if this is in the right forum. :confused:
But here goes, my question is;
Is it possible to update the criteria by using a form?
What we are looking to accomplish, is a report that prints out labels in relation to the customers that are chosen in the query. What we want the user to be able to enter multiple Company ID's on this form, which are then picked up by the query and the companies name and address printed out via the report.
The printing out of the report from the form we have done.
The below SQL gets it's criteria from a form but if the form has no Date1 and Date2 values I would like it to return all records.
I could not tinker it into submission. :confused:
Any pointers would be great.
SELECT [Central Western Region Database].[Date of Term], [Central Western Region Database].[Agent Number], [Central Western Region Database].[First Name], [Central Western Region Database].[Last Name], [Central Western Region Database].[District Name]
FROM [Central Western Region Database]
WHERE ((([Central Western Region Database].[Date of Term]) Between [Forms]![Compliance Lair Reporting]![Date1] And [Forms]![Compliance Lair Reporting]![Date2]) AND (([Central Western Region Database].Status)="inactive") AND (([Central Western Region Database].[Employee Type])="agent"))
ORDER BY [Central Western Region Database].[Date of Term];
I can't figure out what I am doing wrong... I want the query criteria to be defined by a combo box in the form. The user will select the criteria on the combo box, then click a button to run the query, however the query keeps coming up blank.
[Forms]![UserReview]![Staff] is what I have entered in the criteria for the field in the query. If I simply type in "casbds1" as the criteria, which is one of the choices in the combo box, it returns with the data I want, but I just can't get it to work with the combo box. Any ideas what I am doing wrong?
Hello,
I am working on a database which has many queries that count different statistics of clients (race,gender,ect) during specific date ranges. I would like to set up a form that you could go in and enter a date range into a text box and it would update the criteria (date range) for each of the queries. How would this be possible? I have looked at some past threads similar to this but they didn't help.
Any help will be greatly appreciated.
Hi,
I want the user to be able to pick from a list the criteria that they can put in when prompted by a query.
At the moment, when you run the query, I have it set up so it prompts for the criteria: [Enter Section:] - so the user has to type in the section.
I want them to be able to pick the section from a drop down list.
I remember vaguely having to set up a form to do this....???
Thanks!
Maria:o
Hi All
Ok I have a simple problem. I want to entry value of a field as criteria of a query. Ie. I have a field on a form which is EmplyeeCategores. It's simple to use this as a a filter in a query by simply putting Forms![frmMailer2]![EmployeeCategories] in the criteria of the EmployeeCategories column in the query if there was only 1 criteria.
What I mean is, if the value of the field in form was A1 for example, it'll work and the query will bring up all emplyees who are categorised as A1. But I want users to be able to put multiple codes in the form, such as A1 or A2 or C4, and the query to bring up all the employees that fit into all 3 of these categories,
I hope this makes sense. Any help will be much appreciated.
Many thanks
Oz
Hi
This is simple im sure but i am a thicky
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
thanks
thicky ste
Hello all,
I have a form that feeds a query with information, in my query I have this:
[Company] Like [Forms]![QBF_Form]![Sales] & "*" Or [Forms]![QBF_Form]![Sales] Is Null
When I fill in a value in the field e.g. "data*"the query brings up all information with the word "data" in it.
However, when I fill in something like "data* or vent*" I don't get any query results.
How do I change the query expression above so I can search for multiple criteria in one field using "or"?
Thanks a lot for your help!
'lo all,
I'd like to use a form to get user input on BeforeDate and AfterDate for a query with criteria
<[BeforeDate] AND >[AfterDate]
I must be missing the wood for the trees- how hard can it be?
Thanks for pointing out the glaringly bleedin' obvious,
Alex
Hey all this is my first post so thanks in advance for any help you can give me.
I am trying to use multiple checkboxes on a form to try and make a select query, when the box is checked the data is queried when unchecked it is not, sounds simple enough, here is my problem.
I set the query criteria with an expression like this:
[Forms]![frmMainLookup]![Check Box Alarm Number]="-1"
As long as the checkbos is checked everything seems to work fine.
If I uncheck the checkbox then none of the data is shows up.
I still get the columns to show up just no data.
I am sure there is something simple I am missing but the Force is not with me today.
Thanks again for any help.
How do I use the current value in a control on a form as the value for a criteria in a select query and for the update value in an update query? I must be messing up the syntax somewhere. I have written a macro to run the query. The button is located on the form so I would like to be able to click on it when the values I want to use for criteria are showing on the form.
I currently have the criteria is entered as
[Forms]![xtestupdateqry]![Licensee]
where [xtestupdateqry] is name of the form and [Licensee] is the name of the control I want to draw the current value from. When I run the macro I get a message saying I am about to update 0 fields. I must have my criteria wrong???
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.
Suggestions welcomed.
Gunner...:confused:
I tried to word the title as accurately as possible.
What I'm trying to build is a Form where they can select data in different fields to limit (filter) the list. In my query criteria I want to read the form, if there's a value use THAT in the criteria (for that field) otherwise ignore it.
I've tried isnulls etc. If I put a value (like City) it properly returns all the records with that city, however if I put nothing the "ISNULL" returns a value that the criteria doesn't match i.e. I get nothing.
There must be a way to tell a query to NOT use any criteria if an object (form!field) is blank...?
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
Looking to be pointed in the corect direction,
having trouble using a text box on a form to set the search criteria for a particular field within a query.
Ideally i would like to enter the criteria in a textr box then click a button which sets the criteria and opens the query results in a report,
I have designed the query but cant which works if you go into the design and enter the criteria. the problem lies with getting the text box on teh form to set teh criteria.
If i use the expression builder to set the criteria to the same value of the "text" within the "text box" on said form the following happens,
If i open the query itself it opens a small window and displays the "expression" that i entered in the criteia box, above the data entry. the query does work this way but dont understand why the expression is displayed???
The text box on the form also becomes locked, not allowing data to be entered.
I have tried to link the query direct to the text box.
also tried creating a table which has data entered via form then linking the query criteria to a field in the table.
Could some one point me in the right direction please
many thanks
Cheers
watson
I was searching through here looking for a solution to the following problem:
I am building a Capacity database for work. The requirements require that users be able to sort by numerous criteria (Forecast Date, Portfolio, Market, Bucket, Month), all from a simple form that will spit out the results. Rather than creating unique queries for each combination of criteria(way too many!) or creating the SQL text in VBA, I played around until I came up with the following(which may have been done already, but I can't find it on here, so I'm not claiming to be brilliant...LOL). I have dropdowns on my form for each sort criteria-(Forecast Date, Portfolio, Market, Bucket, Month)
Then I created a query and for each criteria, I put this in:
Like (IIf(IsNull([Forms]![frmWAOFAdjustments]![Bucket]),"*",[Forms]![frmWAOFAdjustments]![Bucket]))
So if the user leaves a dropdown blank, the query simply brings back all the results from that field (Like *).
The text in bold is simply replaced with the dropdown name for each segmentation criteria. Next to each dropdown is a button that clears the dropdown box and requeries the listbox with the results(in case the user doesn't want to remove a criteria. This makes running searches for a specific number of accounts easy for the user and easy on me..lol.
Hopefully this can help someone out with a similar problem. I have learned so much from this forum, I just wanted to give something back. If anyone has any questions on this, just let me know.
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)
I am attempting to create a toggle on a form that would instruct a query which criteria to use.
Specifically they are date criteria. I want to be able to toggle between evaluating on a start basis verse a ship basis.
If the toggle is set to 'Start Date', the query would use the start date as its criteria. If the toggle is set to 'Ship Date" it would use the ship date.
The other complication is that within the query, "Start Date" and "Ship Date" are two separate fields always contained in the query, so how do I write the query so that it only applies criteria to the appropriate field based on the toggle setting?
I have small data base with many tables, one of them a table for equipment wit related details, as below fields.
1-EqipmentID
2-SerialNO
3-Model
4-Coustmer
6-City
I created one normal method query of ACCESS "QueryEQ" on that table , but every time I want to change any criteria I have to edit manual direct to query design .
What I need to do now is to create a form with one combo box showing all "model" and after select any mode I have to press command bottom to change the criteria of the model of the existing query "QueryEQ" and run it and show the result in a subform in same form.
I am working on a warehouse database and i have run into a small problem. I am using access 2010 and 2007 depending on location.
I have attached a picture of my database relation ship so you can see where i am coming from. What I am trying to do is make a main form called products and use that as the sours of the information that my queries look for and then have linked pages to my main form showing the results of that.
Example of the above would be as follows
Product on main form lets say tennis ball red then on my link forms show stock plus location and quantity
On another linked form product on main form and orders for that product and qty
I'm trying set the query criteria from a textbox on a form. It's a Status field in the query. 1 = Open, 2 = Closed. If I set the criteria to "1" it shows all open, "2" and it shows all closed and "1" or "2" it shows all. The problem I'm having is setting these in the form. I've set the query to pull the value from the form. I can get the Open or Closed to work but not the All. My textbox shows exactly how the criteria should read "1" or "2" but doesn't show any results.
View 3 Replies View RelatedI have several (about 10-15) queries that I have designed to run monthly reports. Various pieces of the criteria for each query need to change regularly. For example, in design view, I currently have a 'Where' field for each of our eight products. Where 1= include the product in the query analysis. Where 0= do not include the product in the query analysis. We would like to have a form that has entry boxes for each product where the person doing the analysis could type '1' or '0' and then the query could update the criteria entered before running.
We also need dates to update via the form. I currently have [Current Period End Date] and [Prior Period End Date] pop up boxes on each query that allow the person running them to enter their desired dates. But again, my boss wants to limit their involvement with the actual queries. Plus, you end up entering the dates for all 10-15 queries, which is a hassle. We would like to have entry boxes for the desired dates in the form and have the queries pull date criteria from the form.
If we can pull query criteria directly from a form, is it possible to create a table from a form and then pull the query criteria from the table?
I've got a continuous form based on a query.Each of the fields have a search box below it (in the footer) which should ideally filter the query.I'm starting with the FirstName field.In the form's query, I've set the criteria to the following for the FirstName field:
Code:
Like "*" & [Forms]![frmStudentDetails]![txtSearchFirstName] & "*"
frmStudentDetails is the form name.
txtSearchFirstName is the search box's name (in the footer).
The AfterUpdate event for txtSearchFirstName is:
Code:
Me.Requery
But, it doesn't work; when I switch to form view, it displays an error. The Microsoft Access database engine does not recognize '[Forms]![frmStudent Details]![txtSearchFirstName]' as a valid field name or expression.