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.
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! :(
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
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:
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.
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,
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!
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 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 RelatedI 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] & " ' ")
What I want to do is following.
In form ASSIGN(fields are takedate and inout) ,if TAKEDATE is different then 0 then set value of inout field to 1
My VBA knowledge is poor cause i dont think this should be hard
thanks in advance
Is 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.
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 would like to change the property of a text box to Visible and Required when a user chooses a specific value in a drop down box of a different text box. Is this possible using a macro or some other way?
Thank you,
Deana
I have a report that is looking up min and max quality specifications for a product. The table with the specifications has a Yes/No check box field for when the specs are defined as "Internal". So the table looks like Product/MinValue/MaxValue/Internal
I want the report to display "TEST AND RECORD" when the Internal box is checked where the MinValue and MaxValue (that are stored in the table) would be displayed otherwise.
I have a form I would like to validate input on. The field UpdatedResponsibilityCode can be updated but it can only have certain values based on the PaymentResponsibilityCode. Is there a way to force this validation?
If UpdatedResponsibilityCode = 4 then PaymentResponsibilityID can not equal 1
I have a main table which is autonumbered on unitid, I have another table which is linked via the unitid which holds items purchased for the main table, so the second table is a subform of the suppliers and will have multiple entries of the same unitid. What I would like to do is when an item is entered into the second table, check the main table to see if it exists, if it does populate the unitid field with the unitid from the main table, and if not exist create a new record in the main table with the next unitid number.
This in essence is what I want, bearing in mind that the tblinvoices is a subform and the tblmain is not open.
If ([tblinvoices]![Unit] = [tblmain]![Unit]) Then
[tblinvoices]![UnitID] = [tblinvoices]![UnitID]
End If
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 have a query where I prompt for a Report_Date to create a new field; Report_Date:[ Enter date for report]
Is it possible to use the result from this prompt as a criteria in a different field?
for example, Order_Date based on the criteria of <=[Report_Date]
Hi.
I have a main table (with other tables related to it).
It has the 255 max number of fields. (I know... But bear with me.)
ANyway, I need to change some field's size to smaller sizes. But if I try to change them, even 1 at a time, I get the cannot do because I have too many fields defined.
Ideas???
Russ
All,
Is there a way to create a query to show when text in a specific field was changed?