Hello.
I'm trying to build a query criteria for a tab control with three different tabs. They are all using the same subform in each page. I want to be able to set the criteria for one specific field to the current tab control page that is selected. Does anyone know how to do this? I wasn't able to find it on the forums...
Thanks a bunch!
I'm working on a report called Open Orders and when the database loads, it takes you to a Navigation Form. You make some selections mostly from combo boxes, then click run report which runs a query then launches the report.
I want the user to be able to click a check box called "Ready Only". If the checkbox = True, then I would like the field "Ready Pieces" in the query to have the criteria ">0". If the checkbox = false, I want that field to show all values (*).
I have no problem setting the criteria of a query field to equal that of a combo box value (Warehouse Like ([Forms]![Process Form]![Warehouse] & "*") but have problems when the criteria isn't the exact same as the value of the control.
Things I have tried to no avail: Putting a Iif statement in the query criteria: gives an error that criteria is too complex Creating an invisible text box whose value is determined by the checkbox to ">0" or "" then basing the Ready Pieces criteria equal to this....doesn't work Trying to use the DoCmd.RunSQL with my SQL code that changes via VBA when the checkbox is changed...Get an error and the SQL doesn't run
I have a totals query that provides an avg for each month. i'd like to be able to use a text box control (named "Date") on a form (named "Report Runner") to show only a certain month and it's avg.
I tried using this as criteria on the "MonthGroupPMC" field:
Code:
Format([Forms]![Report Runner]![Date], "yyyy-mm")
but the results came up blank.
how can i filter the results of this query to show only one month, specified by the [Forms]![Report Runner]![Date] control?
I want the Query Criteria to pull its value from a control on a form.The form control either has data or is null. (My problems occur when the form control is Null). The field in the table either has data, is null or is blank.
Code:
=IIf(IsNull([Forms]![FormName]![FormControl]),"" Or Is Null,[Forms]![FormName]![FormControl])
This works for the records with fields that are blank.
Code:
=IIf(IsNull([Forms]![FormName]![FormControl]),"",[Forms]![FormName]![FormControl])
This works for the records with fields that have data.
Code:
Like IIf(IsNull([Forms]![FormName]![FormControl]),"*",[Forms]![FormName]![FormControl])
This works for the records with fields that are Null or Blank if i drop the iif function but then i lose the ability to pull criteria data from the form control.
Code:
"" Or Is Null
I need to pass a criteria to a query from my option group control to my query.
It contains three options 1,2 and 3.
If option 3 then Pricing Type 1 and 2
How do I make the code below working?
IIf([Forms]![FrmUserSelection]![PricingType] Like 3,([dbo_AGPricingDiscounts].[PricingType])="2" Or ([dbo_AGPricingDiscounts].[PricingType])="1",",[Forms]![FrmUserSelection]![PricingType]")
I need to set calendar control as criteria for date in VBA query. I cannot find Calendar control in ActiveX tools in Acc 2010. Where is it?
Date picker seams like very slick and simple solution but I can see it only in database. As it might be solution to my problem, can date picker be used on a form as criteria for VBA query?
Hi all. Been away for some time due to the necessity of working two full-time jobs and maintaining house and family. I'm now back to one job and have a bit of time on my hands so I'm making a little db for myself.
I keep going to the video store and renting movies that I already have so I decided to input them into a db and play with it to resharpen my dull skills.
Very small and simple. I have a query that's fed by two tables. This is what I want to feed one form that will display in datasheet view when a selection is made from a cbo on another form. Not a problem. What I want to do is have the one query and one form for two or more cbo controls, each passing a different criteria. For example, in cboGenre when Action is chosen, the datasheet view shows all the movies with that genre. Then when another is chosen, it requeries with those (the requerying part isn't a problem). Then, when PG-13 is chosen from cboRating, the datasheet shows all the movies with that rating.
I can do this when it comes to passing where conditions when opening a report, but a report isn't really the cleanest way to go.
Hope everyone has had a great year (at least as long as I've been here) and look forward to your responses and also browsing the information here again.
Jim
I have form with a number of of combo boxes that filter a query that populates a list box. It's used to track inventory. I want to add a toggle button that when press will filter my query so that it shows only items with a value > 0 in the quantity field. And when not pressed it won't filter by that field at all.
View 14 Replies View RelatedHi All. I have a query that is based on 1 table. I am using a form with a calendar control for the users to select the date range. In my table the date format is mm/dd/yyyy. My calendar control is pulling the date format mm/dd/yyyy I need my query to only look at the mm/dd part of the field. In my criteria field inc_date I have Between [Forms]![frm_LookUpEnquiry]![txtSelectDate] And [Forms]![frm_LookUpEnquiry]![txtDate2] I have tried the datepart but Im not sure I am doing it right. Basicly Im not sure how to parse out the date to only look at the mm/dd. Or would this be better done in a SQL query Any Idea's for this would be very appreciated. Im kinda in a bind on this one and its the last thing I have to do to finish this database.
Thanks for all you assitance in advance
I learned the criteria on this forum
Forms!FormName!Control or Forms!FormName!Control Is Null
It enables me to leave the text box on a search form blank to return all records. This seems to work but has a very odd behaviour.
In design view, I typed the criteria for 3 fields and saved the query, but when I reopened it, the criteria added 3 NEW COLUMNS and spread over 8 ROWS (as in the image attached.)
The SQL was even unreadable:
SELECT Table1.ID, Table1.FldA, Table1.FldB, Table1.FldC
FROM Table1
WHERE (((Table1.FldA)=[Forms]![Form1]![txtA]) AND ((Table1.FldB)=[Forms]![Form1]![txtB]) AND ((Table1.FldC)=[Forms]![Form1]![txtC])) OR (((Table1.FldB)=[Forms]![Form1]![txtB]) AND ((Table1.FldC)=[Forms]![Form1]![txtC]) AND (([Forms]![Form1]![txtA]) Is Null)) OR (((Table1.FldA)=[Forms]![Form1]![txtA]) AND ((Table1.FldC)=[Forms]![Form1]![txtC]) AND (([Forms]![Form1]![txtB]) Is Null)) OR (((Table1.FldC)=[Forms]![Form1]![txtC]) AND (([Forms]![Form1]![txtA]) Is Null) AND (([Forms]![Form1]![txtB]) Is Null)) OR (((Table1.FldA)=[Forms]![Form1]![txtA]) AND ((Table1.FldB)=[Forms]![Form1]![txtB]) AND (([Forms]![Form1]![txtC]) Is Null)) OR (((Table1.FldB)=[Forms]![Form1]![txtB]) AND (([Forms]![Form1]![txtA]) Is Null) AND (([Forms]![Form1]![txtC]) Is Null)) OR (((Table1.FldA)=[Forms]![Form1]![txtA]) AND (([Forms]![Form1]![txtB]) Is Null) AND (([Forms]![Form1]![txtC]) Is Null)) OR ((([Forms]![Form1]![txtA]) Is Null) AND (([Forms]![Form1]![txtB]) Is Null) AND (([Forms]![Form1]![txtC]) Is Null));
I don't know how to add the criteria for the 4th field.
Can someone help?
Thanks
I have a report that is filtered like Year_ = "2014/15".
Say for example on this report it contains two text boxes:
Text box 1 has a control source of "March_T"
Text box 2 has a control source of "Aprril_T"
How can I make text box 1 be filtered by Year_ = "2014/15" and text box 2 be filtered by Year_ = "2015/16"?
My form is based on a table (SOP TBL) and has several text boxes with control sources of the records in the table. I need to add a criteria to one field (SOP Number) so that only current versions of the number show up. I have the SQL for a query that I use in another form for a combo box as shown below. What this criteria does is find the version number which is the last 2 digits of the SOP Number and displays only that record for the SOP Number in the combo box. How can I apply this code to the text box with the control source directly from the table so only the current records are displayed on the form?
Code:
In (select max([SOP Number]) from [SOP TBL] group by left([SOP Number], len([SOP Number]) - 2))
I have a form (named frmAddSession) with a combo box (named Band_Name) and lets say i have it displaying the band "Band A"
I have a query where the criteria is [Forms]![frmAddSession]![Band_Name]. when i run the query the results are blank. but if i type ="Band A" it works just fine.
why won't my reference to the combo box on the form work?
In a series of deductions to try and figure this out, my form only has the one combo box, and my query is based on only one table and only has the one field.
Private Sub btncard_Click()
Dim stLinkCriteria As String
DoCmd.RunCommand acCmdSaveRecord
stLinkCriteria = "[jobref]=" & "'" & Me![jobref] & "'"
DoCmd.Close
DoCmd.OpenForm "k_job_card", , , stLinkCriteria
End Sub
this button code opens the k_job_card form.
the form opens and says 'Record 1 of 1, (Filtered)
i have a control on the form that opens.. and i am simply trying to assign a default value to that control..
it is a textbox.. for my jobtype field (text datatype)..
is there any particular reason why i cannot assign a default value to a control if it has been placed on a form that has been opened using a form filter?
this is how the Form Data tab is set at the moment..
I'm setting up some analytical reports and i'm having a hard time with some date criteria.
I've gotten monthly averages already and what i want to do is take those monthly averages, and average them throughout the year. (the year being from the specified date on a form control back one year)
Month([Forms]![Report Runner]![Date1]) and Year([Forms]![Report Runner]![Date1]) worked great for specifying a certain month in a certain year, but what i'd like is to show all the monthly numbers for every month going back one from [Forms]![ReportRunner]![Date1].
I tried this:
Code:
>(Year([Forms]![Report Runner]![Date1])) And <((Year([Forms]![Report Runner]![Date1]))-1)
and it didnt work. How to do this?
I have a form with a (large) number of controls which acts as a kind of read-only dashboard of data. The data is split across a number of tables (4, for now, but may grow) I've created a query which amalgamates all of the data from the tables into a single dataset and set that as the RecordSource (Snapshot) of the form. I now want to bind each of the controls to a particular field returned by that query.
However - I have a combobox (same form) which allows users to select a date and I need the controls to update with the relevant data for that date whenever the combobox is changed. The query already returns values for each field over a range of dates so the data is available within the form's RecordSource - I just don't know how to include the date criteria, as specified by the combobox, when binding each individual control?
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%
I'm trying to pull a date from a table into a text box on a form tab control using DLookup and I just can't figure out what is wrong with my DLookup expression:
=DLookUp("DateOrdered","tDateOrdered","PrNumber=" & [PrNumber]) ----- (DateOrdered is short date, PrNumber is text, db is split Access 2013)
Whats missing in this expression? I've tried every criteria variant I could find but to no avail.
Hi
I am building a stock control database, which uses a barcode reader to input data into the fields.. (stored in a product table)
For a sale the item is scanned.. and the details are displayed. However is there any way which the same form can be used to have multiple text boxes with the same names..
So in essence I need to be able to view more than one sold item on one form.. is it possible to have rows of different transactions??
Hope that makes sense.. any assistance would be great!!
Sunshine!!
I have an inventory transaction file that has quantity on hand as a calculated field. I am trying to add this unbound field to a report using a query but I can't figure out how to add my quantity on hand field to the query since it is a calculated field and not a bound control.
Any help would be appreciated.
Thanks
darrrellx
I added a new field to one of my tables and query, but when I try to add a control for that field into my form it is not recognising it and the field isn't appearing in the sources for the form, even though the table is sourced to the form? Or am I going to have to do the usual and redo the entire form because I made a minor adjustment?
View 1 Replies View RelatedI have a form with a tab control, inside the tab control I placed an ActiveX control (Microsoft Web Browser). These are at the bottom of the form.
Everything displays fine if the entire form fit on the screen but if the form is too long and I scroll down the browser control is getting obscured by the tab control and getting chopped off (the contents are covered). It is as if the browser control is staying in space where it was and moving behind the tab control as I scroll.
This problem does not occur if I place the browser control directly on the form. Also I note that the browser control is sitting correctly within the tab control.
I have been through all the settings and properties of both controls and haven't been able to fix it. I searched all over the web but no one has previously stumbled across this one by the look of it.
See the attached image ...
I have table that I had to add a new field to which we update with a form. I tried to add a control for the new field but the field does not show up in the list for the control source. I am trying to add a list box to the form with a blank and 5 options.
I have attached screen shots of the table design and the form. The table has the field in datasheet view and I have manually entered a few entries in it but it still will not show in the control source for the form control. The top section of the form is where we enter and select the data for the new records. The bottom section (circled in red) autofills the matching record, from separate tables, for updating with the new entries.
I have added form controls for modified fields in the past so I am confused about why this is happening.
basically, im making a stock control database
i've made an update query but its not exactly what i wanted
is there a way to make the criteria equal a particular form field because i dont want the user to type in the stock ref in a parameter query.
also, how do i make it so no orders can be made on stock which has stock levels of 0 and have a message saying it is out of stock
at the moment, i put >0 in the criteria underneath no in stock field in the update query
i hope i made it clear because im not sure how to explain it
thanks for any help
the jayman
I'm currently using a Form with combo boxes to select query criteria. On the same form I have a control button which opens up a Report which is based on information generated by the underlying query. This is a fairly classic way to generate Report contents, and it works just fine when something is selected in each combo box.
The problem arises when I want to leave one or more of the combo boxes blank (i.e not select anything from the drop down list), basically to broaden the query output to include all entries for that particular field in the Table. The Report will open OK but have no content.
Is there any way around this? I'm using MS Access 97. Any assistance offered is very much appreciated.
SuperTek