Multiple Criteria Set Via Form Field...
Mar 24, 2005
Hello,
...I'm not sure if I'm making this question more complex or not or if it's even possible? (I would think so) Anyway...What I am trying to do is create a form in which I can input more than one criteria into a field - We will cal it txtSetValues. Then via a button open a query that will use txtCode as the criteria for searching records in a table. The
table is called: tblMASTERTABLE-EmpCount
query is called: qryEmpCount
The table currently only has two field; ORG and Name. The criteria I have in ORG is: Like [Forms]![frmEmpCount]![txtCode]
I currently can find any specific ORG just fine. I can also use the wildcard and that works fine. But how can I set it up so that I can enter in multiples... For example, I would like to find all employees that there org is either; AL001 OR AL010 OR AL100 - The wildcard will 'Almost' do it in some circumstances, but it may gather some orgs that I don't want in this list. I hope I am explaining this in an easily understood fashion?
Thanks,
---roystreet
P.S. Now I know I could just make multiple fields in the form (ie; txtCode2, txtCode3,etc) and then just add those as criteria in the query, but I was hoping that I wouldn't have to do that.
View Replies
ADVERTISEMENT
Mar 9, 2007
Hi all,I posted something similar to this beforehttp://www.access-programmers.co.uk/forums/showthread.php?t=124289But i didnt get it figured out.Is it possible to use the same field for multiple criteria in a query?the one i would like to base it on is taskID.i just want the total time to be called admin time if taskid=2 and investigative time if taskid<>2.Ive tried it with single and multiple queries in one and am recieving errors with both. help is always appreciated!Woohoo for 100 posts!
View 3 Replies
View Related
Jul 3, 2014
I am trying to build a query where the result is one line per ID with all results for W(eek)E(nding) on the same line.
ID ---- UOM ---- WEJUN20 ---- WEJUN27 --- WEJUL4
6 ---- Hours --- ---250 -- --- --- - -- 278 -- -- --- --- 242
The result I am getting is in the format below:
ID --- UOM --- WEJUN20 --- WEJUN27 --- WEJUL4
6 --- Hours --- ---250
6 --- Hours --- --- --- --- ---- --- --278
6 --- Hours -- --- -- --- --- --- --- --- --- --- ---- -- 242
This is the trimmed down part of the query that is causing the result.
Code:
SELECT [tblP&E].PnE_ID, tblUsage.Measurement AS UOM, IIf([tblUsage]![Week_Ending]=#6/20/2014#,[tblUsage]![Usage],"") AS [June 20], IIf([tblUsage]![Week_Ending]=#6/27/2014#,[tblUsage]![Usage],"") AS [June 27], IIf([tblUsage]![Week_Ending]=#7/4/2014#,[tblUsage]![Usage],"") AS [July 4], IIf([tblUsage]![Week_Ending]=#7/11/2014#,[tblUsage]![Usage],"") AS [July 11]
FROM tblUsage RIGHT JOIN ([tblP&E] LEFT JOIN tblCosts ON [tblP&E].[PnE_ID] = tblCosts.[PnE_ID]) ON tblUsage.PNE_ID = [tblP&E].PnE_ID;
View 3 Replies
View Related
Jan 12, 2007
Hello,
Was wondering if there is a way without building individual update queries, to update info in one field that has multiple criteria ?
Basically I need to change/update daily multiple ID numbers to new ID numbers, long story on why this needs to be done but for now I need to do it this way.
Example: 12345 update to ABCDE, 6789 update to FGHI, etc. These ID's are all within the same field in the table.
It works fine running each ID one at a time but was wondering if it is possible to do all these updates within one query or code ?
Thanks for any assistance
View 6 Replies
View Related
Apr 5, 2007
I have a query that displays all records. I need it to limit based on multiple criteria in a single field. I.E... instead of all employees from every section, I just want it to display employees from section A, C, D, F one time and next time maybe go with section B, C, D. For some reason the solution eludes my little brain.
View 3 Replies
View Related
Apr 4, 2013
I am designing an update query for a specific field in my table
There are 3 fields: PROVIDER_GROUP, MEDI_CAL_IND, MEMBER_LOW_CAT
I want to update the value of "Y" in the MEDI_CAL_IND field to "N" if:
the medi_cal_ind value is "Y" AND the PROVIDER_GROUP is "Medicare_Part_D"
OR
the medi-cal_ind value is "Y" AND the MEMBER_LOW_CAT is "Commercial"
A screenshot of the table is attached.
View 5 Replies
View Related
Dec 30, 2014
Preamble edit: I'm running Access 2003.
I have two tables, Students and AttendanceRecords.
Students just has studentID and studentName
AttendanceRecords has AttRecID, studentID, presence, thedate
I'm looking to create what looks like an Excel grid, with the last 10 days as columns and the student names as rows. All the cells in middle will be filled with the values of 'presence' for that student/day (e.g., P for present, A for absent).
Here's something I'm currently considering.
-I could make 10 queries, each using LEFT JOIN to connect studentName with presence & thedate on studentID, varying the 10 queries only in that 'thedate' will have a criteria of Date() -1 , Date() -2 , etc.
-If I'm understanding it correctly, I'll then have 10 tables, each containing 3 rows -- student name, presence, and the date (with each table having only 1 date repeated throughout).
-I could then join those 10 queries together on studentName, theoretically resulting in 1 big table with all the student names and the corresponding presence values for the last 10 days
If I do that, I could make a form in Continuous view and have each row show the studentName and 10 text boxes closely bunched up with presence values.
That seems very inefficient? Making 10 queries separately and then manually merging them seems redundant.
Also, now that I think about it, will the final product end up being read-only, or if the user changes one of the presence cells will it update the corresponding record in AttendanceRecord?
View 3 Replies
View Related
Jul 24, 2013
I am trying to make a query that outputs the minimum "Need Year" AND ALSO if the need year was equal to 9999 it shows "NO DATA".
This is what I have so far for checking the minimum value:
field: Need Year: MinofList(PMS_output!pqi_ny,PMS_output!iri_ny,PMS_ output!sdi_ny,pms_output!sai_ny)
I am not sure if I should be putting it in the criteria to check whether this minimum value (need year) equals to 9999 or not and if it does, it says "NO DATA" instead of 9999.
View 3 Replies
View Related
Apr 21, 2013
I need to update the periodtype field in my table depending on different values in the field Formtype- I am looking to do this without having to use VBA. I have the following fields in a table - I want to update the value of the field Period_type as follows -
When Formtype is 10-Q, update Periodtype to "Quarterly"
When Formtype is 10-K, update Periodtype to "Annual"
The current value of Periodtype for both formtypes is "Semiannual"
Can I do this using only update query?
View 2 Replies
View Related
Jul 30, 2014
I am using MS Access 2007.
I would like to display a report based on the table called "expenses", filtered by:
- "from" (datefrom field) and "to" (dateto field) date on "payment date" field;
- multiple criteria on same field called "payment method" (I would like to include only payment methods "check" and "credit card", but not the other payment methods in the field, such as "cash", "transfer", etc.)
For that end I made a query based on the table "expenses", and in the "payment date", in the criteria field, I entered:
between [form]![formname]![datefrom] and [form]![formname]![dateto]
This works fine so far, however when I attempt to add multiple criteria on the "payment method" field, it does not filter accurately any longer. In the same row of the criteria field where I completed the date criteria, I enter "check". In the next row, same field, I enter "credit card".
Since it doesn't work, I tried putting both arguments in the same line as the date criteria (always in the payment method field) as: "check" and "credit card" but still does not work (now it filters the payment method correctly, but the dates filter appear as if I have never completed them).
View 3 Replies
View Related
Jun 2, 2013
I'm working with a table of bird survival data I am trying to summarize in a query. I've got a bit of a roundabout way to achieve my goal, but I'm curious if there is a simpler approach.
Background : In my table, each row represents a day I check a given nest and includes a [Nest ID] (not unique, multiple visits to each nest), a [visit ID] (auto numbered, so it's a unique value for each visit at each nest), the calendar day I visited a nest [Date], and [Survive] (1 or 0) depending on whether a nest survived or failed.
I'm trying to convert this detailed table into one that is more concise. Instead of each visit to a nest being a row, each nest becomes a row with 4 fields: The Nest ID, the minimum date (the day I found a nest), the last day a nest was checked (Max[Date]), and the last day a nest was checked alive (essentially max date where survival=1).
My current solution is to run 3 separate queries. The first queries the max date where survival=1, the second queries the max and min dates regardless of any other criteria, and the third brings both queries together.
I am curious if there is a way to create the same final product in a single query rather than doing multiple ones as I have done?
View 3 Replies
View Related
Feb 25, 2015
I have a small problem with dlookup multiple criteria. Vba code looks like this:
Code:
Label34.Caption = DLookup("[Spent_Hours]", "249_1_CHours", "[Date_Added]= " & Me.Text27 & " And [Shift] = '" & Me.Text29 & "'")
This gives following error:
Syntax error in number in query expression '[Date_Added]=4.02.2015 And [Shift] = '2'.
[Shift] column is a numeric field.
View 14 Replies
View Related
Feb 10, 2008
I have a form that has seven unbound fields named txtCityCriteria1 through txtCityCriteria7
The idea is to have the use type in up to seven cities and have this applied to the criteria of the city field in a query. This query is the datasource for a report. I know how to do a single text ....
[Forms]![frm_OpenReport]![txtCityCriteria1]
in the criteria row would do the job. But how can I do seven?
Oh and if all are left blank i need it to open another form that informs them of their mistake.
I have looked through these forums and have found several possible solutions but I can not get any to work. Any assistance would be greatly appreciated. Take it easy on me I am relatively new to this code thing. Used to do everything in macros.
View 1 Replies
View Related
May 19, 2005
I have a patient form that has a subform showing the care plans. At the side of each care plan for that patient i have a button that if clicked will take you to the specific care plan. I need this to open the care plan form based on multiple criteria which is
Date (Date Field)
Care Plan Number (Numeric Field)
Patient number. (Text field)
These fields are all on the subform and will be populated.
Any ideas? :confused:
View 4 Replies
View Related
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
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
Apr 19, 2013
I have a form lets call it 'Customer' and I have a table called orders how can I write 'Excel like' Countifs and sumifs formulas? I have been trying the IIf functions I believe correctly but am not getting the results expected or rather nothing at all!
What I am wanting to do is for example look at the table 'orders' and count all 'orders' which are for the 'Customer' in the active form this field is imaginatively called 'Customer Number' and 'orders' that are for simplicity 'rocks'
Basically if the client in the active form exists in the orders field count how many times they have placed an order for rocks.
View 1 Replies
View Related
Apr 17, 2014
How to open FORM which has "inner join" in source?
Code:
...
Dim idRS As DAO.Recordset, ShowIdentity
strSQL = "INSERT INTO tblZlecenia (id_zlecenia_info, DataPrzyjecia) VALUES ('" & _
ostateczne & "', " & _
Format(Date, "#mm/dd/yyyy#") & ")"
[code]....
View 5 Replies
View Related
Jul 29, 2015
I am trying to set up a query by form with multiple criteria.For the majority of the criteria I'm using the format: Like [Form]![formName].[txtInputboxName] &"*" .
I copied the Like (...) &"*" from someone else in order to allow for multiple, optional criteria, which does do the trick, however I don't understand why.
I now want to set up a criteria on my query to search for values between two input values to gives the records in the range e.g between 50 and 100 Following the above format, I want to put something that achieves this:
Between Like [Form]![formName].[txtInputboxName1] &"*" AND Like [Form]![formName].[txtInputboxName2] &"*"
with Inputbox1 being 50 and Inputbox2 being 100, however that code doesn't work and I don't know how to get around it as it keeps giving the 'incorrect syntax' error.
View 5 Replies
View Related
Nov 17, 2006
Hi,
I have the following code which i found on another thread on this forum (thanks to original author) which is attached to the On Click of a button which prints the report corresponding to the details displayed in the form.
Dim strCriterion As String
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
If IsNull(Me![ReferenceNumber]) Then
strMsg = "You cannot print a Blank Form!!."
strTitle = "Print Error"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Exit Sub
End If
If Me.Dirty Then
Me.Dirty = False
End If
strCriterion = "[ReferenceNumber]=" & Me![ReferenceNumber]
DoCmd.OpenReport "DoC Certificate", acViewNormal, , strCriterion
This works fine, however, i need to be able to select the report based on more than 1 criteria. For example, the Reference Number can be repeated but is distinguishable from each other by an Issue Number i.e. ReferenceNumber = 93, Issue 1 or 2 etc. At present when i run the above it prints all versions of, in this case, reference number 93, which given that each report is only a single page isn't a show-stopper but it would be nice to have it working as i would like.
I have tried adding to the strCriterion line such as strCriterion = "[ReferenceNumber]=" & Me![ReferenceNumber] and "[IssueNumber]=" & Me![IssueNumber] but no joy. I have tried bracketing the whole line and variations thereof, again no joy.
Can anybody tell if what i am attempting to do is possible and if so how do i go about it?
I have tried the above coding using MasterID which is the Autonumber PK but it produces an "Enter Parameter Value" box for MasterID. Obvioulsy if i can get it to work for the Autonumber then my problem goes away but i can't seem to figure out why it works for Reference Number (Number) and not MasterId (Autonumber)?
Regards
Alan
View 5 Replies
View Related
May 29, 2014
I have a form where varying layers of information can be entered. In some cases, a user may know all information or only part. If i tie the query to the form, each field has to be populated for it to work. how do i set the query up to effectively use the information available and not require all information?
I have tried setting VBA code as follows:
If Me.Combo1 = Null Then Me.Combo1 = "*"
However, when i do this, it updates the form field with an asterick/wild card but does not include in query. yet, when i update the form fields with data (not asterisk/wild card) the query runs.
View 13 Replies
View Related
Nov 26, 2013
I need to create a query that has multiple criteria selected from a form For example. I will always have the customer name but then the user might also choose any one or more of the following, start date, end date, number etc
I have tried to create a select query and added the customer form element to the criteria but I am not sure how to do the rest because it will always have at least one more criteria but I am not sure which one and it may have up to 5 more criteria.
View 5 Replies
View Related
Jan 4, 2006
I am trying to put together an form to be used in an academic administration database.
Ideally, I would like to create a form that will allow a user to select criteria and get a list of all students fitting that criteria.
For example:
Show me all a) all Economics majors with b) 30-50 Credits who c) have not taken English 101.
All of this data could be compiled into one query, but I would like to create a form that will allow a user to select what data he or she would like to see.
(Maybe they only want to see graduates)
I hope this makes sense.
And I hope someone can help point me in the right direction.
Thank you!
View 2 Replies
View Related
Sep 29, 2014
I have a user form with six different dropdown boxes. I would like to create a query that gets its criteria from users selecting values from one or more of these boxes.
I have tried:
[forms]![frmName]![comboboxname] or [forms]![formName]![comboboxname] is null
in the corresponding query fields and get a "query is too complex" error message, even when only selecting one criteria.
What am I doing wrong?
View 5 Replies
View Related
Nov 14, 2012
My form-based search mechanism uses controls to set the parameters for the query data source.
I have one field call quantity in stock. I could you a Between and And method to allow the end user to input the stock quantity they want.
HOWEVER, i would love it for the user to first select the Comparison Operator (e.g. > , <, >=, <=) from a combo box and then in an adjacent text box, enter the quantity.
The expression i entered in the query goes something like this.... Forms![frmSearch]![cboRange] & [Forms]![frmSearch]![txtQuantity]
When i try and run this, i get the message "THe expression is too complex to be evaluated".
View 5 Replies
View Related
Aug 13, 2015
I have Table for rooms called Rooms, and the data in the table is roomNumber which is in the format Letter and two Digit number, so A01 would be dorm building A and room building 1, and I then have a seperate row named roomType that is either VIP or Semi Private.
Now I am creating a form where a worker will put in there scheduledCheckin date and scheduledCheckout date and it will be written to the Bookings table. I would like this form however to take the dates they have put in, as well as room type (Semi-Private or VIP, and assign them an available room that isn't booked at all in that range) or list all available rooms for that range of time and they could then just select the room. I would rather it automatically assigns an available room based on room type though because this check in system is going to have about 500 rooms.
I've attached a link for what I have so far. I know how to make a query to list anyone who has booked rooms over that date, but need one for just preventing booking the same room.
[URL] .....
View 3 Replies
View Related
