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.
View RepliesI have a report which asks for the beginning date and ending date which they want. The report is generated using this information. I want to put the information entered in the parameter request into the heading of the report. How do I recover this information to put it in.
Jerry Hughes
Hi,
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.
Hi All,
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?
Thanks!
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?
Thanks
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.
Thanks.
Dear All,
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
Many, Many thanks
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
Ssql = "Select [projectInformation].[projectId] from [projectInformation]" & _
"where [projectInformation].[projectId] = " & Me![ProjId]
'DoCmd.SetWarnings False
DoCmd.RunSQL Ssql
'DoCmd.SetWarnings True
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.
View 2 Replies View Relatedhi 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:
Expr1: DateAdd("d",+[Expr2],[Date of Joining])
Expr2: IIf([Membership Type]="Family - 1 Year","365",(IIf([Membership Type]="Family - 6 Months","183",(IIf([Membership Type]="Adult - 1 Year","365",(IIf([Membership Type]="Adult - 6 Months","183",(IIf([Membership Type]="Child - 1 Year","365",(IIf([Membership Type]="Child - 6 Months","183",(IIf([Membership Type]="Student - 1 Year","365",(IIf([Membership Type]="Student - 6 Months","183")))))))))))))))
thnx in advance, chia
Hey guys
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)
Your help on this is much appreciated
Nathan
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.
View 1 Replies View RelatedI 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
DoCmd.OpenQuery "qrySearchRTP"
DoCmd.OpenForm "FRM_PRIMARY"
DoCmd.GoToRecord acDataForm, "FRM_PRIMARY"
DoCmd.Close acQuery, "qrySearchRTP"
DoCmd.Close acForm, "FRM_MENU"
Exit_cmdSearchSite_Click:
Exit Sub
Err_cmdSearchSite_Click:
MsgBox ("Site not found. Please enter a valid RTP Reference Number")
Resume Exit_cmdSearchSite_Click
End Sub
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
View 1 Replies View RelatedI 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?
Thanks,
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
Hi,
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!!
Good Morning Guys
This is a form related query problem.
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.
Any ideas what might be wrong please.
Thanks
VC
I have parameter queries and then I use them to create reports so users can run those report and usualy they select the date range from and to in order to get results, all of that is fine.
But now I have a request do pick up one date if not null and if it is null to display a different date. I am not familiar if you can do that with parameter queries and if not how could I do that, basically they want to run the report off those dates if one is entered run it off this on if not use the other one.
could somebody helpe me with that please?
Cheers
I've got a report printing out dependant on someone entering a certain parameter, ie. upon running the report I enter the code FR when the parameter box pops up to print out all items coded with FR.
I need to be able to enter more than one code into this box, ie. FR & IN so that the report prints out all items coded either FR or IN.
I think this is easily done in the query by modifying the 'Like [Enter Code]' statement I'm already using, but since I'm still learning I could well be wrong. Any help would be appreciated!
Hi,
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!!
Hi
I have the following Access SQL query:
SELECT Trade.Ref_No, Trade.Carbon_Project, Trade.HostName, Trade.Country, Trade.Retainer_fee_to_Receive, Trade.Fees_Payable, Trade.Due_Date
FROM Trade
WHERE ((Month([Due_Date])=[?] And Month([Due_Date])=[?]));
I have put the information from WHERE into the Criteria of Due_Date in the Query Design View.
What I am trying to achieve is to be able to view the records based on two months parameters. i.e say to view the records between Jan 06 to Dec. 06.
Somehow I only get asked the first month input parameter from above query.
Can anyone pls help me on how I should get this?
Thanks
dfuas
Hi
I have created a simple query that is used as the record source of one of my forms. I want to pass the query a different criterion each time the form is opened.
Any suggestion/example on who to do that will be very much appreciated.
Thanks,
B
Hi
Apreciate your help on this.
I have a stock control db and when goods are recieved they could have a different references on them e.g. our internal ref, the purchase order ref etc.
I would like to run a query that firsts ask what criteria i want to use to run the query e.g use the int ref or the PO nr. I can set up a simple parameter query in one field but can not find a solution for the db asking what field i want to use before you put in the reference?
many thanks
scott
I have been searching the web for quite a while now and I can't find any solution to my problem or my question if you prefer.
I have got a query that runs with several parameters and return a single value. I wanted to know if it was possible to link that query to a single text box in a form and return the results when the form was loaded (The parameter would be equal to values taken from other textbox on he same form)
If anybody can help me out or advise me a good link I would be gratefull,
Thanks,
Fred,