Forms :: Multiple Between / And Criteria But Only One To Be Chosen
Oct 20, 2014
I have a form where users can enter the beginning and ending dates into text boxes. They could also do it for the number of the week (1 to 52) or a single text box for the quarter.
I set up VBA code where, if you click on a button, it will clear out the data from the other two options before a query is run based on the parameters in those text boxes. Here's a sample piece of code.
Code:
Private Sub btn_UseDates_Click()
Dim lngGreen As Long, lngWhite As Long
lngGreen = RGB(0, 255, 0)
lngWhite = RGB(255, 255, 255)
Me!txtDateFrom.BackColor = lngGreen
Me!txtDateTo.BackColor = lngGreen
Me!txtWeekFrom.BackColor = lngWhite
Me!txtWeekTo.BackColor = lngWhite
Me!txtQuarter.BackColor = lngWhite
Me!txtWeekFrom = ""
Me!txtWeekTo = ""
Me!txtQuarter = ""
End Sub
I've tried setting those txt boxes to Null and I've tried both "" and " ", but to no avail. The query will work if the three types of ranges (date, week, or quarter) are on OR lines, but not on the same line.
View Replies
ADVERTISEMENT
May 28, 2014
I have a table that consist of pricing for different types of products:
Table name: Costs
ID TYPE EastNew EastRenew WestNew WestRenew
1 500 4100 1000 4400 900
2 501 4100 1000 4400 900
3 600 3400 900 4600 1200
4 700 3700 1300 4900 3300
I have a form that consist of many fields that the user needs to manually input data. 3 fields on that form are the criteria i need to determine which pricing to use back on the table. The first field, which name is LTYPE, is a combobox that the user needs to choose as its type (i.e 500,501,600,700). The second field, which name is EastCoast, is a checkbox (Yes/No) that tells me if it's East Coast (East = yes and West = No). The third field, which name is NewRenewal, is a combobox that the user chooses NEW or Renewal).
Example 1, if the user chooses type 501, checkbox is YES for East, Product is NEW. Then the pricing is $4,100
Example 2, if the user chooses type 700, checkbox is unchecked for NO (which is West), Product is Renewal. Then pricing is $3,300
I would like the unbound field to auto determine the pricing based on the 3 criterias chosen. I read around the forums that Dlookup would be the function for this, however, i tried many times but it did not work.
View 6 Replies
View Related
Mar 12, 2015
I have a report with a table as the row source. I have command buttons that opens different forms and allows the user to choose criteria, the form then filters the report based on the chosen criteria in the form, but if I use the destination city form to filter the report by destination city, then filter the report using a different form, the destination city filter is lost, is there a way to filter the report with a form by say destination city, then further filter that dataset with another form for say location city.
View 14 Replies
View Related
Feb 7, 2015
I have a form that adds a record to a table in the database.I want that when i choose a value from a combobox in the form, it will change the value of another textbox according to what value was choosed.So essentially what i did is simply go to the combobox AfterUpdate and there i used this code:
If Me.Combobox.Text = "Solid" Then
Me.TextBox1.Text = "grams"
ElseIf Me.Combobox.Text = "Liquid" Then
Me.TextBox1.Text = "ml"
End If
But i encounter 2 problems:
1. When i set a default value to the textbox (which i want to), the changing of the value in the combobox doesn't do anything whatsoever.
2. When i remove any default value from the textbox, the changing of the value in the combobox does change the textbox to the corresponding value but then gives run-time error 2185: "You can't reference a property or method for a control unless the control has the focus".
View 2 Replies
View Related
Apr 11, 2014
I have a sub form that shows me a companies history. This subform is used on 2 different Parent forms. The record source for the sub form uses a criteria that looks at a companyID field on the parent to determine which records to return. My question is how do I have the query criteria depend on which form is currently opened? The criteria would look something like this:
[Forms]![frmCompanyHistory]![txtCompanyID] OR [Forms]![frmCompany_Project_Details]![txtCompanyID]
If I open the form with this criteria, I will be asked for a value in txtcompanyID for whatever parent form is not open. How do I properly do this without making multiple queries that are almost identical?
View 5 Replies
View Related
Feb 27, 2008
I'm sure this one has been asked time and time again!
I currently have a set of staff questionairres each on a different table.
Currently when I want to export all the scores for one employee to excel (eventually) I have a macro which runs 7 queries, but each asks for the same criteria (Date To & From) and (Staff Name) in turn - very time consuming!
How do I create a query or macro which will ask once for a date range and an employee name and apply to all of my queries?
I am a novice on access and am trying to do this without SQL
Thanks for your help
View 1 Replies
View Related
May 18, 2013
I have a form bound to a table and I need to display and edit records. I want to narrow down the records by applying two filters; one combobox to select the ClientID and another to select the date. What I have so far is this:
Private Sub CboClientID_AfterUpdate()
Me.Detail.Visible = True
CboDate.RowSource = "Select AppointmentDate " & _
"FROM tblSample " & _
"WHERE ClientID = '" & CboClientID & "' " & _
"ORDER BY AppointmentDate"
End Sub
then in the cboDate afterupdate event I have:
Private Sub CboDate_AfterUpdate()
Me.Filter = "ClientID = '" & Me.CboClientID & "' AND [AppointmentDate] = #" & Me.CboDate & "#"
Me.FilterOn = True
End Sub
This appears to work with some records and not with others and I don't know why?
View 6 Replies
View Related
Apr 30, 2014
I have a dsum statement with multiple criteria that I cant get to work. Basically it returns no records, when it should return records that have a CategoryID = 1 and a State/Province = 14.
Code:
TotalSMECount = Nz(DSum("WorkingDays", "qryTotalUsageForDashboardNew", "CategoryID = 1" And "State/Province = " & Me.cboProvinceFilter.Column(2) & "")) + Nz(DSum("UsageDays", "qryEquipmentDaysRapidProtoCumulative", "CategoryID = 1"))
I am guessing that I just have the criteria portion written incorrectly Is there anything obviously wrong? For the record, when I debug.print Me.cbo Province Filter.Column(2) it returns the value '14', which is correct for my test data.
View 3 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
Feb 4, 2015
I am trying to create a form that allows the user to select a detective and multiple case statuses (such active, inactive, dna, filing, etc.). The form is intended to allow the user to determine the number of assigned cases. The form is tied to a query and the criteria that I've set up in the query is:
[Forms]![DET_CaseManagementFRM]![Combo30] which is under the investigator field.
The case status field has similar criteria [Forms]![DET_CaseManagementFRM]![List43].
My problem is that I cant figure out how to allow the user to select multiple criteria in case status field in my form. The research I've done online says you need to specify simple or extended in the Multi Select field under the other tab in the property sheet. However, when I do specify simple it doesnt return any records which I know it not true. Is there VB code that I need for this?
View 3 Replies
View Related
Jan 31, 2015
I can't seem to get past this expression/criteria problem.
[Actual] is my field I want to grab data from
[L3-4-5] is my main table
[CDATE] is a form control where the user enters a date
[Quarter] is a number field and want it to equal 1
[Partname] is a text field and string it to equal 1
My current expression is:
=DLookUp("ACTUAL","L3-4-5","ID=" & [CDATE] And "[QUARTER]=1" And "[PARTNAME]='1'")
Although my other expression on another form works.In my if code statement I have
Me.DAYS_TRAINED.Value = DLookup("Days", "TRAINED LH A-PILLAR", "ID=" & FTM_NAME)
So I think I don't understand the multiple criteria part.
View 3 Replies
View Related
Oct 8, 2013
I'm currently trying to build in access a replica of an atrocious search function in excel.
I have a list of data quite simply in 5 columns and i want to filter through this data about (10000 rows).
My form has 5 data points.
The first is Product Name this is a string (i've looked up a lot of codes to search strings and even partial strings but no one seems to have done what i need).
- Basically i need it to search for any / multiple parts of the string entered.
- for example if someone enters apple trees june i need it to look for cells containing those three words in any order, even conjoined for example "appletreejune" would still return or "apples on a tree in june".
- This is attached to a single col called Product Name.
Based on this search i need it to look for data in a col called mark type (which is selected by a drop down)
Then by Market Context (also a drop down)
Then by a start and an end date, however, only one of the values (start or end) needs to be between the start and the end dates listed in the start and end date columns in the table.
View 4 Replies
View Related
Nov 15, 2013
I have a form with combo box named "TEXT17" i have an another text box named(Basic Pay). How can I use dlookup creteria with two condition
EMPID= text17 value and
month in lbl9 value="mar"
I also paste my code here but show error
Me.Text43 = DLookup("Basicpay", "Salaryall", "[EMPID]='" & Me.Text17 & "'" And "[Month]='" & "Mar")
View 6 Replies
View Related
Jun 20, 2014
I have a large table with information about different cars, called "Car Metadata". In this, each car has a unique identifier ("Car Code"), the make ("Manufacturer Name") and the model ("Short Model").
What I want to do is create a form with dropdown menus.I have a first combo box with all the makes in the Metadata table.
What I am having trouble with is that I want a second combo box underneath which would only show the models which correspond to the make selected in the first box.E.g. someone could select Ford in the top box and the second box would only show Fiesta, Focus etc.
Once it has done that I want it to be able to write the Car Codes for the records which match up to the make & model selected to a new table. Just to complicate things further, there can be multiple records with the same make and model but different Codes, I want all these multiple codes to be written to the final table.
View 4 Replies
View Related
May 30, 2015
I Have a forms which brings up a record in textboxes when you hit a search Button using a value that you type into a Textbox called BarTxt. My problem is that this value may not be unique. Therefore I'm trying to find and update the table using 2 values from the table (Barcode and PurchaseOrder).I have entered the code
Code:
Private Sub SearchBtn_Click()
PNTxt = DLookup("PartNumber", "BookInTable", "BarCode ='" & [BarTxt] & "'" And "PurchaseOrder ='" & [POTxt] & "'")
End Sub
This however is just bringing up a Type mismatch error. Even though both are text fields.Also even if I just use Barcode to search which works. My update Code
Code:
DoCmd.RunSQL "Update BookInTable SET DateBookedOut = '" & Me!DateTxt & "' WHERE BarCode ='" & [BarTxt] & "'" AND PurchaseOrder ='" & [POTxt] &
I get an Compile error saying "expected expression".
View 3 Replies
View Related
May 15, 2013
I have a list of client stored in a table "Clients". I would like a form to present a user with the next client in the list at the click of a button, but there will be some exceptions:
Some clients will be given priority, and should be moved to the top of the list although they may not be the next logical entry in the table. Priority clients are indicated by a field "Priority" with a Yes/No option set before work begins.
Some clients will have requested a call at a certain time of the day - This time will be stored in a field named timeToCall. The next user to click the button after the time has passed should be given that clients details.
When the end of the list is reached any clients who could not be contacted will be tried again. My table currently has a field "Attempts" to track this, but that may not be needed. Clients who have been dealt with will be removed from the "Clients" table to a "Completed" table.
Whichever record is selected will be flagged as being dealt with so that 2 people don't get the same entry. I may add a new field for this, but right now I plan to use the "attempts" field to control this
I am unsure of the best approach to the above, I'm not very well-versed in Access, but if I were doing this in Excel (as originally requested by my boss) I would do it like this:
Use a form where the "Next" button will use VBA code to first check if there are any priority clients, then check if there are any timed client's ready to call and then move to the next entry that has had 0 contact attempts. Whichever record is the next one will be displayed in the form to the user.
View 1 Replies
View Related
Aug 14, 2015
I want to result as dlookup function in access 2007, when I use dlookup like this
=DLookUp("GPFDed","[SalaryAll]","[EMPID]='" & ![Cmb_EMPID] & "' And [SalMonth]='" & "Jan" & "' And [Salyear]='" & [me]![Label17].[Caption] & "'")
it gives #Name?
What is the problem and how can i change the label caption to Combo box column 2 value...
View 1 Replies
View Related
Jun 14, 2015
I have a problem printing a Subform that uses multiple criteria(in textboxes) as filters.
The search portion of the form works fine. The problem is I have created a report based on the subform and am using the following code to open/filter the report
Code:
Private Sub PrintBtn_Click()
Dim strCriterion As String
Dim strMsg As String, strTitle As String
[Code].....
View 3 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
Jan 4, 2014
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
View 4 Replies
View Related
Feb 4, 2005
I have a form that opens by showing an option group:
Search by: 1-Team 2-Rep
Based on the option chosen (1 or 2) another set of boxes and options appear that let you select the team or rep and the report you want:
If 1:
Combo: Team A/Team B/Team C
Option Group: 1-Report A, 2-Report B, 3-Report C
If 2:
Combo: JohnDoeRep/JaneDoeRep/SchmoeDoeRep
Option Group: 1-Report D, 2-Report E, 3-Report F
I have specified the logic for which report to run in the OnClick event of the Run Report button based on the conditions above.
My problem occurs when a user for example chooses Option 1 and runs a report, then comes back to the form and wants to run a report under Option 2. When this happens, the report selection for Option 1 stays selected, and as a result, both reports are run when the Run Report button is clicked.
How do I get the Form to deselect any options in the old selection if a user switches from Option 1 to 2 or vice versa?
Many thanks to all who help!
View 1 Replies
View Related
Jan 26, 2015
I have a form that is showing data from 1 table. That table has 12 different fields on it and I want to be able to filter based on selections I make in a combo box in the header of the form. The filter string must be dynamic enough to allow filtering based on 1 criteria selected, or multiple criteria selected. For example:
If I have values in filter fields 3, 5, and 9 I'd want the filter string to be created as follows:
"...WHERE field3 = field3filter.value AND field5 = field5filter.value AND field9 = field9filter.value"
If I have values in only field 7, I'd want th efilter string to be created as follows:
"...WHERE field7 = field7filter.value"
And so on and so on.
I have created some filters before but all of the different VBA syntaxes I'm using seem to come up short.
View 5 Replies
View Related
Sep 29, 2005
Hi, I'm in the process of building a program in access 2003 for estimating roofs, I've ran into a little problem, as described below. any suggestions would be very useful
Populating the text box’s that relate to the chosen combo box’s with the sum of a calculation.
In other words if X combo box is chosen (There are many combo boxes to choose from) populate xx text box with the sum of a calculation.
Refer to the print screen example of my program: http://www.roofmart.net/Rest.asp
Notice under the label felts the 15lb 36” felt combo box is chosen and it displays the value of 11 and next to the combo box is a text box that displays the value of 25 The calculation will be (11*25) = 275 this value of 275 must be added to the text box values that are being shown in the forms header under 30yr where it reads $4,606.80 and 40 yr $6002.80 only, this is because the 30yr comp and the 40 yr comp combo boxes are the only chosen roof types under the label roof types above the felt label.
Hopefully this makes sense.
Thanks -- Any help with be greatly appreciated.
View 3 Replies
View Related
Jun 20, 2014
What i am try to do is have specific text boxes or items appear on the form depending on the combobox item you have chosen.
For Example
Combobox
CAR
CAKE RECEIPE
CLOTHING
if car is chosen 2 text boxes appear example car manufacturer, Car name. If Cake recipe is chosen a larger text box appears, if Clothing is chosen another combo box appears and gives you more options.
View 5 Replies
View Related
Aug 2, 2005
Hi I'm a real newbie at this but basically I have a form displaying three lots of contacts Expediting, Quotation & Miscellaneous contacts.After choosing the company name from a list box it displays the Expediting, Quotation & Miscellaneous contacts info - but doing it this way there isnt much space on the screen to show all the Contact names,Telephone fax & email addresses clearly& at a decent size.Apart from the company list box I want to have a list box/combo box with:
Expediting
Quotation
Miscellaneous
and then after choosing Expediting, Quotation or Miscellaneous from the list/combobox i want it to take the relevant data from the Expediting, Quotation, or Miscellaneous contacts sections and only display the information relating to those sections only, therefore allowing me to have bigger text and not so cramped display so everyone can read it ok and get more info on the screen for each contact group.
If anyone could help it would be much appreciated!!!! Thankyou in advance for any help. Be gentle with me I am a newbie,lol.
Thanks again
View 2 Replies
View Related
Jan 29, 2015
I am having is that the auto-number value on his machine is different than on mine so anytime I try to add data in testing, it tries to create a primary key value that already exists in the table.Can I set the auto-number "counter" on my computer to a chosen value so I don't have these issues?
View 14 Replies
View Related
