Parameters
Jun 13, 2005
I have a form that lets the user enter a beginning date and ending date in text boxes, then below that asks the user to choose a major category from a combo box. That works fine. The user the hits a command button below which opens a report. On the report is a chart based on the information they provide. This works well too. Also, the heading of my report contains the parameters in which the user gave to develop the report. The dates come up fine because I used a text box and put in =[Forms]![myformname]![parametername].
But this code does not work for a combo box. It will not pick up the choice that the user makes. Does anyone know how I can capture the answer so that I may display it in my report?
View Replies
ADVERTISEMENT
Mar 16, 2006
I have created a report from the results of a query. The query has 2 parameters.
SELECT Nonconformances.DateRaised, Nonconformances.Customer
FROM Nonconformances
WHERE (((Nonconformances.DateRaised) Between [Enter start date] And [Enter end date])) OR (((Nonconformances.Customer) Like [Enter customer name] & "*"));
Now the problem is that I have been asked to add the search criteria to the report header. If the search criteria came from a form, no problem, but the user enters the criteria into a parameter box generated by the query. So if the start date was 1/5/05 and end date was 31/12/05 and the customer search was F.C, how do I capture this and show it on the report?
Any help appreciated.
View 10 Replies
View Related
Sep 1, 2006
Hi
When I want to run a form it prompts me to enter some parameters. I wonder what could have caused this issue?
Any help will be very much appreciated.
B
View 3 Replies
View Related
Jun 3, 2005
I have a date field. I have
Between [Please enter second begin date] And [Please enter second end date]
as my criteria so that the user may enter two dates. But, I want the extreme dates to be included in my data.
For Example: If the user enters 1/1/2005 and 1/31/2005, I want the information for the 1st and 31st to be included. How can I do this using just about the same criteria statement?
View 8 Replies
View Related
Nov 29, 2005
Below is a query in SQL view that is driving me crazy.
When ran it ask for a StartDate, EndDate, StartDate, EndDate.
Can someone please look and see if they can determin where the criteria is coming in from. In design view there is no criteria set up to ask for dates. Also, there is no parameter set in the parameters box.
SELECT tblBooksAndContracts.intTrackingNumber, tblGroupInformation.strGroupName, tblGroupInformation.strGroupNumber, tblSystems.ysnSystemWork, tblSystems.dtmSystemWorkComplete, tblGroupInformation.dtmDateMembershipReceived, tblGroupInformation.strRegion, tblBooksAndContracts.ysnNeedBook, tblBooksAndContracts.ysnNeedContract, tblBooksAndContracts.intBookAndContractCarveOut, CompareDates([tblBooksAndContracts].[dtmBookShipped],[tblBooksAndContracts].[dtmASODraftsSent]) AS [Book Complete], tblBooksAndContracts.dtmContractDistributedToMarke ting AS [Contract Complete], CompareDates([tblBooksAndContracts].[dtmBookShipped],[tblBooksAndContracts].[dtmASODraftsSent],[tblBooksAndContracts].[dtmContractDistributedToMarketing]) AS [B/C Complete], ([tblGroupInformation.dtmDateMembershipReceived]+[tblBooksAndContracts].[intBookAndContractCarveOut]+30) AS ECD, CompareDates([tblBooksAndContracts].[dtmBookShipped],[tblBooksAndContracts].[dtmASODraftsSent],[tblBooksAndContracts].[dtmContractDistributedToMarketing],[tblSystems].[dtmSystemWorkComplete],[tblGroupInformation].[dtmDateMembershipReceived]) AS [Master Complete Date], tblGroupInformation.dtmEffectiveDate, tblGroupInformation.strRegion, tblGroupInformation.strNRC
FROM (tblBooksAndContracts INNER JOIN tblSystems ON tblBooksAndContracts.intTrackingNumber = tblSystems.intTrackingNumber) INNER JOIN tblGroupInformation ON (tblSystems.intTrackingNumber = tblGroupInformation.intTrackingNumber) AND (tblBooksAndContracts.intTrackingNumber = tblGroupInformation.intTrackingNumber)
WHERE (((EntryIsComplete([ysnSystemWork],[dtmSystemWorkComplete],[ysnNeedIDCard],[dtmMailIDCards],[ysnNeedBook],[ysnNeedContract],[ysnNeedDraft],[ysnNeedFlyer],[ysnBookAndContractComplete]))=Yes) AND ((IsBetween([startDate],[endDate],[tblBooksAndContracts].[dtmBookShipped],[tblBooksAndContracts].[dtmASODraftsSent],[tblBooksAndContracts].[dtmContractDistributedToMarketing],[tblSystems].[dtmSystemWorkComplete],[dtmMailIDCards],[tblGroupInformation].[dtmDateMembershipReceived]))=Yes));
As well as when it does run, and you enter the span dates, if the field is blank it inputs 12:00 am in the field and includes it in the query. I checked the tables and it is not set up to input 12:00 am as a default value, nor is it stored as 12:00 am in the tables.
I am at my wits end here and any help/advice would be helpful.
If this does't make sense let me know and I will try and explain further.
Thanks in advance!
*please disregard the grammer, I am typing this fast before I head out to get the kids..a mommies job is never done!*:eek:
View 10 Replies
View Related
Sep 21, 2006
Please look at this sql statement and tell me where the error is. When I try to open the recordset, I get a "Too few parameters. Expected 1" error. That kind of error usually goes with missing # on dates or ' on text
DateTime is a DateTime field that defaults to Now().
The problem appears to be in the dates because when I comment out all after the user parameter, it works, and taking out the "AND Used Is Null" doesn't help.
sqlstr = "SELECT * " & _
"FROM tbl_Usage " & _
"WHERE User = '" & UsrNm & "' " & _
"AND DateTime >= #" & Date & "# and DateTime < #" & DateAdd("d", Date, 1) & "# AND Used Is Null"
This is from the immediate window
?sqlstr
SELECT * FROM tbl_Usage WHERE User = 's5ucba' AND DateTime >= #9/21/2006# and DateTime < #9/22/2006# AND Used Is Null
View 1 Replies
View Related
Sep 4, 2007
Hi
I am using 'Like "*" & [Forms]![Template]![Combo433] & "*"' in a query to a combo box on a form so if I have nothing selected in the combo all data is shown, except this does not show any null values!
Can any advise on how I can adapt this to allow for null values!
Thanks in advance...
View 1 Replies
View Related
Dec 25, 2007
I have designed an access database for an organisation. The database is basically all the people who work for an organisation, their addresses, the borough they work in and other details. I have deisgned a query that picks out all the people who work in a particular borough, by using parameters. So when I click a button, a box comes up and asks me to put in the borough and voila, I get a list of all the people who work in that borough. But what do I put in the parameter box, if I want the entire list of people in all the boroughs not just one particular borough?
Really appreciate the help...
View 1 Replies
View Related
Feb 15, 2007
How would I go about creating a parameter that can search for all events that occurred in a particular month. I don't want the user to have know the exact date, just the month and year.
View 1 Replies
View Related
Nov 8, 2007
I have a report thats based on a table works well(thanks to the help from you guys) my problem is..When the parameter comes up to put in the customer name I would like to change that into a list box for spelling reasons, I have changed "customers" in all tables and quereies to look up list box but my parameter only comes up with a part for me to type the customer name.can this be changed? if so how please and I am a novice at this so please try and keep the help as straight forward as possible
cheers
View 5 Replies
View Related
May 3, 2006
Hi,
I have a Data Access Page which is generated on behalf of a query.
Query uses one parameter field ,so when is started it promts with the form named Enter Parameters in which I have to input parameter value.
So before the DAP is opened, first is displayed parameter form.
I would like to change dimensions of that form(height, width) but I dont know how.
I already tryied by opening DAP in design view & then in microsoft script editor but i couldn't find anything about this pop-up parameter form.
Any suggestions are wellcome:rolleyes:
View 1 Replies
View Related
Jul 26, 2005
I am trying to strengthen an already developed database at my work. They had a form with five different text boxes, each one that would run a seperate query on the same table, and the results would open on a different page. I am trying to combine all these text search parameters from the form to a single query and have the result come up on the form. I have done every search I can thing of from a forum search, a google search, and just reading through query forum posts for about a day; but I still cannot tell what is wrong. If someone could look at my code, and see if I have a mistake, or if I'm even in the right direction. :confused:
SELECT FLIGHTS.[FINISH OD], FLIGHTS.[PIPE OD/SHAFT OD], FLIGHTS.PITCH, FLIGHTS.[TYPE OF MATERIAL], FLIGHTS.[BURNED OD], FLIGHTS.[BURNED ID], FLIGHTS.[RIGHT/LEFT HAND], FLIGHTS.PartNumber, FLIGHTS.COMMENTS, FLIGHTS.[START TIME], FLIGHTS.[FINISH TIME], FLIGHTS.PurchaseOrderNumber, FLIGHTS.QTY
FROM FLIGHTS
WHERE ((FLIGHTS.[FINISH OD]=Forms![KNOWN BURN SIZES]!Text25 & "*") And (FLIGHTS.PITCH=Forms![KNOWN BURN SIZES]!text37 & "*") And (FLIGHTS.[TYPE OF MATERIAL]=Forms![KNOWN BURN SIZES]!Text32 & "*") And (FLIGHTS.PartNumber=Forms![KNOWN BURN SIZES]!Text0 & "*") And (FLIGHTS.PurchaseOrderNumber=Forms![KNOWN BURN SIZES]!text36 & "*"));
The FLIGHTS is the main table, [KNOWN BURN TABLES] is the form that the parameters are entered. I want users to be able to enter any fields they want and leave others blank. Currently all I get is a blank query. Sorry if the answer seems obvious; I tried avoiding posting till I knew that I couldn't figure it out on my own.
Thanks.
-Brian
View 8 Replies
View Related
Apr 23, 2007
hi,
i have written an sql statement to display fields from a table. but i want it to do this based on a date range (the user enters a parameter)
this can happen in access, but im using vb6 to do this and i dont know how to specify the parameter to wait for user input to filter the results
pls help
View 6 Replies
View Related
Apr 23, 2007
After researching this issue I have not found a satisfactory solution to this issue.
I currently have 1 Access query that is the basis for my VBA code_ Sql statement. both Query statements work when debugged. However, I am getting this error on execution of the sql statement in my vba.
Dies here: '<<<<<<<<<<<<<<<<
Public Sub ConnectCMIS(spar As String)
Dim sConn As String
Dim oConn As ADODB.Connection
Dim lCnt As Long
Dim sSql, strSQL As String
Dim rstOra As ADODB.Recordset
Dim rsAccess As New ADODB.Recordset
Dim fld As ADODB.Field
On Error GoTo ErrorHandler
DoCmd.SetWarnings False
sConn = _
"Driver={Microsoft ODBC for Oracle};Server=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS =(PROTOCOL=TCP)" _
& _.;......
strSQL = "SELECT " & _
"MEASNO, FTEMNOMENCLATURE, NOMENCLATUREMODEL, " & _
"EquipID As EQUIPMENT_ID, MULTIPLE_ID, JOB_GROUP, " & _
"PROJECT, PRIORITY, IIf(Len(Trim(COMPLETE_BY_DATE)) > 0, Mid(COMPLETE_BY_DATE, 3, 2) & ""/"" & Mid(COMPLETE_BY_DATE, 5, 2) & ""/"" & Mid(COMPLETE_BY_DATE, 1, 2), Null) AS COMPLETEBYDATE, " & _
"RequestorId As REQUESTOR_ID, " & _
"CALIBRATION, REPAIR, MODIFICATION, ACCEPTANCE, EVALUATION, " & _
"MAINTENANCE, SUPPORT, CMIS_LAB, SERVICE_LAB, WORK_CODE, " & _
"CHARGE_NUMBER, DISPOSITION, ReqComments as REQUESTORCOMMENTS, INPUT_RANGE_MIN, " & _
"INPUT_RANGE_MAX, INPUT_UNITS, OUTPUT_RANGE_MIN, OUTPUT_RANGE_MAX, " & _
"OUTPUT_UNITS, GAIN, CUTOFF_FREQ, INPUT_FREQ, REF_FREQ, REF_VOLTAGE, " & _
"EXCIT_VOLTAGE, EXCIT_ENABLED, FTIR_ACCURACY, OFFSET, OFFSET_ENABLED, " & _
"REQ_EMO1, REQ_EMO2, REQ_EMO3, REQ_EMO4, REQ_EMO5, REQ_EMO6, " & _
"SPARECODE, CALIBRATION_ID " & _
"FROM QS_SRUpdatetoCMISdrt " & _
"WHERE job_group = " & Chr(39) & spar & Chr(39) & ""
Set rsAccess.ActiveConnection = CurrentProject.Connection
rsAccess.CursorType = adOpenStatic
Debug.Print strSQL
rsAccess.Open strSQL'<<<<<<<<<<<<<<<<
If rsAccess.EOF = False Then
Set oConn = New ADODB.Connection
oConn.Open sConn
Set rstOra = New ADODB.Recordset
rstOra.ActiveConnection = oConn
rstOra.CursorType = adOpenKeyset
rstOra.LockType = adLockOptimistic
rstOra.CursorLocation = adUseServer 'default
rstOra.Open "CMIS.UDV_RFS_SR"
Do While rsAccess.EOF = False
rstOra.AddNew 'Then where you add the Oracle record instead of individual assignments you have
On Error Resume Next
For Each fld In rsAccess.Fields
rstOra(fld.Name).Value = fld.Value
Next
rstOra.Update
rsAccess.MoveNext
Loop
End If
strSQL = "UPDATE CMIS.UDV_RFS_SR SET PROCESSED_IND = 'S' WHERE job_group = '" & spar & "'"
oConn.Execute strSQL, lCnt
DoCmd.RunSQL _
("UPDATE TA_SR SET PROCESSED_IND = 'S' WHERE Job_Group='" & spar & "'")
rstOra.Close
Set rstOra = Nothing
oConn.Close
Set oConn = Nothing
Call MsgBox("Submittal to CMIS has been processed.", vbInformation, "Process Submittal Complete")
SubExit:
On Error Resume Next
If Not oConn Is Nothing Then
Set oConn = Nothing
End If
rsAccess.Close
Set rsAccess = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error Number = " & Err.Number & "-> " & Err.Description, vbExclamation, "CMISStatus"
Resume SubExit
End Sub
1st query:"QS_SRUpdatetoCMISdrt"
SELECT QS_TT_GeneralInfo.BEMS AS RequestorId, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, tblEquipListingPerJobGroup.MeasNo, IIf([Primary]=True,[Equipment_ID],Null) AS EquipID, IIf([Additional]=True,[Equipment_ID],Null) AS Multiple_ID, TA_SR.Job_Group, TA_SR.Project, TA_SR.Priority, TA_SR.Complete_By_Date, TA_SR.Calibration, TA_SR.Repair, TA_SR.Modification, TA_SR.Acceptance, TA_SR.Evaluation, TA_SR.Maintenance, TA_SR.Support, TA_SR.Cmis_Lab, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.Input_Range_Min, TA_SR.Input_Range_Max, TA_SR.Input_Units, TA_SR.Output_Range_Min, TA_SR.Output_Range_Max, TA_SR.Output_Units, TA_SR.Gain, TA_SR.Cutoff_Freq, TA_SR.Input_Freq, TA_SR.Ref_Freq, TA_SR.Ref_Voltage, TA_SR.Excit_Voltage, TA_SR.Excit_Enabled, TA_SR.FTIR_Accuracy, TA_SR.Offset, TA_SR.Offset_Enabled, TA_SR.REQ_EMO1, TA_SR.REQ_EMO2, TA_SR.REQ_EMO3, TA_SR.REQ_EMO4, TA_SR.REQ_EMO5, TA_SR.REQ_EMO6, TA_SR.SpareCode, TA_SR.CALIBRATION_ID, First(TA_SR.RequestorComments) AS ReqComments, "S" AS PROCESSED_IND, Now() AS LAST_UPDATE_DATE
FROM (TA_SR LEFT JOIN tblEquipListingPerJobGroup ON TA_SR.Job_Group = tblEquipListingPerJobGroup.Job_Group) LEFT JOIN QS_TT_GeneralInfo ON TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId
WHERE (((TA_SR.Job_Group)=[Forms]![FE_SRForm]![JobGroup]) AND ((TA_SR.SubmittedSR)=0))
GROUP BY QS_TT_GeneralInfo.BEMS, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, tblEquipListingPerJobGroup.MeasNo, IIf([Primary]=True,[Equipment_ID],Null), IIf([Additional]=True,[Equipment_ID],Null), TA_SR.Job_Group, TA_SR.Project, TA_SR.Priority, TA_SR.Complete_By_Date, TA_SR.Calibration, TA_SR.Repair, TA_SR.Modification, TA_SR.Acceptance, TA_SR.Evaluation, TA_SR.Maintenance, TA_SR.Support, TA_SR.Cmis_Lab, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.Input_Range_Min, TA_SR.Input_Range_Max, TA_SR.Input_Units, TA_SR.Output_Range_Min, TA_SR.Output_Range_Max, TA_SR.Output_Units, TA_SR.Gain, TA_SR.Cutoff_Freq, TA_SR.Input_Freq, TA_SR.Ref_Freq, TA_SR.Ref_Voltage, TA_SR.Excit_Voltage, TA_SR.Excit_Enabled, TA_SR.FTIR_Accuracy, TA_SR.Offset, TA_SR.Offset_Enabled, TA_SR.REQ_EMO1, TA_SR.REQ_EMO2, TA_SR.REQ_EMO3, TA_SR.REQ_EMO4, TA_SR.REQ_EMO5, TA_SR.REQ_EMO6, TA_SR.SpareCode, TA_SR.CALIBRATION_ID, "S", Now();
View 2 Replies
View Related
Jun 8, 2007
I am using Access to run my database. I am using Coldfusion to power the website. What I need to do is be able to update some fields. The problems first start in the select query, however.
There are two different forms. One that is a select box that has the options of "Buyers", "Sellers", "Newsletter". Onchange the select box (named "page"). When I submit that box it needs to populate a second box. That box is populated with the information found in the table that is selected in the first box.
Here is the query that it is using to get the information to populate:
SELECT RE.Information, RE.Page
FROM RE
WHERE (((RE.Page)="#form.page#"));
Does anyone see a problem?
Here's the result:
] Too few parameters. Expected 1.
The error occurred in D:Inetpubaheart4homeadmineditpages.cfm: line 10
8 : SELECT RE.Information, RE.Page
9 : FROM RE
10 : WHERE (((RE.Page)="#form.page#"));
11 : </cfquery>
12 : </cfif>
Thanks,
Brandon
View 2 Replies
View Related
Jul 31, 2007
What is the meaning of the (rightclick)menu, parameter option?
If i want to create a parameter query like
SELECT * FROM Table1 WHERE Field1 = [ParameterField1]
Why should i use the rightclick menu? What's the added value of this menu item?
Thx!
Guus
View 1 Replies
View Related
Mar 30, 2005
I often use forms to select a parameter & date range for a subsequent report. Where I have chosen to use multiple combo boxes (two in this case)to select more than one parameter I have run into a problem. The report opens ok the first time but if I close it (report) and change my selection then the report refuses to open. Should I just requery the two combo boxes, is there something else or can't it be fixed?
View 1 Replies
View Related
Aug 30, 2005
I need some help please on passing parameters such
as a recordsource to a report but not using OpenArgs
I heard that this can be done using a hidden form.
I have a calling form (form1) which opens a preview snapshot
form (form2) which in turn brings up a rptCurrentRecord (form3)
I can't get the openargs RecordSource SQL from form1 to form3
For arguments sake, Form3 in this case is really the report itself.
Hope this is not too confusing.
Thanks for any help
View 1 Replies
View Related
Oct 30, 2006
I have used the OpenArgs method to pass a parameter to another form, but I now need to psss two parameters, and I am not sure how to do this.
On Error GoTo Err_CmdOpenForm_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmName"
DoCmd.OpenForm stDocName, , , , , , Me.Text0 'need to pass another
'Parameter Me.Text1
Exit_CmdOpenForm_Click:
Exit Sub
Err_CmdOpenForm_Click:
MsgBox Err.Description
Resume Exit_CmdOpenForm_Click
And on the Load event of frmName, Me.Text1 = Me.OpenArgs 'and Me.Text2
View 5 Replies
View Related
Aug 30, 2004
I have a parameter query with 5 possible user entries.
The criteria for all my fields is set up as follows:
Criteria: IIf(IsNull([Enter County]), "", [Enter County])
Or: Like "*" & [Enter County] & "*"
If the user enters something for 2 or more parameters it seems to work, but if they just enter a county, for example,, it gives an error message about the expression being too complex.
Any suggestions.
Thanks.
View 9 Replies
View Related
Oct 26, 2004
I've created a database where I have all the information for people like name, address, etc. Then there's a list box to choose what events they will be attending. I want to be able to run a report and see all the information for the people who are attending any specific event. Is there a way to create a form where I can click a button then it brings up the screen to choose the parameters of my report, then generates the report? Thanks!
View 4 Replies
View Related
Jun 10, 2005
I have a table with tons of rows with different times (among other things).
I want to 'group' all the times (this would be for one date and that's fine) into 4 or 5 spans. Without doing several querys where I type in for example: between14:49 and 23:59 etc. etc. How can I do this?
View 7 Replies
View Related
Nov 2, 2006
I have write the following code but on execution gives the following error. "Too few parameters. Expected.1"
the code is myQuery = "INSERT INTO SummaryHistory (HTimeStamp, HDiaryNo, HDiaryDate, HRecivedFrom, HSentTo, HReffrenceNo, HSentDate, HPageFrom, HPageTo, HRemarks, SummaryID) "
myQuery = myQuery + " VALUES ('" & myTimeStamp & _
"', '" & myDiaryNo & _
"', " & myDiaryDate & _
", '" & myRecivedFrom & _
"', '" & mySentTo & _
"', '" & myReffrenceNo & _
"', " & mySentDate & _
", " & myPageFrom & _
", " & myPageTo & _
", '" & myRemarks & _
"', " & myID & _
" )"
CurrentDb.Execute (myQuery)
on this last line its give error
any idea
View 1 Replies
View Related
Feb 3, 2007
Hi everyone,
I'm doing up an order database that uses a report to display orders that are
faxed to the supplier.
When the report is first opened, it asks the user for the name of the
supplier (to print at the top and specify which orders to display), the date
(to also specify which orders to display), and the customer number (for the
benefit of the supplier).
Now, there are two main suppliers for this business, other suppliers are
used every now and then. Because of this, if one of these two main suppliers
is entered as a parameter initially, the corresponding customer number I
hard-code in should be displayed without having to ask the user for it. If,
however, another supplier is entered, it should ask the user for the customer
number.
This is all done in a label on the report. Currently, its control source is
set to this:
=IIf([Supplier]="supplier1","Customer
#123456",(IIf([Supplier]="supplier2","Customer #987654","Customer #" &
[Please enter customer number:])))
This works, but asks for the customer number regardless of the supplier I
enter initially.
I'm looking for a way to do something similar to above, but it shouldn't ask
for the customer number if I enter "supplier1" or "supplier2", it should use
my hard-coded values.
Any help would be much appreciated!
Thanks,
Panda.
- - - - -
Win XP Pro
Access 2003
View 3 Replies
View Related
Apr 20, 2007
I am trying to figure what code to use. What i am trying to accomplish is when the use clicks to open a report i want a form to open and make them choose from a combo box a "training activity". then the report will generate info for that activity only.
Any thoughts??
Thanks
Megan
View 7 Replies
View Related
Jul 10, 2014
I have a report that is linked to a form's List Box. Users can select Product A, Product B, Product C, etc. on the form, and the report will populate with data for the corresponding product.
I'd like to export each version of the report to Excel automatically, and I'm trying to figure out how to use VBA to enter the parameters. Is there a way to cycle through each value in the listbox?
Here's my code so far.
Code:
Sub ExportReport()
Dim ctr As String
Dim BillingTemp As String
Dim qryBilled As DAO.QueryDef
Dim dbs As DAO.Database
Set dbs = CurrentDb
[Code] ....
View 3 Replies
View Related
