I have a SELECT query which includes complex calculation that I would like to run on a large number of tables. This query also uses parameters to input ranges for some fields.
I can run this on other tables by doing a global replace of the table name in the script and pasting this as a new query, but this is cumbersome. I would like to set the table name as a parameter, so I can type in the table name for which I want the query to run (similar to the parameter inputs on the range fields).
Is there any way this can be done within SQL? I presume it can be done via VB, but I am not familiar with VB.
I would like to set up a query that uses two tables. One of the tables, the 'main table', wil be used every time the query is run. Each time a new record is created in the main table a 'new table' is created containing a subset of data - the name of this table uses a ref ID from the main table. I would like to be able to select which 'new table' to use in the query as part of a parameter query.
eg. Main table record 1 has a field JID of J0001 and a corresponding 'new table' titled J0001. record 2 in main table has JID J0002 and creates a 'new table' J0002 etc
so I would like to set the query up such that when a record is chosen in 'main table' the query knows the name of the 'new table' corresponds to the JID of the record in 'main table' ....
Have seen the same question asked but no answer - is this possible ?
With referential integrity enforced, is it possible to use append query to append to multiple tables based on only one parameter that applies to only one table. If so, please enlighten me with a detailed explanation.
I have a lot of tables that need updating often, so I delete the old ones and re-import them then run the queries again. From time to time, I add / remove columns but in the case I removed columns that are NOT needed in the queries.
So, I ran the queries again and they run fine but it says "Enter Parameter Value" for a column that is no longer there. It's not in the SQL code either.
So where is it trying to get these columns from and how do I fix this?
I have a function that when called transfers a query recordset to an excel spreadsheet then emails it. At the end of the function I use code to write the date sent to a table. Each time the function is called I only need records in the query that have been modified since the last time the function was called. I have a field in the query 'LastModified' with a criteria '>[Enter Date]'. I then look up the date in the table and enter it manually. I know how to look up the last date sent in table using code but getting the >#SomeDate# in the query with VBA.
I have a parameter that I need to get a table contents between dates. In the query:
Birthdate: XXTable: criteria as follows: Between [Enter Start Date] and [Enter End Date:]
When run it gives me the Error - ! This expression is type incorrectly, or its too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables
I have tried almost everything. The formatting of the Birthdate is x/x/xxxx or shortdate. Will this affect the input thus affecting the outcome of the query.
I've created a farm database, with a form (frmSearch) that will allow user to filter data. The form comprises of combo box and list box etc... for the user to input their own criteria. The subform below has a datasource. The data source is based on a query (qContractionSearch) which is basically a parameter query with 3 tables. The problem is, however, that it won't work with 3 tables... but will work if data source comprises just one table.
See the farm database attachment...and go to frmSearch...then go to Contraction tab. (The Cattle tab filter works fine-it only has a single-table datasource).i have a requery macro which runs whenever the user clicks 'search'.
I have a linked table tblHome which is stored in a Sql Server DB and I want to create a form with 3 fields in it i.e. fieldA, fieldB, and FieldC in it and a button.
I want to add values to fields fieldA and fieldB and fieldC and when I click the button I want the value in fieldA to update any records in the linked table tblHome which contains the values in fields fieldB and FieldC.
I'm using Access 2003...I have a query that searches a parts table by description:
Like "*" & [Enter in Part Description to search] & "*"
I need to add another search to this query, I added another field to the parts table call manufacturer. I add this field to the above query and added this parameter to it:
Like [Enter in Manufacturer] & "*"
I then added a manufacturer to one of the fields for test purposes. For some odd reason this doesn't work. If I take out the manufacturer parameter and don't enter in anything into the part description the query returns all the records. When i type a description into the part description field the query returns the proper records. When I add back the manufacturer parameter and enter through both parameters only the one record returns showing the test record instead of all of them. If I put a part description in and enter through the manufacturer parameter no records show when they should.
I've tried adding a test field to a different table and tried a different query using the like parameter. The parameter works in a field already in the table but won't work with the new field I added. I've done compact and repair.
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.
I am trying to set up a create table query using parameter called Year. For each year I input , I need to create a new table with the suffix of the year. for example, if i input year 2011, i want to create a table called budget_2011, if i input year 2012, i want to create a table called budget-2012...
I am using a function to calculate the quartiles from an existing query and enter the corresponding EqNum and EDescription into a table.When I run the code below it asks me for a value for SysAssetCritRankQ from the rstPercentileRST parameters. I have tried adding "" and [] but they aren't working.(The issue with self taught SQL/VBA)
SysAssetCritQ = Query Name Total = Calculated field in query
Code: strSQL = "SELECT SysAssetCritRankQ.EqNum, SysAssetCritRankQ.EDescription INTO [EOQComboT]FROM SysAssetCritRankQ " & _ "WHERE Total >= PercentileRst([SysAssetCritRankQ], Total, 0.75); "
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
