I want to create a query where the field being reference is an employee ID in a purchase orders table.
I have a sperate employee table with all the employees in and the employee ID in the other tables is set so that it displays the name in the forms.
I would like to set the query up so that when i type the name into parameter query or search form it is not looking for the employee ID number but the employee name that is typed?
Hi guys! I'm working on a project where I need to be able to use 4 different text boxes as parameter query. I need to use this boxes dynamically, could be text1 and text2 and text 3, or could also be text1 to text4, and/or text1 and text2. Here is the code I'm using right now, but it is not working. I placed this in the Criteria for field EmpID
I 've created a query that copies the records of a table into the same table, creating new records. (I don't know how this is called in english, sorry). For example if have a table with 2 records when the query is run I have 4 records with duplicates. The fields of each record I want to remain the same except for one, called code. For example: this is the table before the query is run:
surname name code tracy john 1 spencer bud 1
and this is the table after the query is run:
surname name code tracy john 1 spencer bud 1 tracy john 2 spencer bud 2
the code is also in an another table and is included in a textbox in my main form. Everything works fine when I run the query but I must type a parameter (code) to create the duplicated table. How can I pass the parameter to the query "automatically", using the textbox value? Thanx in advance
trying to pass a textbox value as parameter in a sql query.I have a multiline textbox and I use it to find several values in the database, so in this textbox all the values are pasted each per line and I have a small code to convert it to single line comma separated values.This is an example:The user enter the data:
[Text0] A1C556CC3C-TNNN C010070H13
The code convert this data to a single comma separated string and runs the query: ex: "A1C556CC3C-TNNN","C010070H13"
Code: test = """" & Replace([Forms]![Search]![Text0], Chr(13) & Chr(10), """,""") & """" [Forms]![Search]![Text0].Value = test DoCmd.OpenQuery "FindPartNo", acViewNormal, acReadOnly
In the SQL code I use the IN operator to find the exact value for each record:
[code]...
I'm passing the value incorrect into the sql code?
My problem is when I do this I get a pop up asking for a Parameter Value. but there is text in Text89. How to I get a round the Parameter Value box coming up an automatically do this. If I type the first four letters in to the Parameter Value it finds it on Consignment Guide.
(a) The On Open event of my report contains a VBA Sub that assigns a value to a variable named vShow. (Tracking the sub in VBA shows that vShow is correctly being assigned the desired value.)
(b) I then use vShow to try to control a calculation that occurs in one of the text boxes of the detail section in the report
(c) Basically, the control source of the textbox contains (in part) the statement (vShow>[fieldA]), which is embedded in a longer function.
(d) However, when I type this in at Control Source box of the Data tab of the the Property Sheet, Access always substitutes "[vShow]" for "vShow".
(e) So what I get is ([vShow]>[fieldA]), which treats vShow as the parameter of a parameter query.
ANY WAY TO OVERCOME THIS AND HAVE vShow recognized as the variable I defined at On Open? Perhaps there needs to be a variable declaration there, that I don't understand.
The basic form (vShow>[fieldA]) does not seem to be the problem, because I can enter, e.g. (500>[fieldA]) and everything works OK.
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've been searching for several hours now and can't find the answer to this problem... I'm using a form to capture criteria for a query. One of the fields is a text field and here is the parameter I'm using: Nz([forms]![frmMain]![Company],[CNm])
I want to return all records if the text box named Company is null. I'm getting only the non-null records. How can I get it to display all the records in the field, CNm? Thanks!
I have a report and within the report I have added some text boxes with some simple logic such as sum, count, etc. These text boxes function well as long as the logic is referencing existing fields. The minute I try to have a text box reference another text box I get the "Enter Parameter Value" box pop up. I don't understand this as the text box I am referencing has a vaule based on what it is referencing.
Example: 1st text box control source =Sum([existingfield]) I get a value. 2nd text box control source =[existingfield2]/[1st text box] I get "Enter Parameter Value" of 1st text box.
I am very new to access, any way that I can have a popup box appear when I ask to produce a report and I can type in a parameter of my choice and it will produce a report based around that parameter?
I have a table that is directly linked to an excel spread sheet that is updated each week external to the database. I have to produce reports on the data contained in that table.
I have already produced reports that look for specific number and those reports are produced automatically.
I was wondering if there was some way I could open a form and type a number into a text box and it would produce a report around that number. e.g. "list all engines below X margin" and I can type any number in representing X and a report would be produced.
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.
I'm trying to perform a DELETE query at run-time. The criteria for deleting records is somewhat complex due to various one-to-many dependancies between the tables, overlapping of date fields etc., so in order to restrict the DELETE to only those records which qualify, I've set up a load of smaller queries to produce the list of eligible records.There is only one parameter required - a 'CutOff' date which is arbitrarily chosen by the user and is available in a textbox control on an open form when the code is triggered.
Here is the main DELETE query :
Code: DELETE DISTINCTROW [tblComments].* FROM [tblComments] INNER JOIN [qryCommentsToPurge] ON [tblComments].[CommentID] = [qryCommentsToPurge].[CommentID] WHERE [tblComments].[CommentID] = [qryCommentsToPurge].[CommentID]
I've put qryCommentsToPurge and all the cascading sub-queries below - have highlighted in red where the parameter value is required (it is used in the queries to identify the records which must be retained, i.e. not deleted)Each of the individual sub-queries works fine and correctly (they return the correct population of records that they were designed to)If I run the sub-queries manually, I am prompted to provide the CutOff date, as you would expect - and the recordset returned in each case is correct for that date.
If, however, I run the DELETE at run-time (with the form containing that textbox open and containing a valid date value) I get a 3061 error :Suggesting that the queries can't pull the parameter value from the textbox on the form. But I've stuck a watch in the debugger on [Forms]![frmArchive]![txtCutOffDate] and I'm getting the correct date value? The form is open and available at the time.So why can't the query see it? I use these kinds of control references elsewhere in other queries and they work fine. I don't understand why it's a problem here?
Code: SELECT [tblComments].* FROM [tblComments] INNER JOIN [qryIssuesToPurge] ON [tblComments].[IssueID] = [qryIssuesToPurge].[IssueID] ORDER BY [tblComments].[CommentID];
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
