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
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
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.
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.
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
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?
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.
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 ?
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]")
Hey folks,
Not sure if this is the best forum but decided to put it here anyway.
I have a Access query that reads:
SELECT bleh
FROM blah
WHERE something > 10
Then in Excel I pull the data across using:
Set qdf = db.QueryDefs(qryName)
Set rs = qdf.OpenRecordset
and paste the data using
ws1.Range("IV1").End(xlToLeft).Offset(0, 2).CopyFromRecordset rs
I now want to change the query so the where statement reads
WHERE something > [amt]
So the question is how do I pass the value for the parameter value for [amt] from excel to access?
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
I'm having a rough time trying to figure out how to pass a date to an SQL statement that Excel VBA macro will run. The date is in a cell (A1) formatted as 'm/d/yyyy'. Let's say it's 2/1/2014. I want to run an SQL statement that retrieves data from a table where a field is greater than 'A1'. The table field is a date/time field and has values formatted as 'mm/dd/yyyy'.
I've tried various syntax on the Where but cannot get it to work.
sd = Range("A1")
SELECT [tn].[Date Submitted]
FROM[tn]
WHERE tn.[Date Submitted] > """ & sd & """
This results in the following where clause that does not work.
WHERE tn.[Date Submitted] > "2/1/2014"
I'm running a VBA routine in Excel that loops through a lot of data. As part of the process, I'd like to pass a variable from Excel to an Access database that is open and have it run a query based on that value.
View 4 Replies View RelatedNot 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.
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.
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.
Hi,
I hope someone will be able to help me with this one, I have a simple access query set-up which references to a table containing information about processed orders (From a ficticious company). I am trying to set up a query that will display all order placed within the last week. I have edited the criteria on the date filed to
Between Now() And Now()-"7"
Now this sort of works in that it filters the data, but it doesn't filter it by the day but by the year. i.e. Any order played within the last 7 years rather than the last 7 days. Any help much appreciated.
Sorry for being such a n00b,
Thanks for looking!
cheers
xRes
Sorry if this is a very elementary question...
My boss dumped data into an Access database. I set up an ODBC connection so I could pull the data to Excel using MS Query. I have no problem pulling data, until I try to put criteria on a numerical field in the query, then it throws an error.
In MS Query, it's a simple 2 table set up. With 1 join. I am pulling 1 text field and 3 number fields.
One of the number fields is actually a date, but shows up as a number, so the criteria I want is >20060801 .
The error I get is: Internal OLE Automation error ... the help button only says it is a problem with the driver.
I removed and reset up my ODBC connection and tried changing the format of that number date field to date/time field.
Any ideas?
Thanks!! Christen
I'm trying to pass data from a macro using DDE to Access (it must use DDE), however, I can't seem to get the data to a Module, Macro or Form (either one will do).
Is there a way to pass arguments to any of these three? I'd imagine the easiest would be to run a macro like DDEExecute channel1, "[RunMacro MyMacro]" but how can I pass the data specifically do the Macro?
I use an Excel interface to retrieve data from an Access file that has approximately 1 million records. I use an MS Query from Excel to run an Access Query to retrieve the data.
I have 16 "Criteria" fields (columns) in Excel that I change with drop-downs. Each criteria column has 2 to 9 values set; a total of 75 values. I pass these criteria fields to Access via a table link in Access.
The criteria of the fields in the query that is run in Access to pull the data based on the table link is updated by using the Values In A List (IN) option.
When I refresh this query in Excel, it pulls the data fine, but may take 10 minutes to run. (I am using MS Office 2003, 1Gig memory). I have limited VBA knowledge. Is there a better/faster way to pull this data?
I am looking to run a query in Access that will limit the number of records for each criteria.
example:
>10 years count 300
1-3 years count 100
4-6 years count 200
6-10 years count 250
can limit the amount in one query or have to do the criteria separate?
Hi - I am looking for some help understanding the instructions in the msdn article on how to pass a parameter from a DAP. Please refer to the article: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k2/html/odc_PassParam.asp
I was able to follow the instructions until Page 2 of this article...where it says: "Add the functions to write, read and delete cookies in a separate global script block."
I do not understand how to create a global Script block. Do I just copy and paste this code right below the 1st code on Page 2? Or, is there a way to create a global script block separately and add this code there?
I am unable to find that much Online resources on DAP as other Access topics. I thought posting my question here would be the last resort for assistance! :confused:
I am writing a small database using MS Access 2007. I have in it a function for running queries and printing reports. Printing works just fine if any one of the multiple query criteria is give.
I also want to trap errors and present a message if the user clicks the "Print" button without a query criteria. At the moment I only get a run-time syntax error message which has a "debug" button for accessing the entire code. I want a message that will say "There was no search criteria".
This is the code for printing:
Code:
Private Sub cmdPrint_Click()
Dim varWhere As Variant
varWhere = BuildFilter
' Update the record source
Me.RecordSource = "SELECT * FROM q_Vehicles " & varWhere
[code]....
I have a table that has one field and I want to use this field as a query criteria . when i click on CRITERIA & BUILD, Access lets me select the table field and shows [tblBillRun]![Bill_Run] for the criteria but when I click RUN, it prompts me to ENTER PARAMETER VALUE. What am i doing wrong?
View 3 Replies View RelatedI have a form design where to enter a new "system" (what we call a new group of members) you have to specify the representative (the member that will represent the system).
If the system is new AND the representative is new, then both need to be entered. Typically this will be done through the "New System" form that has a combo box for you to choose the representative (but the representative won't be there!). If the representative is new as well, then the user clicks "Add New Rep" which is located next to the combo box.
This is where things get tricky, I'd like to use only 1 form to load a new rep. My idea was to use an openarg on the docmd.openform method that specifies what the control should be.
Code:
DoCmd.OpenForm "RepresentativeAddNew_Form", , , , , , "System_ID|" & Me.System_ID
That is the code that is called when the user click "Add new rep" and in the on load event I have this code:
Code:
'Use this version if the ID is text
Dim x As Variant
Dim strControl As String
Dim strID As Long
'If parameters exist, use them
[Code] ....
My goal was to pass the value to the combobox and turn it off, that way they can't change anything about it. This was nice because if the openargs is null (there were none passed) then the combo box would be enabled and the user could select the school that way.
But this doesn't work, for some reason, the combo box has NOTHING done to it (I have the control name correct).
Is this due to the way comboboxes work? Or am I missing something?
I should point out that the on-load event was not my idea, I found that online but makes perfect sense. I was trying something similar but was trying to use global variables because I wasn't aware that OpenArgs was a valid control!
I would like the user to be able to select the months he wishes in case they want to look at calender year, financial year or just a custom group of months. If I use the wizard and pick dates say the start and end of the year I get the following code in the row source of the chart control:
Code:
SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] WHERE ([DatePaid] BETWEEN #01/01/12# AND #31/12/13#) GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));
So I decided all I needed to do was replace the dates in the above code with my own global varible which i would pass custom dates into via a form. Which I called getds() and getde()
Code:
SELECT (Format([DatePaid],"MMM 'YY")),Sum([TotalPaid]) AS [SumOfTotalPaid] FROM [Q_AllCust_Gross] WHERE ([DatePaid] BETWEEN >=#getds()# And <=#getde()# ) GROUP BY (Year([DatePaid])*12 + Month([DatePaid])-1),(Format([DatePaid],"MMM 'YY"));