Hi have a query which looks up a bunch of values across 2 tables and i have a parameter which prompts user to input a date and i then want all records matching that date to be returned but it just returns all records: The sql for the query is a follows:
SELECT Tbl_Customers.Customer, Tbl_Customers.[Telephone Number], Tbl_Customers.DateCalled, Tbl_Products.Sales_ISP, Tbl_Products.Sales_IBP, Tbl_Products.Sales_ITSM, Tbl_Products.Sales_Datasure, Tbl_Products.Sales_BusEmailPlus, Tbl_Products.Sales_IntTrade, Tbl_Products.Sales_IntTradePay, Tbl_Products.Sales_Mobile, Tbl_Products.Sales_Fusion, Tbl_Products.Sales_BBV, Tbl_Products.Sales_BBVPlus, Tbl_Products.Sales_Other, Tbl_Products.Sales_OtherCheck
FROM Tbl_Customers LEFT JOIN Tbl_Products ON Tbl_Customers.ID = Tbl_Products.ID_Custref
WHERE (((Tbl_Customers.DateCalled)=[Please select date]) AND ((Tbl_Products.Sales_ISP)=Yes)) OR (((Tbl_Products.Sales_IBP)=Yes)) OR (((Tbl_Products.Sales_ITSM)=Yes)) OR (((Tbl_Products.Sales_Datasure)=Yes)) OR (((Tbl_Products.Sales_BusEmailPlus)=Yes)) OR (((Tbl_Products.Sales_IntTrade)=Yes)) OR (((Tbl_Products.Sales_IntTradePay)=Yes)) OR (((Tbl_Products.Sales_Mobile)=Yes)) OR (((Tbl_Products.Sales_Fusion)=Yes)) OR (((Tbl_Products.Sales_BBV)=Yes)) OR (((Tbl_Products.Sales_BBVPlus)=Yes)) OR (((Tbl_Products.Sales_OtherCheck)=Yes));
As you can see it filters by looking for any check box with a value of yes and then hopefully by date -
I have a main form whose recordsource is a table that holds transaction data. the pk to filter is taken from another open form and i want to filter on the group that was selected with the group number being a fk in the ftran table as a number.
the open event has the following code:
Dim GroupID As Integer GroupID = Forms![group main screen form]!GroupID
Me.Filter = "group-DSN = 'GroupID'"
Me.FilterOn = True
it insists on asking for parameters for group and then asks for DSN
I have a form which contains a listbox sourced from a query and several text boxes where the user can enter filters for the query. The query has references back to the form for the criteria, i.e. the filter for "Company Name" is handled in the query criteria as
Code: Like "*" & [Forms]![HooversProspects]![SearchText] & "*"
When i exit this form in my un-compiled version it does not pop up an "Enter Parameter" box, however, when i exit this form from the compiled version it pop ups the "Enter Parameter" box for every filter I have.
How i can fix this? Perhaps change the rowsource on the query to null upon exit?
I have a bound form, in its heading I have a combo box which lists three choices. Basically I want to filter all my purchase records by checking a field PUOrderNb (Example: PO200100025) against the choice made in the combo box, namely DE (for demand), PO (for Purchase order) etc. On the after update event of the combo box, I have the following code:
Dim strfilter As String strfilter = " left ([PuOrderNb],2) like " & cboFilter.Column(1) Me.Filter = strfilter Me.FilterOn = True
When I make a choice in the combo box, I get a window asking me to enter a parameter value and it lists the value of the combo box choice as a sort of a title just above the white input fame.When I type in PO for instance, the program does correctly filter all order numbers starting with PO, but the whole point of having a combo box is not to have to type anything.The other odd thing is, when I change the choice in the combo box, after my first choice, I do not get this parameter question but nothing happens as to filtering. The first choice remains active.
I have a form which uses a parameter based query to present an individual senior doctor with a list of names for of individual juniors to provide an assessment report on. When the first form opens the user enters their RespondentID.
Once senior has decided which names to comment on I have another form which has the questions to be answered which is opened by clicking a button on the first form.
How can I pass a parameter from the first form to the second so that only the records relevant to that senior doctor are displayed? The underlying table has 60 senior doctors and 20 junior doctors. The senior doctor is identified by the field RespondentID in the first form. I've tried putting a WhereClause in the FormOpen command but I still get a dialogue box asking for the parameter RespondentID when the second form is opened.
I have a Main form, and a subform which lists client details. On the Main form I have an unbound field. I want to be able to type a word into this unbound field and have it display all company names that have this word in them. ie. I type "Ltd" into the unbound field and it displays all companies with "Ltd" in the title.
I have created a query that does exactly this (Like ("*" & [Enter Word] & "*")), it displays a dialog box and I type in "Ltd" and it displays all relevant companies.
I have tried everything I know to make this work when I use the unbound field on the Main form, but I've had no luck.
I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.
I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! :eek: Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!
Here is my code (abbreviated slightly):
SELECT DISTINCTROW C1.*, C2.* FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct] WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);
I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.
So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!
Help! :confused:
P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.
How can I check the Parameter query interval is out of range? Details:
I have a table with Date column. With parameter query(by Date field) I extract records between two dates. How could I make a check when taping in Inputboxes, am I or Not Between the Last and the First Date?
I'm writing a query which when run will prompt the user to enter criteria. In this case it is a workers id (like 000UA or 000UB)
My problem is I would like to have the ability to enter more than one criteria. What I want to do is to have the ability to enter several worker id's in the criteria field, like 000UA, 000UB, 000UC, 000UD, 000VA, 000VB, 000VE, 000VF, 000WA, 00WB
Is this possible and can someone explain to me how to do it?
I am trying to run a parameter query that will prompt the user for "facility", but instead of displaying the results in table format, I want a form to display the results. I have already developed the form. I would also rather for the users to search by a list box instead of typing the facility. I cannot seem to figure this out. Please help.
I am having problems finding a solution to this problem. I am working on 3 databases at the moment..and am at the final hurdle to complete it. I have built an Access database. I would like to generate a number of reports which have to be exported directly into excel individually.
I have a parameter query with 14 columns. One of the columns is called GROUPS. I have a form with a button. When you click on the button the parameter query asks for the parameter value.
The parameter value must be a GROUP. i.e BURR, WIEN.
After you enter the group the parameter query generates results for that particular group.
I have a number of groups which i would like to generate results for at the click of the button on the form.
There are around 30 groups. but i only need to generate reports for about 15 groups. The groups are stored in one of the tables.
So for instance after i click a button on the form the results for each requested "GROUP" are exported individually into excel.
Each group result must be in one excel file.
How can i achieved this. Do i need to use VBA? Please can you help. I need a solution then i can apply this to a number of databases
I want my msgbox to tell my user that this projectId does not exist in our db if the sql search returns an empty value. I get errors that i can't run this action query. please see red text. any ideas?
Private Sub Project_Quick_Find_Click() On Error GoTo Err_Project_Quick_Find_Click
Dim stDocName As String Dim stLinkCriteria As String Dim Ssql As String
stDocName = "Project Status - Full Details" stLinkCriteria = "[projectId]=" & Me![ProjId]
If Ssql = "" Then MsgBox "A Project with this number does not exist in our database", vbExclamation, "Cannot find project" Else DoCmd.OpenForm stDocName, , , stLinkCriteria End If
Exit_Project_Quick_Find_Click: Exit Sub
Err_Project_Quick_Find_Click: If Err.Number = 3075 Then MsgBox " Please enter a Project ID to find! ", vbExclamation, "Empty Field" Else MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error" Resume Exit_Project_Quick_Find_Click End If End Sub
Hi, I'm a complete novice to Access and wonder if anyone can help me with this one please? I have a column in a query that is the output from a barcode scanner called WhenScanned from a table tblScans. The data in the column is in the format of the date and time for example: 20/01/2006 21:30:00 I want to be able to interrogate the data in the query by selecting a range of a date(s) and times for example from 20/01/2006 05:30:00 to 20/01/2006 13:29:00, date and time range from 13:30:00 to 21:29:00, date and time range from 21:30:00 to next day 05:29:00. I can achieve this by typing the required range into the criteria row of the query column in design view e.g. >=#20/01/2006 13:30:00# And <=#20/01/2006 21:29:00# for each range but I want to make it more user friendly so that a user can select type in the appropriate date and select the time range from a drop down list or something without having to edit the query using syntax. Thanks in anticipation.
hi all, im trying to do this query, and in it i am using fields from 1 table, and i have created 2 fields using the expresion builder (these come up as Expr1, Expr2). these field (expr 1&2). & iwant 2 do use a parameter on Expr1, but when i go to view the query, it says i need to enter a paremter value for Expr2.. if i click OK then the parameter i need appears and when i enter the value i want no records appear. i uses the followin expressions:
I was wondering how I would go about making it so that a query will run or not run based on the input from a combo box.
I have a form with several combo boxes. And a couple of these controls a query that I would like to be optional. Right now it runs no matter what the input in the combo box, but I would like it to run based on the selection of the box (ie. "Yes" or "No" type input)
I am trying to create a query for a database for a company I'm interning for. The query is supposed to bring up an individual machine from a column labeled Machine ID. This column contains 20 or so abbreviated Machine labels. When you run the parameter query, and you select the individual machine ID I get multiple results or it brings up a list, but it is incomplete. The machines sometimes share parts, so in the machine ID column for a part there may be up to 5 machine IDs separated by commas. I want to know if there is a way of isolating a machine coded K from a part that has K,SIM12,C40 listed for the machines that it goes to.
I have a search button on my menu form that, when clicked, asks the user to enter a desired Reference number they wish to search for. This works fine when it is a Reference number that is stored within the database.
I have tested it to see if it works with reference numbers that do not exist, and instead of giving an error message to ask them to retry, it opens up the form to a new record... That is not what I want it to do.
Please can someone help me, here if my code up to yet (not sure if the msgbox is correct btw):
Private Sub cmdSearchSite_Click() On Error GoTo Err_cmdSearchSite_Click
Hi, I know how to create a parameter query where users could enter ONE criteria each time for a particular field of a table. Please see codes below. Now is there a way I can allow users to enter more than one criteria SEPARATED with commas for a particular field of a table?
Thank you.
Joe
"SELECT DEDPARMS" & payp & ".EMP_ID, DEDPARMS" & payp & ".FORMAT_NM, First(DEDPARMS" & payp & ".DEDPLAN_CD) AS FirstOfDEDPLAN_CD, First(DEDPARMS" & payp & ".DEDTYPE_CD) AS FirstOfDEDTYPE_CD, " & _ "Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='R',[overded_am],0)) AS [Employer Amt], Sum(IIf(Right([Dedetail" & payp & ".Dedtype_CD],1)='R',[Dedetail" & payp & ".ded_am],0)) AS [Employer Actl], " & _ "Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='A',[overded_am],0)) AS [Admin Amt], Sum(IIf(Right([Dedetail" & payp & ".Dedtype_CD],1)='A',[Dedetail" & payp & ".ded_am],0)) AS [Admin Actl], " & _ "Sum(IIf(Right([DEDPARMS" & payp & ".Dedtype_CD],1)='E',[overded_am],0)) AS [Employee Amt], Sum(IIf(Right([Dedetail" & payp & ".Dedtype_CD],1)='E',[Dedetail" & payp & ".ded_am],0)) AS [Employee Actl], " & _ "First(DEDPARMS" & payp & ".STATUS) AS FirstOfSTATUS, First(DEDPARMS" & payp & ".AGENCY) AS FirstOfAGENCY, First(DEDPARMS" & payp & ".ORG) AS FirstOfORG, First(DEDPARMS" & payp & ".TITLE) AS FirstOfTITLE, " & _ "First(DEDPARMS" & payp & ".STTL) AS FirstOfSTTL, First(Right(DEDPARMS" & payp & ".title,2)) AS RepUnit, First(Left([DEDPARMS" & payp & ".DEDTYPE_CD],2)) AS Type, " & _ "First(Left([DEDPARMS" & payp & ".DEDTYPE_CD],2)) AS LeftType, First(DEDPARMS" & payp & ".DEDTYPE_CD) AS FirstOfDEDTYPE_CD1, " & _ "First(Right([DEDPARMS" & payp & ".DEDPlan_CD],2)) AS Tier, First(Left([DEDPARMS" & payp & ".DEDPlan_CD],2)) AS Carrier, Plan.PlanDesc, Plan.TypeDesc " & _ "FROM (DEDPARMS" & payp & " LEFT JOIN Dedetail" & payp & " ON (DEDPARMS" & payp & ".DEDPLAN_CD = Dedetail" & payp & ".DEDPLAN_CD) AND (DEDPARMS" & payp & ".FORMAT_NM = Dedetail" & payp & ".FORMAT_NM) " & _ "AND (DEDPARMS" & payp & ".DEDTYPE_CD = Dedetail" & payp & ".DEDTYPE_CD) AND (DEDPARMS" & payp & ".EMP_ID = Dedetail" & payp & ".EMP_ID)) LEFT JOIN Plan ON (DEDPARMS" & payp & ".DEDPLAN_CD = Plan.Plan) AND (DEDPARMS" & payp & ".HLTH_TYPE = Plan.Type) " & _ "GROUP BY DEDPARMS" & payp & ".EMP_ID, DEDPARMS" & payp & ".FORMAT_NM, Plan.PlanDesc, Plan.TypeDesc " & _ "HAVING (((First(DEDPARMS" & payp & ".STATUS)) Not In ('P')) AND ((First(Right(DEDPARMS04.title,2)))=[Enter a Repunit]) AND ((First(Left([DEDPARMS" & payp & ".DEDTYPE_CD],2))) In ('01')) AND ((First(Right([DEDPARMS" & payp & ".DEDPlan_CD],2)))<>'00' And (First(Right([DEDPARMS" & payp & ".DEDPlan_CD],2)))<>'17')) " & _ "ORDER BY DEDPARMS" & payp & ".EMP_ID, First(DEDPARMS" & payp & ".STATUS);"
in ms access...when i make a parameter query, it asks for the parameter to match the whole field...how can i get it to accept an enquiry that matches 'any part' of the enquiry and then show all the matching results
I have a form that has two dropdown lists. First ddl specifies table to work with. Second ddl is populated dependant upon the table that has been selected in the first ddl.
I know how to specify form elements inside a query, however this first ddl is selecting the table that I want to pull a query from. When I right click the TABLE field under query design I am not prompted with the Build option as usual. (Note: The query will be identical regardless of the table that has been selected)
Is there a way with the [] brackets to specify the table to use in the following ddls query or should I use a parameter somehow?
I have designed a query wich contains the following columns: name, september, october, . . . , august. I want to create a parameter so that when i open the query i'll be able to see only one month instead of all. can i do that? I only know to create parameters for one column not for the whole query.. thanx
I want to create a query whereby the user can enter the part of a name (via a pop up) to be searched and all results with that searched part will show. Would appreciate any help. Thanks!!
I have set up a dialog box/form to act as a custom query builder. The user can enter various search information using a selection of textboxes and combos.
The query design as as follows. (this is just for one field of the query, the other fields are built in the same way).
In the field section I have the following
[SalesPerson]=[Forms]![Search]![cboSalesPerson] Or [Forms]![Search]![cboSalesPerson] Is Null
The SQL is
SELECT Main.RecordNumber FROM Main WHERE ((([SalesPerson]=Forms!Search!cboSalesPerson Or Forms!Search!cboSalesPerson Is Null)=True)) ORDER BY Main.RecordNumber;
Criteria is set as TRUE
This is just one part of the query, I won't set the other fields up until I get this working. On the form the user will select the sales person name from cboSalesPerson and then click the cmdSearch button. cmdSearch runs a macro which opens the query, currently in datasheet view, but later it will be a form or report view.
The trouble is this won't work at present. If I run the query from it's design view it ask's for the parameter and works fine. It just won't work when run from the cmdSeach button on the custom search form. It seems the value in the combo is not passed to the query.
