I have a simple parameter form where the users enter selectives for their queries.
They usually want the top 100 records, but would like to select the number of records from that same form.
I tried adding the forms' field name into the top records field in the query but it will take only a number.
Any suggestions about setting it form the form or using VBA?
Thanks
i'm creating a search form giving the end user a range of controls to use when filtering/searching data. See the image.But, i think my range search (using the textbox) to put in a lower and upper limit...is preventing this from working. In fact, when i put data into all the controls, no data pops up in my subform.
My query data source can also be seen...showing you how i've handled teh null entries. (i need to put in a null 'handler' for the two textboxes?)
BACKGROUND: I have a query, in which the user decides a customer name. According to the customer name a specific custom price is chosen for the customer from tblInputProductSpecs. Based on this I do the following calculation Total Price:[Price]*[Quantity], where the quantity is already known.
PROBLEM: Since the Total Price is total price for a specific product. I wanted to calculate the TOTAL of all the Total Price/Product. But finding it really hard to do this. I tried to do Total:SUM([Total Price]), but this doesn't work. I get an error. I even tried Total Price:SUM([Price]*[Quantity]), but this also doesn't work. Is there any possible way to find this TOTAL?
I would really appreciate your help with this frustrating problem.
regards,
Vakul
I have devolved two reports which receive their data from the same query. I could not fit everything in one report as I am limited by Access’ 22 inch limitation. I would like to enable a user to run the reports off a Macro. The report/query requires the user to enter a “Parameter Value” such as date and facility number. When running the Macro it requires the user to input the same “Parameter Values” twice, once for each report. What I would like the Macro to do is only request the Parameter Values once. Is there a simple way to achieve this? Thanks for any assistance.
View 2 Replies View RelatedI am trying to alter this parameter to bring back all records if either beginningsalesrange or ending salesrange is left blank. I can't quite get it right.
Between [forms]![frmState]![BeginningSalesRange] And [Forms]![frmState]![EndingSalesRange]
I have a query set up which needs to have different criteria at run time depending on values selected by the user. If no option is specified, I need to pick up all records with 'Nulls' in them else, if date is specified by user, I need to pick up all records with date > than specified date. The query is able to pick up the date value from an unbound text box in the form. I used the same field in the form and populated "Is Null" in the field and it does not work. How do I pass this as a paramater to the query from the form?
Is there a way around without setting up 2 sets of queries and reports?
Thanks,Priya :mad: :confused:
I am using MS Access 2010 to export data into MS Excel 2010 spreadsheets.
I am just wondering: Is there a way to set the Parameter value for the query via VBA?
For example: There are 10 regional managers. When I click a button on a form, 10 sets of data per manager are going to be exported into Excel spreadsheets.
I have created a saved query named [For exporting] with a parameter [Manager Name] for the field [Master Table].[Manager].
The VBA for the button has 2 subs:
1) Sub 1 for the loop for 10 managers
Code:
...
strQuery = "SELECT DISTINCT [Master Table].[Manager] FROM [Master Table] WHERE ((([Master Table].[Manager]) Is Not Null));"
Set rstStores = CurrentDb.OpenRecordset(strQuery)
[Code].....
I have a text box in a report, with it's control source property set to =[BegDt].
Upon loading the report a message box pops up and asks the user for the value of [begDt]. This occurs 3 more times for 3 other parameters.
Now I want to change the design of the report so I decided to put these 4 parameters on a form so the user can fill in the 4 values all at once rather than responding to 4 message boxes that pop up when you run the report.
The problem is now the control source properties in all the different text boxes need to be changed to [forms]![frmABC]![txtBegDt] etc.etc. There are over 100 text boxes in this report that make reference to the 4 different parameters.
Is there a replace command I can use to change all the occurrences in the text boxes or perhaps a simpler way would be to pass the values to the report through code.
I tried placing this in the report header's format event and it doesn't seem to have any effect.
Code:Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)begytd = [Forms]![frmABC]![txtStDt]endytd = [Forms]![frmABC]![txtEndDt]curmo = [Forms]![frmABC]![cboMo]curyr = [Forms]![frmABC]![cboYr]End Sub
I should mention that the report uses a table (tblRecap) as it's record source. I tried using a query that refers back to the form for the parameter values but that means I have to use the query as the reports record source and that creates a problem because the report needs fields from the table to perform calculations.
Can someone help me with a solution please.
Thanks for your time.
I have a report that is based on a query.
The query has two fields. Start and End Dates.
When I run the query the Parameter box asks for the dates by using <[Date1] and >[Date2]
What I want is to have 2 fields at the top of the report, that display the values I enter in these boxes?
I have an append query that currently looks like this URL...This query automatically adds the machine parameters for a product code and lot number into the running condition log. This is so the user does not have to manually go in and tediously select each machine parameter.
The running condition log also has a date field to specify what day and record number the machine parameter's value was recorded on. When I run the query the appended rows look like this. The product and lot are defined by user parameter and there are actually about 36 machine parameters
Code:
Productstockcode LotNo Day Record Parameter ActualCond
PE-500 130816m71 StockTemp
PE-500 130816m71 Zone 1
PE-500 130816m71 Zone 2
My question is: how do I modify the query to automatically add the date and record number in one shot? It needs to be user defined at the time of the query because this data is not stored anywhere else in the database. For each 36 machine parameters the day and record would be the same.
I have two forms one simple and another sub form located in simple form and connected to query where query has parameters, while opening the simple form the sub form require the parameter values. Question is here how I can make the sub form like when I open the simple form the sub form must be disabled (not request me for parameter values) and make a button in simple form to activate the sub form and then request me the parameter values.
View 1 Replies View RelatedAny possible solutions to this issue would be much obliged, it's one of two absolutely stupid issues that I'm just working around since I can't resolve...
I have a parameter query set up on a Date/Time field that is returning results not including the last date entered as a parameter. So if the user enters "2007-04-07" for the start date, and "2007-04-30" for the end date, the records with "2007-04-30" are not included.
I know it's some stupid thing I've done with this query that is causing it to do this, but since when does "<= some date" and ">= some date" not include the last date retrieved in the result set... do I need some programming lessons on operators, or what?:confused:
I want to have a form that is based on a query. I want the first box to be a dropdown and fill in 6 textboxes after selectino (I have already made this) Then I want to select a start date and an end date for whomever was selected in the begining dropdown. I don't know how to get the query to do this. It is like a parameter but I don't want messages, I want txt boxes.
View 1 Replies View RelatedDoes anybody know how to have a text box on a form "satisfy" a parameter in a query? I want to enter two dates and have a subreport show information from them? (The records shown will only be between those two dates) How do I do this? (I don't want the parameter to pop up on form open) Something like (Date1) = Parameter1 (Date2) = Parameter2
View 2 Replies View RelatedI've run into a strange problem. I'm trying to have an image box that you can double click and it will open a different form with a full sized image. When I open my form with the larger image, it asks me for a Parameter Value. Is there any way to stop it from asking me for that? Also, what is a parameter value and should I be typing something there?
Any ideas you may have would really help me out. Thanks a lot in advance!!
I have a form that allows the user to "Enter a Parameter Value" that determines the data that will show up
on that form. I'd be pleased to know:
1) How can I display the Parameter Value, choosen by the user, in the form.
2) Consider a form that will display all the students whose name is having Vincent.
If the user doesn't know the complete name, how can he define the parameter value. I mean in some
languages I can use Parameter Value = Vincent* and it will show me all the students whose name includes
Vincent. And in Access? How can I do that?
Hi. I have a parameter query viewed in a form.
How do I show the results in a list rather than singular?
Other than a report...
Thanks!
Does anybody know how to have a text box on a form "satisfy" a parameter in a query? I want to enter two dates and have a subreport show information from them? (The records shown will only be between those two dates) How do I do this? (I don't want the parameter to pop up on form open) Something like (Date1) = Parameter1 (Date2) = Parameter2
View 7 Replies View RelatedI searched and read everything found regarding TopValues- since those answers don't fit exactly what I am trying to do here is another post :)
I have a query that selects records for a table based on a users input on a form-
They would like to be able to input the TopValues number [set to 100 for this query] from that form, also.
The code for the query is below-
*******************************************
INSERT INTO tblGeneratedRandomRecords ( recid, ACCOUNT_ID, UNIFORM_BUSINESS_ID, ACCT_ACTV_DATE, REGION_NUMBER, CountOfRISK_MAIN_SUB, SumOfPREMIUM_ASSESSED, SumOfTOTAL_UNITS, YEAR_QRTR, ACCT_STATUS_CODE, [Total DrvdFTE], FIELD_AUDIT_FLG, LAST_FIELD_AUDIT_DATE, IN_COLLECTION_FLG, OUT_COLLECTION_DATE, RandNumGen, NAICSCode, NAICSDesc )
SELECT TOP 100 tblRollUpPayRoll.recid, tblRollUpPayRoll.ACCOUNT_ID, tblRollUpPayRoll.UNIFORM_BUSINESS_ID, tblRollUpPayRoll.ACCT_ACTV_DATE, tblRollUpPayRoll.REGION_NUMBER, tblRollUpPayRoll.CountOfRISK_MAIN_SUB, tblRollUpPayRoll.SumOfPREMIUM_ASSESSED, tblRollUpPayRoll.SumOfTOTAL_UNITS, tblRollUpPayRoll.YEAR_QRTR, tblRollUpPayRoll.ACCT_STATUS_CODE, tblRollUpPayRoll.[Total DrvdFTE], tblRollUpPayRoll.FIELD_AUDIT_FLG, tblRollUpPayRoll.LAST_FIELD_AUDIT_DATE, tblRollUpPayRoll.IN_COLLECTION_FLG, tblRollUpPayRoll.OUT_COLLECTION_DATE, acbgetrandom([recid]) AS RandNumGen, tblRollUpPayRoll.NAICS_Code, tblRollUpPayRoll.NAICSDesc
FROM tblRollUpPayRoll
ORDER BY acbgetrandom([recid]);
****************************************
Any help is appreciated!
Hi, I have a combo box based on a parameter query, which is on a sub form. I want the query to use the existing value from a field on the main form as the parameter, without getting the "Enter parameter" box. please help.
View 3 Replies View RelatedI have searched the form and what I am trying my best to do right now is create a form with a textbox and a button. In the textbox I would just put in the parameter of a query and the query result or report would open up that is based on it. Can someone give me an example or point me in the right direction.
The reason im trying to do it this way is I would be able to customize the form to go with the rest of the database's layout....
I currently have a parameter form named "Fm_Commander_Parameter" and a report named "Rpt_Bldgs_by_Commander". On the parameter form I have a unbound drop down. On the After Update have I this code that opens the report.
Private Sub Cmb_Commander_AfterUpdate()
DoCmd.OpenReport "Rpt_Bldgs_by_Commander", acViewPreview
DoCmd.Close acForm, "Fm_Commander_Parameter"
End Sub
In the Query for the report for the Commander field I have the parameter as [Forms]![Fm_Commander_Parameter]![Cmb_Commander]..how to write code very well and am unsure this can be done, but what I would like to do is remove the 'After Update' on the form and on 'Open' of the report I would like to write a code to go to the form, allow them to select the commander, then go back to the report query with the selection. This way I could use the Parameter Form with other reports. Below is the code I've come up with that will be place in 'Open' on the report.
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Fm_Commander_Parameter", acNormal
(I don't know what to enter to keep the form open to enter the data. Without the below command it will run the report but the parameter form stays open.)
DoCmd.Close acForm, "Fm_Commander_Parameter"
End Sub
how to get a Parameter from a form into a query.I have a form with a subform and a table. However I want to enter a number in the Form and then the subform shall display all entries from the table with that number as a specific field. So to speak i want to apply a filter. Can i do that somehow without using VBA ? And without entering the number in that tiny dialog window that pops up when i use "[ ... ]" brackets in my query ? This is may query so far .... and it is the datasrc for my subform ...
Code:
SELECT tbl_autos.ID, tbl_autos.Typ, tbl_autos.Alter, tbl_autos.BesitzerID
FROM tbl_autos
WHERE tbl_autos.GaragenID = <Param>;
i want to replace <Param> with the content of my form.
I have Created This Macro (Photo1) to Display Clients Home Address on A Bing Map, however, When I run Code I get A "Enter Parameter Value" Pop-Up. (Photo2) When I actually enter ClientID number in this case 5 it Does what I want See (Photo3). How do I get it to open directly to form with out Enter Parameter Value popup?
View 2 Replies View RelatedHi there!
I'm trying to use an Invoice number from an Invoice form to populate a table of Invoice Lines with relevant records (linking by Invoice number). I realise the following SQL code isn't an append query, but I've simplified it to make it easier to explain
SELECT [Forms]![fInvoices]![SPInvNumber] AS MTInvNumber, tInvoices.SPInvNumber
FROM tInvoices
WHERE (((tInvoices.SPInvNumber)=[Forms]![fInvoices]![SPInvNumber]));
The output I get for this query is something along the lines of:
MTINvNumber | SPInvNumber
쉠 | 49760
THe MTInvNumber should read the same as the SPInvNumber, but it doesn't. If you can't see the above, its some sort of weird chinese/japanese character I think!
I thought maybe it was a display error, but I tried using it in my append query, and it inserted it as garbage too. The weirdest bit is it actually regards the form information as equalling the SPINvNumber from the tInvoices table! :S
Any clues on how I get it to format correctly??? I'm very tired now so it's bound to be something simple!
I know this is probably a basic question- but Im not finding a clear answer here.
Basically- I have a value that I want to select from a drop down box on a form (not created yet). That value will get inserted into my query for a calculation I am doing. The form will pop up the results of the query in a table/dataset.
How do I designate the variable in the query that is being inserted from the form? I am using Access 2002- is there a way to visually perform this task (ie- drag/drop type thing)?
Thanks guys!