Passing Criteria To A Query
Dec 6, 2005
Hi
I have recently started using queries to base my forms on. Even for basic forms i am using the query as the record source.
If for example i have a standard query with no criteria e.g. a employee form. How would i then use that query to create a report that filters for example a list of employees for a certain company?
Would i have to create a seperate query to base the report on? because the form when opened will always filter that criteria?
If this is the case, is there a way to keep a query set up with no criteria but when the object being used is opened some code will pass to the query the criteria e.g. a combo box on a form list all the companies and when one is selected it passes this to the query e.g. without the criteria already being in the query?
Hope that makes sense.
thanks
scott
View Replies
ADVERTISEMENT
Apr 11, 2006
I have a combo box in a form which allows users to select a Client Group. One of the choices in the combo box is ALL.
I have some code in a STEP1 query that says
SELECT IIf(Forms![Date Picker].[Client Group]="ALL","'SEDP' Or 'LD' Or 'MH'",Forms![Date Picker].[Client Group]) AS Expr1
FROM MPI_PERSON
GROUP BY IIf(Forms![Date Picker].[Client Group]="ALL","'SEDP' Or 'LD' Or 'MH'",Forms![Date Picker].[Client Group]);
I have a STEP2 query to select all records on the MPI_PERSON table and do some slight processing.
Now, in a STEP3 query, I want to select all records from the STEP2 query where the field [Client Group] satisfies the STEP1 criteria. If the Client Group chosen in the Combo box is one of the existing categories i.e. SEDP or MD or LH, it works but where the ALL has been entered, it is returning no records in the final query.
I have tried pasting the results of the STEP1 query into the criteria box as a test - and in that case it does return all records I want. What do I need to do to get the criteria recognised in the QBE box - do I need to use Eval or something?
Here is the SQL for the final query
SELECT [Find all Clients STEP 2].[Paris ID], [Find all Clients STEP 2].DOB, [Find all Clients STEP 2].[DOB Estimated], [Find all Clients STEP 2].Gender, [Find all Clients STEP 2].[Status From Date], [Find all Clients STEP 2].[Status To Date], [Find all Clients STEP 2].STS_MAIN, [Find all Clients STEP 2].Title, [Find all Clients STEP 2].Name, [Find all Clients STEP 2].NAM_FROM, [Find all Clients STEP 2].NAM_TO, [Find all Clients STEP 2].[Client Group], [Find all Clients STEP 1].Expr1, Len([Expr1]) AS Expr2
FROM [Find all Clients STEP 2], [Find all Clients STEP 1]
GROUP BY [Find all Clients STEP 2].[Paris ID], [Find all Clients STEP 2].DOB, [Find all Clients STEP 2].[DOB Estimated], [Find all Clients STEP 2].Gender, [Find all Clients STEP 2].[Status From Date], [Find all Clients STEP 2].[Status To Date], [Find all Clients STEP 2].STS_MAIN, [Find all Clients STEP 2].Title, [Find all Clients STEP 2].Name, [Find all Clients STEP 2].NAM_FROM, [Find all Clients STEP 2].NAM_TO, [Find all Clients STEP 2].[Client Group], [Find all Clients STEP 1].Expr1, Len([Expr1])
HAVING ((([Find all Clients STEP 2].[Client Group])=[Find all Clients STEP 1].[Expr1]));
Hope there isn't too much "Social Services speak" in there - I can also strip down non-crucial fields if it makes it easier to follow what I'm on about.
View 4 Replies
View Related
Jan 23, 2006
i have a query in design view to which i'd like to pass a criteria value for one field... I have some routine in VBA which creates a string as below:
"SupplierName1" And "SupplierName2" And "SupplierName3"
I am passing this to a textbox on the form exactly as shown above.
I would now like to use this value as criteria input for the query. However, it seems that Access doesn't recognise this....if I write just SupplierName1 in the textbox, the query works fine but as soon as I put in the quotes or else more than one supplier name, it does not work...
any ideas how can i resolve this?
Just FYI, the string of names of suppliers is being generated through string manipulation from a listbox using a value list.
Thanks in advance.
View 3 Replies
View Related
Jan 4, 2006
I'm trying to pass values from a combo box to the criteria for a select query. The first 2 columns of the lstCompany combo box are Serial Number and Company Name. I have tried a number of different ways to no avail. Anyone know?
Thanks,
Beagles
View 1 Replies
View Related
Dec 6, 2007
Hello Everybody,
I have an append query that contains a parameter [PTIdent] under the field named PTID. I would like to be able to set the parameter through VBA from a control on a form eg Form!PTID
I use the following code as normal
Private Sub Command19_Click()
On Error GoTo Err_Command19_Click
Dim stDocName As String
stDocName = "apNewPres"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Command19_Click:
Exit Sub
Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click
End Sub
Is there a way within this code to do this? I realise I could set the form criteria within the query itself. However I wanted to be able to use this query from multiple forms.
Thanks for any help
View 2 Replies
View Related
Jan 30, 2014
I need to pass values of my two check boxes on my Form
Check box US and Check Box Canada
if both checked the criteria would be "UD", "ud',"b","B","us","US","CD","cd"
if Canada check box checked criteria would be "cd","CD"
if US only checked criteria would be UD", "ud',"b","B","us","US"
how to write the criteria statement on the query?this probably would be a complicated iff statement?
View 2 Replies
View Related
Feb 25, 2014
My problem is as follows, i have created a report that calculates the total volume of FSC Materials. The user picks two dates from Calender controls that the report will range from. However the needs have now changed and i am required to make the report filter further based on user input, the problem i'm facing is that i cannot figure out a way to pass values from different variables to the report separate from another here is the code i would usually use to pass data to a query/report:
Code:
Private Sub MonthlyFSC_Click()
Msg = MsgBox("Select the Start and Finish Dates you wish to Query.", , "Start / Finish")
Start = adhDoCalendar()
Finish = adhDoCalendar()
[Code] ....
However i am now trying to do this, but it gives me an error as it is trying to pass the values to one field:
Code:
Msg = MsgBox("Select the Start and Finish Dates you wish to Query.", , "Start / Finish")
Start = adhDoCalendar()
Finish = adhDoCalendar()
sql1 = "[ORDER DATE]<#" & Format(Finish, "MM/DD/YY") & "#"
sql2 = "[ORDER DATE]>#" & Format(Start, "MM/DD/YY") & "#"
[code]....
It is performing incorrectly within the case select and passing the wrong criteria, as it will only display results that meet the default values' criteria. However the date criteria is not be passed either.
View 4 Replies
View Related
Mar 11, 2014
I have a query which contains figures. i have a search form based on this query and need to search using comparison operators such as <10000 or >500.
Is there a way to pass these value to criteria field in query ?
View 14 Replies
View Related
Feb 2, 2014
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]")
View 3 Replies
View Related
Nov 16, 2004
I am using Office/Access 2003. I have created a report that functions fine within access. The report is based on a query requesting the users Representative ID. It functions as expected. We then creayed a web page using the same query. Again this works quite well. The user clicks on the link - Access pops up the criteria dialog asking for the rep id. What our problem is that we are trying to determine where we can put the Rep ID in the page so it will automatically fill in the Criteria Dialog. We see no place in the access page where the criteria can passed. We have the criteria because they use it when they sign in to the sight. I can pass it as a hidden field, an application variable, or even a server variable but how do I get the web page to see it and stuff it into the criteria dialog.
Frustrated in Tucson! Thanks for your help in advance
Greggers
View 2 Replies
View Related
Feb 22, 2005
Not sure which forum this was under, but I figure Access might be it
I have a stored procedure already written which works fine, if I supply the criteria to it before or at manual execution. I want it to use a field on a form in an Access Data Project as it's criteria (as a form is built off the results of the procedure). I can't find any documentation on how to pass criteria to a stored procedure for use in SELECT WHERE statements.
Can somebody point me in the right direction? Here is my stored procedure:
spGetContact:
Code:CREATE PROCEDURE dbo.[spGetContact](@parHomePhone numeric)AS SELECT dbo.tblContactAddress.AddressLine1, dbo.tblContactAddress.AddressLine2, dbo.tblContactAddress.City, dbo.tblContactAddress.Zip, dbo.tblContactAddress.State, dbo.tblContactEmail.PrimaryEmail, dbo.tblContactEmail.SecondaryEmail, dbo.tblContactPhone.HomePhone, dbo.tblContactPhone.BusPhone, dbo.tblContactPhone.CellPhone, dbo.tblContact.ContactID, dbo.tblContact.FirstName, dbo.tblContact.LastName FROM dbo.tblContact INNER JOINdbo.tblContactAddress ON dbo.tblContact.ContactID = dbo.tblContactAddress.ContactID INNER JOINdbo.tblContactEmail ON dbo.tblContact.ContactID = dbo.tblContactEmail.ContactID INNER JOINdbo.tblContactPhone ON dbo.tblContact.ContactID = dbo.tblContactPhone.ContactIDWHERE (dbo.tblContactPhone.HomePhone = @parHomePhone)GO
When i execute this manually I get the dialog prompt to enter the value for @parHomePhone, which is what I want to automatically pull from txtPhone on the frmSearch form.
View 1 Replies
View Related
Feb 2, 2014
This is my passing criteria to query field:
IIf([Forms]![FrmUserSelection]![PricingType]=3,1 Or 2,[Forms]![FrmUserSelection]![PricingType])
My field is Pricing Type and holds records 1 or 2..I would like to pass value from my group option from a form.It works fine if group option has a value 1 or 2 but it displays no records if option value is 3 What is wrong with my code.
View 4 Replies
View Related
Oct 17, 2006
I am trying to pass parameters to my qury thru my combo selection. I keep getting this error "Data type mismatch criteria expression", does anyone have an idea why?
WHERE (((fShiftWorked([tblTimeLog].[timeStart])=[Forms]![frmOperatorWorkDone]![cboShift] Or IsNull([Forms]![frmOperatorWorkDone]![cboShift]))=True));
I have spent so much time onthis already and i am sick of it :mad:
Attached is my db. Please help me out here.
View 2 Replies
View Related
Feb 1, 2006
Guys I need your help/Advice...
In my Access Database I have a query (lets say qry1) and in this query i have 2 fields for start and end date, which is provided by 2 Get functions.
also i have qry2 based on qry1
then qry3 based on qry2
and finally qry4(using sql in code) based on qry3, and non of these 3 queries have the start and end date fields.
now here is the problem: I am trying to set the criteria in qry4 and then open a record set on this query(qry4) to use the data that it pulls out...
strsql = ""
strsql = "SELECT Sum AS AREA_TOTAL " _
& "FROM qry3 " _
& "WHERE ENERGY_AREA like '" & Area & "';"
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(strsql)
but when the last line is executed I get this error message:
Runtime Error '3061':
Too few parameters, expected 2.
i also tried doing this:
strsql = ""
strsql = "SELECT Sum AS AREA_TOTAL " _
& "FROM qry3 " _
& "WHERE ENERGY_AREA like '" & Area & "';"
Set MyDB = CurrentDb
MyDB.QueryDefs("qry4").sql = strsql
Set rst = MyDB.OpenRecordset("qry4")
but when the last line is executed it gives me an error message saying that the query does not exist or the name is not spelled correctly. (Ps. I have created the query and the criteria does update once the Select statement is run in the code!)
again the reason for this is that the query has not been populated as the main query (qry1) needs 'strat date' and 'end date'!
Is there anyway I can pass these 2 parameters to qry4 directly using code? If there is a way then this will definitly work as i tried opening the query manually in the Query window and after I input the 2 dates in the input box the query ran successfully!
I would appreciate any help/suggestion guys, I need to sort this out quickly as i have a deadline... Cheers
View 1 Replies
View Related
May 9, 2006
I have an insert query that works fine when all fields are entered:
INSERT INTO tblMaster (Fname, Lname, Address, Address2)
Values ('Cozmo', 'Kramer', '1Main', '2Main');
BUT...if I have Address2 as blank b/c its an optional field I get a validation rule error...even though there is no validation set up on this field in the table.
INSERT INTO tblMaster (Fname, Lname, Address, Address2)
Values ('Cozmo', 'Kramer', '1Main', '');
Please let me know if you know why this would be.
Thanks,
Kbreiss
View 2 Replies
View Related
Apr 11, 2008
Hi all,
Using Access 2003. Really need some help. Have the below code which executes a SELECT query and passes the results as an excel spreadsheet attached to an email. Problem is that the query store_report has some parameters that need to be passed to it. Is there a way that with my current code I can pass those parameters. If not is there some other commands or code I can use to accomplish this?
DoCmd.SendObject acSendQuery, "store_report", acFormatXLS, _ "john@john.com", "c.w.k@gmail.com", , _ "Store Reports", "Here are your reports.", True
Thaniks in advance.
William K
View 1 Replies
View Related
Jul 31, 2005
My form has a date field (with a default of today-105 days)( and can be updated by the user) which is part of the following query
SELECT dbo_CM_AS_LG.AS_LG_DT, dbo_CM_AS_LG.AS_LG_WO, dbo_CM_EQ_MA.EQ_MA_ID, dbo_CM_EQ_MA.EQ_MA_DE, dbo_CM_EQ_MA.EQ_MA_PS
FROM dbo_CM_AS_LG LEFT JOIN dbo_CM_EQ_MA ON dbo_CM_AS_LG.AS_LG_TP = dbo_CM_EQ_MA.EQ_MA_NO_P
WHERE (((dbo_CM_AS_LG.AS_LG_DT)>[Forms]![F_Q_RISC_SWAPS]![SearchDate]) AND ((dbo_CM_EQ_MA.EQ_MA_DE) Like "RISC/6000*" Or (dbo_CM_EQ_MA.EQ_MA_DE) Like "ibm power pc*"))
ORDER BY dbo_CM_AS_LG.AS_LG_DT;
The query is thr root query for the form, but when the form opens the default date has not been set and I get an error.
Is there a good way to pass a parameter into a query ?
I have tried running the qury using the ON Open Property too, which works until I try and call this form from another one.
I'm sure there's a simple answer, I just can't see the wood for the trees (yet)
Cheers
View 1 Replies
View Related
Oct 26, 2007
I have a form with a combo box that when I select a value I want the hidden value (UserID) passed to a query that would open up a form that is set up like a data sheet. When I try to set up a form that would display the records related to the UserID I end up getting all records and the UserID column replaced with the passed value when I set the control source to = Forms!frmEmployeeInstances!cboShiftEmployees.Colum n(0)
I don’t know if I can pass the value to a query (if the query can even acknowledge the passed value), so any help would be greatly appreciated.
I have the OnClick set to open a form called frmShowInstances via a Macro but I could never get the Where Condition to work either.
View 6 Replies
View Related
Jan 16, 2006
I have built a function which passes values into the criteria of a saved query. It has worked very well until I recently built a query builder based on this same concept. The user can select from a list of operators "<=", ">=", ">", "<", "=".
Example, the function will pass into the criteria the following:
<150
Unfortunately, I have exhausted all avenues for passing the "<" less than or greater than signs into the criteria. The Access SQL forces an equals sign before the passed in criteria and thus treats the "<150" as the entire criteria.
I have overcome no criteria by defaulting the function value to pass in the wildcard "*" and putting "Like" in the criteria.
Has anyone every handled a "variable" operator in a saved query in Access? :confused:
I am aware that I can change the QueryDef, use temp tables, and do several other longer methods, but the success of this concept will reduce an large amount of coding effort as well as make the software much easier to transfer knowledge on.
This overall method is really a great way to reuse criteria based on a form without hard coding.
Thank you in advance for your help,
Dave
View 3 Replies
View Related
Jun 21, 2006
When I have my form I have an unbound control called rtdDate. In rtdDate's AfterUpdateEvent I have it run a append query. Within the append query I have the following:
RTD Date: forms!masterListQ!rtdDate
Yet it keeps coming up blank when you run the query. Its like its not finding the value of it. I'm sure you can tell I'm pretty much an amateur at database building but I thought this would be pretty easy.
What am I doing wrong?
View 4 Replies
View Related
Aug 8, 2006
I currently have a table showing activity for multiple staff and their availability throughout the day in 30 minutes segments.
I am currently trying to pull the information on who is working by 30 minute timeslice, but as the information is held in a different field for ech period, it is proving difficult.
My thought was to make a query rounding the current time to the nearest hour/half hour and use this to choose the field, but I don't know how to make a query which will allow me to pass a variable (Field name) into the Select query?
can anyone help on this, or have any other ideas?
Thanks
Andrew:
View 4 Replies
View Related
Feb 7, 2007
OK, I am trying to build sql strings from a form buy the user selecting criteria from boxes on a form.
I was given some code which I have adapted very slighty
Dim strCrit As String
If IsNull(Me.server) Then
' proceed
Else
strCrit = strCrit & " And [Server type] = '" & Me.server & "'"
End If
If IsNull(Me.RAM) Then
' proceed
Else
strCrit = strCrit & " And [Ram] = " & Me.RAM
End If
DoCmd.OpenReport "Your Results", , acPreview, strCrit
End Sub
however when I run this I get the following error
http://i145.photobucket.com/albums/r232/stuartkeith/errorsyntax.jpg
or trying to edit the code this error
http://i145.photobucket.com/albums/r232/stuartkeith/error.jpg
how do I pass the built SQL string directly into the query defs?
the strcrit when displayed looks like this
http://i145.photobucket.com/albums/r232/stuartkeith/stricrit.jpg
Thanks
View 1 Replies
View Related
Apr 9, 2007
Hi All,
I wonder if anyone can help me? I am at the stage now of building a query in design view. Rather than using a dynamic parameter field to capture a range of dates (between...[InputDate] And [InputDate]), I have created a text box in a form and want to pass the contents to my query. I have got this to work providing the variable that is passed is 'text'. I need to pass two dates though. When I put paths to the forms textbox in the 'Between' statement above, it just doesn't return any records. I think Access sees these text boxes as 'text' rather than 'dates'. I don't know how to change it so Access sees these as dates. Any ideas?
View 3 Replies
View Related
Sep 3, 2007
I know this is probably a basic question- but Im not finding a clear answer here.
Basically- I have a value that I want to select from a drop down box on a form (not created yet). That value will get inserted into my query for a calculation I am doing. The form will pop up the results of the query in a table/dataset.
How do I designate the variable in the query that is being inserted from the form? I am using Access 2002- is there a way to visually perform this task (ie- drag/drop type thing)?
Thanks guys!
View 6 Replies
View Related
Nov 13, 2007
Dear All, I have a problem which I need to solve and am in need of a clue!
I have a table which contains lots of line items relating to quotes. Each quote usually contains three line items and is prefixed in all cases with a number which relates to number of users. For example
5 5 User Software Details Price Cost Note
in the table there are up to 100 users and each has line 3 items. I automate quote generation by using a form. In the form a quote reference is generated and a text box for the number of users. I have a command button which then, based on the value of users text box, selects from the line items table and sends the records to my TblQuotes.
My Tbl quotes is exactly the same structure as my line items table except it also has a field for the quote reference which is in the form.
Where I am stuck is I cant seem to get Access to send the quote reference into the TblQuotes as part of the append query.
Please help! :confused:
View 9 Replies
View Related
Sep 16, 2005
Ok, i have a form in wich i want to set the parameters to a query.
On the search button it will open another form to display the data.
i used this code stLinkCriteria = "[ID]=" & Me![id] And "[status]=" & "'" & Me![sta] & "'
the 1st value for status is "active"
it returns a msgbox asking the value of the parameter and a field to input the data.
I can't understant whats wrong....
View 4 Replies
View Related
