Changing The Criteria On A Query Via A Form

Aug 25, 2005

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.

View Replies

Changing Criteria In A Query Using A Form

Jun 22, 2006

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.

View 5 Replies View Related

Problem Changing Query Criteria

Jul 17, 2006

I was wondering if anyone could throw some light on a problem I'm having. I have a straightforward report - record source is a query. The query has some selection criteria which requires input at run time. This works fine ! (Why change this you ask ? I'm beginning to ask myself the same question- but hey !)

The problem occurs when I change the criteria in the query (original requested input of a parameter field - the new criteria points at a combo box on an unbound form). If I then run the report, it asks for 'both' sets of criteria (old and new ) !

I've tried copying both the query and the report (thinking that the original criteria are somehow being stored somewhere ), but I'm still getting the problem.

I guess I'm missing something straightforward but I just can't see it.

Cheers

Steve

View 2 Replies View Related

Queries :: Query Changing Criteria On Its Own

Dec 16, 2014

I have a query, and in order for it to run properly, I've set it to look in an unbound combobox on a form to display the necessary data I want, as long as I never close down the form, this is exactly how I want it.However, if I close this form down, and reopen it, I'm asked by messagebox to get the lookup from a different form's combobox, despite saving the correct form's design over and over again, and saving the actual query, more than once, while in design view.when Access changes the criteria, there isn't even a combobox value on the form it changes to.

View 3 Replies View Related

Modules & VBA :: Changing Query Criteria

Jul 22, 2015

I have a query, "adhocComplete" that has criteria ">=[Forms]![frmAdhoc].[Date] And <=[Forms]![frmAdhoc].[Date2]" for the field [30DayDue].Is there a way of removing the criteria from the query by unchecking a checkbox [Check62] on the form [frmAdhoc] and re-adding the criteria if the checkbox is check?

Code:
Dim qdf As DAO.QueryDef
Dim qdfOLD As String
Set qdf = CurrentDb.QueryDefs("Query1")

[code]....

View 8 Replies View Related

Changing The Name Of A Query From A Form

Feb 8, 2005

OK�procedurally I know I am probably doing this wrong, but that is the way the database is set up�so let me try to explain.

Query A has the same fields as Query B. Query B has two fields which are parameter values (i.e. when you run the query, Access prompts you to enter two values). Query A gets these values from a table.

The process I have for getting a report depends upon whether I wish to hard code the values of the report in, or whether I wish to use the information from the table that Query A uses.

Depending upon my choice I end up renaming Query A or Query B to Query C.

All of my subsequent queries utilize the information in Query C to make the necessary calculations and data massaging in order to spit out a Report.

What I would like to do is have a toggle control/radio button (on a form), which will rename Query A to Query C or Query B to Query C, depending upon which choice I click on the toggle button.

My issue is I do not know how to change the name of a Query in VBA. I tried to look up how to do it using [DoCmd.RunCommand acCmdRename], but I couldn�t find any examples which would help and don�t even know if this is how I should go about renaming these queries.

I hope I�ve explained myself correctly� Does anyone have a clue as to how I could do this?

Thanks in advance�
Gary

View 4 Replies View Related

Modules & VBA :: Changing Every Value With Multiple Criteria

Jun 7, 2013

I made this code in excel, and I'd want to get it working in access.

Sub Satunnaisluvut()
Dim OmaAlue As Range
Dim Solu As Range
Set OmaAlue = Range("A1:A5")
For Each Solu In OmaAlue
If Solu.Value = "aa" Then
Solu.Value = "bb"
End If
Next Solu
End Sub

How do i set the wanted field? And how about the loop?

View 3 Replies View Related

Changing The Subform/query On A Form

Jan 13, 2005

Hi,

I have a form that has a query on it. It acts as a subform. I would like for this query to be able to switch depending on what employee I've got selected on the form. I've tried playing around with the sourceobject property:

Dim name As Variant

name = Forms![Time Input Form]!Employee
Forms![Time Input Form]!Table.SourceObject = name

, but it keeps telling me
"Run-time error '3011':
The Microsoft Jet database engine could not find the object
~sq_cTime Input Form~sq_cTable'. Make sure the object exists and that you spell its name and the path name correctly."

The names directing to the subform are absolutely correct though. I'm just not sure if this is the right property to use.

Anyone have any ideas? Thanks. =)

View 1 Replies View Related

Query Criteria From A Form

Feb 28, 2008

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?

View 3 Replies View Related

Use 1 Query For Two Different Criteria In A Form?

Nov 10, 2006

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.

View 2 Replies View Related

Get Query Criteria From Form.

Aug 14, 2007

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

View 4 Replies View Related

Query Criteria From A Form...IsNull?

Oct 19, 2005

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];

View 2 Replies View Related

Query Criteria From Combo Box On Form

Dec 21, 2005

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?

View 1 Replies View Related

Using A Form To Enter Criteria For A Query

Oct 29, 2006

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

View 14 Replies View Related

Form Field As Query Criteria

Jul 17, 2005

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

View 3 Replies View Related

Form Input To Query Criteria

Nov 4, 2005

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

View 1 Replies View Related

Multiple Criteria In Query By Form

Feb 8, 2006

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!

View 2 Replies View Related

How To Use A Form To Collect Criteria For A Query?

Sep 4, 2006

'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

View 2 Replies View Related

Using Checkbox On A Form For Query Criteria

May 15, 2006

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.

View 5 Replies View Related

Query Criteria From Form Controls

Dec 1, 2006

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???

View 1 Replies View Related

Form/Report/Query Aka How To Change Criteria Value

Feb 14, 2006

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:

View 8 Replies View Related

Query Criteria If On Form Field Limit, Otherwise Do Nothing?

Apr 26, 2006

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...?

View 2 Replies View Related

Input Criteria Into Union Query From A Form

May 31, 2006

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

View 1 Replies View Related

Setting Query Search Criteria From A Form

Oct 30, 2006

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

View 1 Replies View Related

Select Multiple Criteria For Query On A Form

Oct 14, 2005

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.

View 2 Replies View Related

Form To Input Query Criteria Between Dates

Jan 30, 2006

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)

View 1 Replies View Related