Hello, I am trying to change some query critera with a script.
Basically I have a drop down list which I select and once selected it requeries a subform. I would like to change one of my query parameters, specifically the criteria in a column.
To change query "MyQuery" column "Model" criteria, I've tried:
Code:!Queries!MyQuery!Model.Criteria = "Ultra5"
but that doesn't work. Is that even close to being right?
Thanks in advance!
I have a database consisting of one table with 5 columns, called Name, Zip Code, Model, Serial #, and Reference #
I made a form with 5 text boxes (generically named Field 1 through 5), and then I built a query. I put all five of the table's fields in the query. In the criteria fields of those five columns, I have
Criteria: Like "*" & [Forms]![Search_form]![Field1] & "*"
Criteria: Like "*" & [Forms]![Search_form]![Field2] & "*"
Criteria: Like "*" & [Forms]![Search_form]![Field3] & "*"
Criteria: Like "*" & [Forms]![Search_form]![Field4] & "*"
Criteria: Like "*" & [Forms]![Search_form]![Field5] & "*"
However, here in lies the problem:
Some of the records don't have a reference field included, and others don't have serial numbers, and etc. Basically, some records have blank fields.
So to find all of "John"s records, we would put John in Field1, and then run the query.
The first criteria is now looking for *John*, which is correct (it might find Jack John, Johnny, John John, or anything else with j o h n in it).
The rest of the criteria are now looking for **.
However, what about the records with blank fields?
John ; _____ ; SDMS71 ; 1231234 ; REF9001 will NOT make it into the query, because that blank field for some reason doesn't meet the ** wildcard!
how can I accomodate this "blank field" problem? I want those records with blank fields to still be included in the query, because they still belong to "John", and that's what i wanted to search for! :(
I have a database with 20 queries designed specifically for my department (SAM). A friend would like to use my database i.e. queries, reports, etc., but her department is named different (PAM).
The the field names in the each of our imported 'data' is the same, EXCEPT, each record in her department data field has "PAM" mine "SAM". However, her department is not part of my data import nor mine of hers nor will they ever be.
How can I, except manually, change every instance of "SAM" to "PAM" in the 20 queries?
Thanks
Gunner...:confused:
Hi,
The query for my form has a boolean field with ' like "No" ' in the criteria. I want users to be able to use a command button to change this to ' like "Yes" ' then requery the form.
Basically, what I want to do is toggle between current records ("No" in the boolean) and Archived Records ("Yes" in the boolean).
Any ideas?
Regards,
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:
Hi there,
I have a search form with a text box (unassigned at the moment). The search I want to conduct, filters the product number by the users search string with the use of * as a wildcard option.
I also have a Query with all the product numbers listed, no criteria set. How can I link the the search string in the search form to the query so when the search button is pressed, it will filter the results.
Any help appreciated.
I have managed to build a macro that will run a query I have in Access but what I would like to do is be able to change some of the query criteria using the macro script or any other way possible.
Is this possible?
Thanks
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 use expression on the query field, but after I run it, the field change its format from number to text, how to reformat on the query criteria to change the text to integer format?
View 8 Replies View RelatedIs it possible to run a query and have the background color of the query field be highlighted if a certain criteria is met?
I'd like to use the expresion builder if possible.
For example if the values in field A and field B do not match, then field C should be a yellow background.
If this is possible, how is it done?
I have an access 2007 database connect to sql server 2008.I am running a pass though query to search between two dates (this query has been fine for years)
If I now run any search using parameters from 26th March 2014 to date - the query takes 10+ minutes to run.If I then change the date to 25th March 2014 to date - it runs in a nano second.I have not changed the back tables and I have not changed the format the data is saved in.
You will see it is a simple problem if you know what your are doing.
When entering the joining date I would like the Status to change automatically to Member and when the renewal date is reached I would like the status to automatically change to Renewal but I am unable to work out a formula for this.
Many Thanks,
Fredfox.
Hello to all the xperts out there,
I have a query with this criteria for Date field:
Where (([tblList].Date)>=#11/1/2005# And ([tblList].Date)<#12/1/2005#))
Is there any quick way, besides creating a form and run the query based on it, to have this criteria updated itself when it's a new month? I've gotten tired of changing it every month.
Thanks in advance
Is there anyway to change the criteria in a field without going into design view I bascially need to be able to type in a particular item in a certain field in a query and it return all the results for that one.
The database has over 160000 records and when I try using a form it takes forever.
Good afternoon,
I need to use a selection from a drop down list to change the criteria in a query as opposed to typing text in a parameter box. This is to allow the user to choose from a list and then click on a command button to move to the next query. There are 20,000 records in the table. The series of queries will take the user to 1 or 2 records in 3 or 4 mouse clicks.
Thanks
I have a table named z_ResltsSampleCountMonthly. In that table I have fields SampleSize, Month and LOB.
I have a report that I need to use a Dlookup in order to pull the SampleSize by LOB into my report. However, my report has three columns that change based on the start and end dates for the report. It shows current month plus the two prior months to show a trend.
So I have each column heading update automatically based on the dates the user enters into the start and end date on the main menu form.
If they select 12/1/14 to 12/31/14 the three columns heading would update to October 2014, November 2014, and December 2014. In the table I have the data for all three months so when I use this formula it works but it's putting September 2014 data under October 2014 so I need to have multiple criteria; one being the LOB and the other being the column heading which is equal to the field Month in the table.
Code:
=DLookUp("[SampleSize]","z_ResultsSampleCountMonthly","[LOB] = ""CMES""")
This is what I tried to do to add the second criteria to pull based on text59 being equal to the Month field in the table but it's not working:
Code:
=DLookUp("[SampleSize]","z_ResultsSampleCountMonthly","[LOB] ="CMES" And [Month] = ' " & [Text59] & " ' ")
I am using a DLookup to default a value in my TextBox. This text box has an event attached to it that updates a table when a different value is entered.
I am having difficulty in getting the default value to reappear when I change my criteria.
For instance Manufacturer is Ford
Colour is default Black
I Change the colour to red
now I change Manufacturer to Audi
I need Default colour to go back to Black
The Lookup Table says Black its just that the default value only seems to run once, first time in and never again unless i exit the database and reopen it.
I have tried adding
Form.Repaint
form.Refresh
Form.Requery
to my afterupdate query
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
Any help would gratefully be appreciated. Thanks
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
StoreSubjectSalesSales %
1516Fiction56431.5-24.15%
1516Audio Unabridged1650.8-231.04%
1516History / Military History10081.1-29.99%
1516Role Playing / Graphic Novels14773.9-20.27%
1516Mystery13152.6-19.84%
1516Audio Abridged1785.9-141.84%
1516SciFi / Fantasy27535.3-7.93%
1516Juv Audio/Video1580.6-100.13%
1516Biography8103.6-15.89%
1516Sports7910.8-15.64%
1516Current Affairs / Law8141.9-14.34%
1516Reference7183-16.22%
1516Juv Non-Bk4585.9-25.02%
1516Science / Tech2961.4-33.98%
1516Movies / TV / Music / Dance3395.3-29.46%
1872Fiction307344.3-7.49%
1872Business134307.5-13.48%
1872Psych / Self Improvement100650.4-10.05%
1872Audio Unabridged29165.9-27.32%
1872Cookbooks57463.3-13.56%
1872Computers59235.7-12.37%
1872Regional59883.4-11.22%
1872Health & Fitness64713.8-10.29%
1872Maps19358.4-27.66%
1872Current Affairs / Law47927.1-11.08%
1872Travel Foreign42583.7-12.27%
1872Religion / Bibles80255.6-6.07%
1872SciFi / Fantasy67641.4-6.49%
1872Study Aids / Notes38299-11.24%
1872Games41745.1-9.79%
In my database I have 2 tables that contains values I would like to add if a check box is marked.
Table 1 columns: JobID;JobNumber; Product; Qty.
Table 2 columns: COrderID, JobNumber, QtyChanged, Accepted(Accepted has the check box).Also, there are multiple records for each job number based on how many change orders will be.
I would like to change the value of Qty on Table1 with the values of QtyChanged from Table2 only if the checkbox is marked.
I am creating a database that is going to import excel files once a week. Most of the data will be the same every week, but some may change slightly or new records may be added. I need to create a query that will show all the changed/new records that are imported from the excel file. i would prefer to use as little code as possible if at all. I am pretty much a novice when it comes to queries so any suggestions would be appreciated.
View 1 Replies View RelatedHi
How is it possible to change row in query column title?
I have query which look like this:
[number] [start time] [end time]
1224345 12.6.2007 13.6.2007
I want that my query should look like this:
[number] [start [end
time] time]
1224345 12.6.2007 13.6.2007
I'm a beginner Access user and would love some advice.
I have two queries:
1. A query called: Scores
This query tallys the information (in numbers) gathered from different fields from a table in a raw score.
2. A query called: Results
This query should then convert the raw score from a field in the query Scores and display a sten score (1-10) in the query Results depending on some pre defined values. For example: A raw score of 1-21 should return the sten value 1. From 22-38 should return the value 2 and so on and so forth.
Somebody remarked that I should use Select Case to do this, but I cannot find an example of how to use that that is easily
tranferable to my circumstances.
Thanks!
I am trying to change the button color on a subform if a related form data changes.Main form is products with a continuous subform with serial numbers of products i.e, serial number, location, price and a button to add addtional issues if there are any for this particular serial number (this will open up another form related to the serial number so I can add an issues if there are any).The reason I would like the button to be a different color is so I can quickly see if there are any additional notes been added to the serial number. Just in case you may ask why not add the field to the continuos form is that the issues and be quite lengthy and there may be lots of serial numbers on the form
2346 location warehouse price 29.99 (button - green)
2347 location shop price 29.99 (button - red)
Another one I'm struggling with.
I have a mainform and a subform. What I want to achive is :
click on a lable (lab1) in the mainform and set the recordsource ( query1 ) in the subform to display relevant data.
Click on a lable ( lab2) in the mainform and set the recordsouce (query2) having different data than query1 in the subform.
Hope this make sense.
How would I do this.
I have a report based on a query
and it hase a [Product] and a [Quantity] field
Quantity Product
80 F2 1.25" Fiberglass Sucker Rod
60 F2 1" Fiberglass Sucker Rod
80 1" coupling
60 7/8" coupling
what i am trying to do is multiply the quantity by 37.5 if [Product] equals one of the items that are sold by the foot and leave the other items the same
this is the way it needs to display on the report
Quantity Product_Name
80 (3000') F2 1.25 Fiberglass Sucker Rod
60 (2250') F2 1 Fiberglass Sucker Rod
80 1" coupling
60 7/8" coupling
thanks for any help