Between [Forms]![Benefits]![date1] And [Forms]![Benefits]![date2]
(Benefits being the form). If I enter date range 01/01/2005 to 01/05/2005 I receive all data from 01 January to 01 May - but including different years i.e 2002, 2003, 2004 etc.
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.
Anyone know an efficient way of setting the value of a textbox to the result of an sql query?
I am using the following, but it seems to be slow when populating a large form:
Make.RowSource = "SELECT BarcodeDATA.Make FROM (Customers RIGHT JOIN CustomerSales ON Customers.CustomerID = CustomerSales.CustomerID) LEFT JOIN BarcodeDATA ON CustomerSales.ItemNum = BarcodeDATA.ItemNum WHERE (((CustomerSales.ItemNum)=[Forms]![FormCheckConsignmentStatus]![ItemNum])); "
Hello, I am working on a database to analyze weekly purchase results. With the goal being to determine the response rates for each promotion source that was used.
I have a table that contains PromoName, PromoCode, PromoQuantity, PromoSource and PromoDropDate. In another table I have imported 8 weeks worth of results and use the fields PromoCode and DatePurchased. An additional field called TransactionValue is auto assigned to the number value of 1.
I was able to create a query that looked at all the records with the same PromoDropDate and PromoSource field and then summed the values in the TransactionValue.
I would like to be able to see the results by week for 10 weeks but am not sure how to proceed. For example if the PromoDropDate is 1/10/08 I could look at the results based on DatePurchased being between 1/10/08 and 1/16/08, and then for 9 week periods after that.
I welcome any and all suggestions on the best way to approach this. thanks.
I have a query that has 4 fields that are text fields in an external database. I have text boxes set up on a form which contain either null or >0 (depending on an option button selected). I want to pass that >0 to one field in the query and null to the other three. I have the following as criteria in my query: [Forms]![frmSelectPayment]![txt2ndPayment] (same one for each of the 4 fields except the form field name changes)
Below is the code that sets the text boxes that are sent to the query: Select Case [FraSelectPayment] 'depending on which pmt being pulled Case 1 '6 payment plan only stDocName = "qryGetPlusARImport" Select Case [FraChoose] Case 1 Me.txt2ndPayment = ">0" Me.txt3rdPayment = Null Me.txt5thPayment = Null Me.txt6thPayment = Null Case 2 Me.txt2ndPayment = Null Me.txt3rdPayment = ">0" Me.txt5thPayment = Null Me.txt6thPayment = Null Case 3 Me.txt2ndPayment = Null Me.txt3rdPayment = Null Me.txt5thPayment = ">0" Me.txt6thPayment = Null Case 4 Me.txt2ndPayment = Null Me.txt3rdPayment = Null Me.txt5thPayment = Null Me.txt6thPayment = ">0" Case Else MsgBox "Please select a payment" FraChoose.SetFocus Exit Sub End Select
DoCmd.OpenQuery stDocName, acNormal, acEdit
If I run the query alone I leave all fields blank and put >0 on the one I want to search by it works fine (695 records returned) but when I run it through code, nothing is returned. I really hope this makes sense to someone! Can anyone see what I am doing wrong? Thanks
having trouble using a text box on a form to set the search criteria for a particular field within a query.
Ideally i would like to enter the criteria in a textr box then click a button which sets the criteria and opens the query results in a report,
I have designed the query but cant which works if you go into the design and enter the criteria. the problem lies with getting the text box on teh form to set teh criteria.
If i use the expression builder to set the criteria to the same value of the "text" within the "text box" on said form the following happens,
If i open the query itself it opens a small window and displays the "expression" that i entered in the criteia box, above the data entry. the query does work this way but dont understand why the expression is displayed??? The text box on the form also becomes locked, not allowing data to be entered.
I have tried to link the query direct to the text box. also tried creating a table which has data entered via form then linking the query criteria to a field in the table.
Could some one point me in the right direction please
I've set up a form with a button to open a report based on the current name on the form. The idea is that as you look through the different pages on the form you can open a report for whatever one you're on and print it.
In my query I have a name field where I put the criteria: Forms!Formname.Textboxname
By clicking the button on my form I'm able to generate a report based on the name that appears in that textbox. It works great when I initially put it in but if I close the form (or query, or report) and open it back up it is blank.
Is this even possible with a text box? It seems like it when I open it, it has nothing to go off and that's why it's blank. I just don't know how to fix that.
I need some syntax in setting 2 criterias for a DLookup query.
I've attached a sample db with 2 tables: Main & Timesheet
I need a "combo" query showing (on the same line) all Qty for Transcodes N, 1 & 2 where the Staff number and TSNum is the same.
I'm sure about the logic but the syntax is letting me down.
I can pull in 1 of the criteria E.g.:
OT1: DLookUp("Qty","ExOT1","[Staff] =" & [Staff])
But can for the life of me not script the second criteria in.
So in my result of ExCombo I'm getting Staff 11 showing 4 under OT1 while I know the result does not apply for TSNum 29832 as indicated hence the need for the 2nd criteria.
Since I have to change some of the values to text inside the query it might be best to have a look at the attached db rather than just suggesting the correct syntax .
I have this criteria which should collect a date range (cboDate and cboDate2), it works well in collecting the date range if i put separate days (like 6/17/2006 and 7/18/2006, it'll collect the data matching those dates), but if i put the same day, say i want to get all the data for 6/17/2006. And cboDate and cboDate2 are both 6/17/2006. With this code, nothing comes up. Can you help me?
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
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.
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 am struggling writing criteria that is based off of multiple combo boxes in a search form. I basically have a form setup in several pages the first page being a search page. This page consists of three combo boxes which are populated from the tables. These three boxes are all related. The way the search is setup at the moment is if the combo boxes are Null then they report all the data in the fields but if something is selected then the query gets requeried and displays the data based on that criteria.
What I need to have it do is if it is Null report everything but if something is selected in the first box that selection will also be apart of the second criteria and third criteria.
What I have written at this time is; First Box: IIf(IsNull([Forms]![FormName]![Combo1]),[Data1],[Forms]![FormName]![Combo1]) Second Box: IIf(IsNull([Forms]![FormName]![Combo2]),[Data2],[Forms]![FormName]![Combo2] ) Third Box: IIf(IsNull([Forms]![FormName]![Combo3]),[Data3],[Forms]![FormName]![Combo3] )
My intentions are too write something that allows the criteria to use the other values in each combo box. The problem I have is it keeps spitting an error out at me which says I have the wrong number of arguments.
Here is what I am trying to accompolish.
PsuedoCode:
If (Combo1 =Null) { Select all values of Data1 Else { Select all values of Data1 with match value of Combo1 } If ( Combo2 = Null) { If( Combo1= Null){ Select all values of Data2 } } Else{ Select all values of Data2 with matching value of Combo1 and Combo2 } }
I'm trying to build a select query where it prompts the user for a few parameters. I've been having issues where people would misspell the vendor name and nothing pops up. I changed the vendor to Like [Vendor Name] & "*" but now I can't use the parameter of PO# without everything popping up. I have attached the SQL view. Thank you for any suggestions.
SELECT [Main Payment].[Batch#], [Main Payment].VendorName, [Main Payment].VoucherPrefix, [FY08 PAYMENT detail].VoucherNumber, [Main Payment].VoucherSuffix, [FY08 PAYMENT detail].Vchline1, [FY08 PAYMENT detail].PONo, [FY08 PAYMENT detail].InvoiceDate, [FY08 PAYMENT detail].InvoiceID, [FY08 PAYMENT detail].Amount FROM [Main Payment] INNER JOIN [FY08 PAYMENT detail] ON [Main Payment].VoucherNumber = [FY08 PAYMENT detail].VoucherNumber WHERE ((([FY08 PAYMENT detail].PONo)=[Enter PO#])) OR ((([Main Payment].[Batch#])=[Enter Batch #])) OR ((([FY08 PAYMENT detail].VoucherNumber)=[Enter Voucher #])) OR ((([Main Payment].VendorName) Like [Enter Vendor Name] & "*"));
I am developing an Event Management DB for business training courses. I have an attendees table with a link to CompanyNames. I wish to be able to produce an attendees report based on the CompanyName ie all attendees from the selected company. I have done this by using a dropdown of companynames on a Criteria selection form. This works well. What I would also like to do is print the same list without the companyName criteria. I am aware I could make another query but was hoping that maybe there is a way of setting up the criteria in the companyName field so that it could accept either a company name or ALL company names.
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.
Need some help trying to filter out some data so it can be deleted from the main table so updated data can be pasted into it that table.
Table name is "dbo_VG_PropertyValues"
AppID - Number DNIS - Text (This is the column i need filtered) PropertyName - Text ProperetyValue - Text
I had a query where i would just type in the 3-4 DNIS numbers that i needed to search for by using "11111" or "22222" or... etc... in the simple query view under criteria..
I'm now getting lists of 50 + numbers i need to do searches for, delete and replace with updated data. I can't run a string of "or" statements on 50+ numbers, it won't allow it. I created a table called 'DNISList" with the 50+ numbers i need to search for out of the "dbo_VG_PropertyValues" table. In the simple query view i linked on the DNIS number from both tables and set the join property to show all the records that matched. Hey it looks good, so far, i got the results i wanted but can't delete anything.
Is there anyway in the criteria field under the column DNIS from "dbo_VG_PropertyValues" where i can so a search on that table without linking the tables? I hope I'm making sense. You know something like:
Hi 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...
get the data from two tables on the basis of criteria...
I want to select the whole table1 which has 6 fields including Emp_id...
I want to select the single field from table2. field name is "Username" from second table2. will select the username on the basis of Emp_ID becaue both tables has same emp_ID.
It should be in order like. Emp id, Username, Startdate, Enddate, Hours, trainingNames.....
I have a combo box on my form that passes criteria to a simple select query. There are four possible selections to make from the combo box. For some reason, when I select the first option on the list the query runs perfectly. However, if I select the second, third or fourth option from the combo box, the query returns no records, even though I know there are records in my table which should be returned.
I am fairly new to Access and I would like to create a form to allow users to create their own query. I would like to allow users to select multiple fields (perhaps with checkboxes?) from all possible fields in a table to return either all data from that field or narrow their search by inputting certain criteria or choosing from a drop down into a text box. Is this possible in Access and any detailed specifics on how to achieve this?
I am building a form that uses list box selections on the form to make a temporary query and open the results in Excel.
It mostly works in just trying to make it more functional.
Currently the listbox that contains the first and last names of the personnel also has a unique shorttext 'number' as a primary key for the table (bad choice in my opinion but I didn't design the database I just have to work with it).
The short text primary key is hidden by an option given in the listbox wizard that let me set that field width to zero but still search on it. This is how I currently build the query's where clause when it involves names. I search the primary key.
I would like to update the names list box based upon which cities and locations are selected (both are multi-select).
Do I need to change how I make the "where" clause to use first and last names or an I use the same query and just hide the USER_ID
The query looks like this
Code:
SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO ORDER BY t_asset_personnel.LAST_NAME;
And the list box wizard hides USER_PHY_ID
By setting the width to zero
If I make my own query in the City_After_Update()
Can I do something like this
Code:
SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID .fieldWidth(0)FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO WHERE [forms]![myform]![citylist].[itemsSelected]'obviously more code is needed here this is just for conceptual illustration ORDER BY t_asset_personnel.LAST_NAME;
How can I get a Query Criteria To Select All Records or specific records in query design section.
I have a table that shows many departments with credit card transactions. I like to run a query to see specific department, or have an option to see all the departments when the query is run.
I have a problem in setting the criteria of queries.
I have two inspection methods: ABC and XYZ. Every two years, ABC will be carried out, and all other years, XYZ will be performed. However, information to which factory XYZ is performed is required. If ABC is carried out, then the information can be simply "N.A".
But when I do the query, I therefore need to add an extra field which will show "N.A" if ABC is performed, or the factory name if XYZ is performed (factory name can be retrieved from another table).
I'm ok with Parameter Queries now, but I'm stuggling for the certeria to select say the top 20 records for a list. This would be based on a number, say amount spent.
I work for a company which have a load of cd's which i've put into a database.
the database consists of a table which lists the contents, date created, department etc.
I've created a form where users can select (using combo box's) criteria to search the database.
It works well if the user selects relevant data from each combo box but i have now added a "select all" function to each combo box incase the user doesn't know the specific criteria.
This is where the trouble starts. I cant work out how to create the query so that if the combo box is on "select all" how it will show all records for that particular field.
I have tried to use an IIF function for each field in the query but i always get errors.
