I have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.
View RepliesOk so im working in MS Access 2007.
I want to create a query based on 3 combo boxes but have it so that if the field in the second or third combo box is not populated the query still runs.
Right now i have the Criteria set for the three columns that i wish to sort by as seen below.
Column 1
Criteria: [Forms]![Entity Selection Form]![areabox2]
Column 2
Criteria: [Forms]![Entity Selection Form]![devbox2]
Column 3
Criteria: [Forms]![Entity Selection Form]![entitybox2]
This gives me the correct query result but im forced to make a selection from each combo box. Is there a way to progamme it so that if I only make a selection from the first combobox and leave the others blank i can still get results in a query?
I'm trying to create a form which filters a report based off of combo boxes selected by the user. The code I'm using currently is:
Code:
DoCmd.OpenReport "rptProgramAttendees", acViewReport, , "ProgramIDFK = " & cboProgramTitle
This works great to return a report if the user selects something from the combo box. How do I adapt this so that the user can also leave the combo box blank and filter the report to return all records?Additionally, what if I want to have the user filter between dates selected on the form; i.e. between 'txtStart' and 'txtEnd'
On I form I have a combo box called cboMobileStatus.Its row source type is "Value List".The row source is: 1;" All";2;"Has";3;"None".I have a query based on table "tblCustomerContacts" and I want to limit the records returned based on the value of the combo box.So if the user selects:
* All (1) I want all records returned.
* Has (2) I only want records that have a mobile number returned
* None (3) I only want records that do not have a mobile number returned
The field mobile is text (to keep the leading 0)I thought of using the Switch function in the criteria of the query for field Mobile like this:
Code:
Switch([Forms]![frmPrintCustomer]![cboMobileStatus]=1,([tblCustomerContacts].[Mobile]) Like "*",[Forms]![frmPrintCustomer]![cboMobileStatus]=2,([tblCustomerContacts].[Mobile]) Is Not Null,[Forms]![frmPrintCustomer]![cboMobileStatus]=3,([tblCustomerContacts].[Mobile]) Is Null)
but the query does not like it!Am I on the right track, and if so, how should I modify the code?
I have a form with cascading combo boxes pulling from a table. They work perfectly, no worries. My problem now is if I do not enter information in every combo box (i.e. only two out of four combo boxes), how can I still run the query and get the appropriate information?
For example (these are my combo box titles in order):
Product
Type
Customer
Contract #
I don't want to necessarily look by Contract # all the time, but sometimes just by the general Product and Type to get a larger view. How do I set up expressions/criteria in my query to accurately produce that information? Right now it just produces a blank query table if I don't fill out all the boxes.
I've tried a couple of expressions with "isnull" criteria, but I must be doing it incorrectly.
What I want to do is set the criteria of a query to the value on a form if the form is open, and set it to a different value if the form is closed. I tried using a function on the OnLoad event of the form to set a variable called IsOpen to 1 if the form is open, and reset that variable to 0 when the form closes, but when I tried using the variable in an IIF statement in the query criteria, I got a "Its too complex" error.
Here's what I really want to do. I have a very complex form with multiple tabs and subforms. The subforms populate based on a query of what is selected and loaded into a textbox control on the first tab of the form. The first tab has a subform that is based on the main table. Rather than recreate that form, I want to copy it and change the rowsource on the first tab to a subset of the main table, and tell the query to use the textbox on the new form so I don't have to go and replicate all the other subforms. Is there a way to do that or am I just screwed?
I have a rather simple query that will produce filtered results based on the user's selection in 1 to 4 separate combo boxes on a form. But I can't get it to do what I want it to do. Here are my vitals:
Form = frmQryAdHoc
City combo box = cboCity (from tblWARNData)
County combo box = cboCounty (from tblCounties)
MWA combo box = cboWMA (from tblLookUpData)
Query = qryAdHoc (source = tblWARNData)
CoCity column criteria = [Forms]![frmQryAdHoc]![cboCity]
CoCounty column criteria = [Forms]![frmQryAdHoc]![cboCounty]
CoMWA column criteria = [Forms]![frmQryAdHoc]![cboMWA]
The query works fine if I make selections in all 3 combo boxes. However, if I make a selection in only cboCity, the query returns no results because it doesn't find a matching record where the city = cboCity and the County and MWA fields are blank.
Here's how I want it to work:
If cboCity = Detroit and cboCounty = Wayne, then
Results = 40 records (where city = Detroit AND county = Wayne)
But if cboCounty = Wayne and cboCity & cboMWA are blank, then
Results = 120 records (where county = Wayne; cities & MWAs = various)
IOW, I want the query to filter only on the cbo's where a selection has been made by the user and ignore any empty cbo's. What statement do I need to enter in each cbo-related criteria field so the query filters only on the combo boxes where a selection has been made by the user?
Any help you can provide will be greatly appreciated. Thank you.
I am trying to run a query and display the results in a report (the report side of it is childs play and not a problem). The problem I am having is that I have a search form which should allow the user to search any one of 6 fields (text boxes) or a combination of each.
If the user enters something into a field then that search criteria must match. I wanted to have it so if all fields are left blank then it will show all entries in the database (but it isn't, it shows a blank report). I also wanted it to allow partial completion of boxes.
So for instance if I have 5 customers (Jones, Jonson, Jonus, jimjonkins, Janis) and I type "Jon" into the name field then I would like it to show the first 4 records as they all contain "jon" somewhere in their name but its not, its only allowing exact matches.
I currently have '[forms]![Search_Customer]![Search_Name]'.
Hey guys, I'm not sure if this is possible, and it is the most challenging thing that I have tried to implement into my database to date.
What I have is a booking form which allows you to Add Bookings for camera kits.
When a customer comes in and would like to book a kit, the form must be filled in with the relevant details, I have 2 combo boxes on this form.
The first is the Kit selection, there are 5 camera kits.
The second is an option to book the full kit out, or just components.
So here's the tricky part...
When the first combo box selection is made (eg. Kit 3) I need the Kit3 table to be active.
Then, when the user selects "Components" from the second combo box, I need a form to open up based on both of the above.
So a form based on tblKit3 would be opened and the user can manually select the components that they require to be booked out.
Any help at all would be appreciated.
My database is using data that is entered by the employees to generate Quotations. There is one important piece of information that will not be entered by the employee. The quotes involve metals which are priced based on market price and weight. I am planning on integrating a data feed with this information, but for now I want to enter it manually in a table. The price depends on two combo boxes one for "Precious or Base Metals", and one for "Metal Name". I want those two values to call the price from a table, and automatically fill in the "Market Price" field in the form.Also once that is in I would like to do my calculations. I am planning on using queries to do these. Is that the correct method?
View 6 Replies View RelatedI have two combo boxes in the form header and command buttons in the form detail. The combos allow the user to select either a customer or a prospect, then the command buttons open forms that only show records pertaining to the customer or prospect selected. Combos are "4Custcbo" and "4Proscbo".
1.How do I change which command buttons are available depending on whether the user has selected a customer or a prospect? Do I use two different subforms or is there a better way?
2.How do I hide the other combo box once the user has selected either a customer or a prospect?
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
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 RelatedI 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?
I managed to get separate combo boxes to filter out results on a query, however now I have a slightly different problem.
I managed to get the combo boxes to filter records in different query criteria, i.e criteria A for field A, criteria B for field B, etc.
Now I would like to have several combo box filters in one criteria field, however no matter how much I try to move the code about, they either filter out nothing at all, or show up blank records as they're filtering one after the other, i.e filter for A, and then B, which clearly won't work as there will be no B if you've already filtered for A.
The code I'm using in each criteria box is;
Like "*" & [Forms]![MyForm]![Combo1] & "*"
and this works as a single criteria in a single field.
I have a form with a check box. A query is run that looks at that check box and decides what the criteria are based on that. So, if the check box is checked, it should pull in all data in the field that is a Y. If it is not checked, i want it to pull all data (Y's and N's and blanks).
here is my criteria:
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","*")
This does not seem to work. I have also tried:
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y")
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y",Like "*")
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","like "*"")
Attached I have a database that I've been working on which has a form called "frmCriteriaSearch". It is based off of the qryCriteriaListBoxUpdate query. I am trying to get the listbox in the second tab of the results section to work. It queries fine for the checkboxes, but I cannot get the comboboxes to affect the query (unless a checkbox has already been selected)
View 6 Replies View RelatedI am working in MS access 2007.
What I am trying to do is fairly simple i just dont have the ability to correctly code what i want to do.
I want to filter my query based on some criteria in multiple columns. But i only want the query to filter based on the specific criteria if a checkbox has been selected.
Basically i want the criteria for one of the columns criteria to read
IF a check box "Check0" is selected THEN filter the column to only records that = 1 and if "Check2" then filter all records that = 2
I have a SubForm "assignments" based on a Query, which has criteria to filter dates and also to filter 0 and 1 of the checkbox ...
The question is:
How do I put in that SubForm one or more Checkbox to "enable" and "disable", only the criteria of such query? So, toggle, for example, those jobs that are not completed (Checkbox of the query=0) and those that do ...
Is there a way in access, through vba or any other means, which would allow me to either enable or disable criteria in a query based on the value of textbox??
Like i have a query that displays bookings customer has made between two dates, so i made a query and in the starting and ending date fields i get the value from form. In case if the use does not enter ending date, i would like to run the query with starting date only that displays booking made after the starting date.
Currently the starting date criteria is set to a textbox of form, and so is ending date. So if customer selects 1st April 2015 as starting date and 30th April 2015 as ending date the query should display the bookings between 1st and 30th of April. And if the user enters only 1st April in starting date it should display bookings starting from 1st April onward.
I have a very simple query on an accounts form to show a running transaction history.
Identifying from the TransactionID (shown for display purposes only - normally hidden) three or four postings make up one transaction.
Using TransactionID 10 as an example, I'd like to have a sum of total [Credit]-[Debit] and have the query display on one line (either at the top of £1,429, or at the bottom of £16,995) to identify this is in fact one transaction, having three posts.
Transaction 9 will have obviously have one total, as this is a single post.
11 the same as 10, by having one total Transaction Value either at the top of the row or bottom.
I am currently working on a project to develop an access database to manage a roster of calls to clients on a daily basis based on two general criteria:
1. Pre-determined days selected by the client. (e.g. Call Mon, Wed, Fri only. This can change as client requirements change.)
2. Ad-hoc changes based on the client’s circumstances. (e.g. No call from 27/7/2015 to 29/7/ 2015)
I have managed to successfully deal with the second of these with the following expression in a query:
CallToday?: IIf((Date()>=[NoCallFrom] And Date()<=[NoCallTo]),"No","Yes")
However dealing with the first is a little more difficult to work out. I have tried a multivalue lookup field with multiple days selectable, but constructing an IIF query to deal with these multiple values is proving quite a challenge.
I am thinking of using a table with days of the week and a junction table to allow the multi-selection, but I may need constructing the relationships and the query here.
:confused: I have a combo box on a form that is used for data entry that allows the user to pick the date. This is based on a table that includes date, week ending date and month, all very specific for our business. I used a function that says Me.txtBox=ComboBox.Column1 for example. So my combo box would include all the information, and all but the actual date is not visible. I want this to feed into another table that will be used for reporting, and that table has the same fields of date, week ending date and month. I keep getting an error saying that the value selected from the combo box doesn't fit the criteria, and I believe what is going on is that it is taking all the fields instead of just the date. Any suggestions how I can still have the text boxes update if I only have the date in the combo box?:confused:
View 1 Replies View RelatedHi all,
I have a query which populates a form called EditPatientFrm, which asks the user to enter the patient UniqueID in order to pull up the record they want to edit from the TblPatient. However, I want the user to now be able to input just one of three criteria in order to pull up the record to be edited:
UniqueID
ChartNumber
PatientLastName and PatientFirstName (two fields)
since they may not have the UniqueID readily available to them.
Can anyone show me how to do this? I have tried to find answer to this one under queries and forms- no avail. Sure it's a simple thing...
thanks!
vrpres
I sure am having difficulty with this. I'm trying to have the criteria in my form refer to a text box within one of my forms which is the text as I would write it myself within the query design view.
Query's [DEST_ZIP5] criteria = [Forms]![Mainswitchboard]![QueryWizard].[form]![calcDestZip5]
[calcDestZip5]'s value in QueryWizard form = "55422" OR "55343"
Basically, I just want to be able to enter the text string within a form instead of within the query. Sure this can be done!
Oh, and for what it's worth...
If, [calcDestZip5]'s value in QueryWizard form = "55422"
Then the query runs just fine. It's as if the query doesn't want to accept multiple criteria from another source.
TIA
I have a combo named cbogroup. I have a tblGroup with several records (active, non-active, nursery, etc.). One of the records is *ALL*. Using the CboGroup the user can pick any of the records. Howeverr, if they pick the *ALL* record, I want the query to pull up animalID based on all records in the TblGroup. If another record is picked (i.e. nursery), then the query will pull up only animalIDs that are in the 'Nursery'.Can I put a (iff then) in a query in order to differentiate a query based on all group records or a query based on only one record?
View 14 Replies View Related