0x80040e10 Too Few Parameters Error
Sep 9, 2005
I could use some help. I am not a programmer, I am helping someone out.
We have an access database that is accessed using asp webpages. When trying to add students to the database we get the following error
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. line 51
Below is the code on the page.
If ((request("REQUEST_METHOD") = "POST") and ((request("CONTENT_LENGTH") > 0) and (request("CONTENT_LENGTH") < 14500))) then %>
<%
LastName = request("Last_Name")
FirstName = Request("First_Name")
MiddleName = request("Middle_Name")
Student = request("Student_Number")
WEng316 = request("WEng316")
WChEn475 = request("WChEn475")
WChEn477 = request("WChEn477")
OChEn391 = request("OChEn391")
OChEn451 = request("OChEn451")
TChEn475 = request("TChEn475")
TChEn477 = request("TChEn477")
TChEn451 = request("TChEn451")
YearOfTest = request("YearOf_Test")
sValue = request("submitValue")
strProvider="Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("iisadmin") & "websitelevel3_questions.mdb;"
set cm = Server.CreateObject("ADODB.Command")
set objConn = server.createobject("ADODB.Connection")
objConn.Open strProvider
cm.ActiveConnection = objConn
insertString = "INSERT INTO Students(LastName,FirstName,MiddleName,StudentNumb er,YearOfTest,Writ316,Writ475,Writ477,Oral391,Oral 451,TW451,TW475,TW477) "
insertString = insertString & "VALUES ('"&LastName&"','"&FirstName&"','"&MiddleName&"',"&Student&","&YearOfTest&","&WEng316
insertString = insertString & ","&WChEn475&","&WChEn477&","&OChEn391&","&OChEn451&","&TChEn451&","&TChEn475&","&TChEn477&")"
cm.CommandText = insertString
'cm.CommandText="INSERT INTO Students(LastName,FirstName,MiddleName,StudentNumb er) VALUES (?, ?, ?, ?)"
'Set objParam=cm.CreateParameter(, 200, , 255, LastName)
'cm.Parameters.Append objParam
'Set objParam=cm.CreateParameter(, 200, , 255, FirstName)
'cm.Parameters.Append objParam
'Set objParam=cm.CreateParameter(, 201, , 255, MiddleName)
'cm.Parameters.Append objParam
'Set objParam=cm.CreateParameter(, 201, , 255, Student)
'cm.Parameters.Append objParam
cm.Execute
%>
<% If (sValue = "1") then
Response.Redirect("viewStudents.asp")
Else %>
<HTML>
<HEAD>
<TITLE>Add Student
</TITLE>
<!--#include virtual ="/byu/admin/adminInclude.inc"-->
<%
call stylesheet
%>
<script language=javascript>
<!--
function form_submit(where)
{
if (where=="return")
{
document.form.submitValue.value=1;
} else {
document.form.submitValue.value=2;
}
var err = 0;
if (document.form.Student_Number.value == "")
{
err = 1;
alert("Please enter student's number.");
} else if (document.form.First_Name.value == "") {
err = 1;
alert("Please enter student's first name.");
} else if (document.form.Last_Name.value == "") {
err = 1;
alert("Please enter student's last name.");
}
if (document.form.WEng316.value == "")
document.form.WEng316.value = "0";
if (document.form.WChEn475.value == "")
document.form.WChEn475.value = "0";
if (document.form.WChEn477.value == "")
document.form.WChEn477.value = "0";
if (document.form.OChEn391.value == "")
document.form.OChEn391.value = "0";
if (document.form.OChEn451.value == "")
document.form.OChEn451.value = "0";
if (document.form.TChEn451.value == "")
document.form.TChEn451.value = "0";
if (document.form.TChEn475.value == "")
document.form.TChEn475.value = "0";
if (document.form.TChEn477.value == "")
document.form.TChEn477.value = "0";
if (document.form.YearOf_Test.value == "")
document.form.YearOf_Test.value = "0";
if (err == 0)
{
document.form.submit();
}
}
function loadMe()
{
alert("Student added successfully.");
}
-->
</script>
</HEAD>
<BODY bottomMargin=15 leftMargin=15 topMargin=15 rightMargin=15 marginwidth="0" marginheight="0" onLoad="loadMe();">
<%
call header
%>
<FORM NAME="form" METHOD=POST ACTION="addStudents.asp">
<INPUT TYPE=hidden name=submitValue >
<table cellSpacing=0 cellPadding=0 BORDER=0 WIDTH="95%" align=center>
<tr>
<td colspan=3 > </td>
</tr>
<tr>
<TD> </TD>
<td colspan=0 ><span class=admintext>First name:</span></td>
<TD><INPUT TYPE='text' NAME='First_Name' Size='25' MAXLENGTH='254'></TD>
</tr>
<tr>
<TD> </TD>
<td ><span class=admintext>Middle name:</span></td>
<TD><INPUT TYPE='text' NAME='Middle_Name' Size='25' MAXLENGTH='254'></TD>
</tr>
<tr>
<TD> </TD>
<td colspan=0 ><span class=admintext>Last name:</span></td>
<TD><INPUT TYPE='text' NAME='Last_Name' Size='25' MAXLENGTH='254'></TD>
</tr>
<tr>
<TD> </TD>
<td colspan=0 ><span class=admintext>Student number:</span></td>
<TD><INPUT TYPE='text' NAME='Student_Number' Size='10' MAXLENGTH='254'></TD>
</tr>
<tr>
<TD> </TD>
<td colspan=0 ><span class=admintext>Written Communication:</span></td>
<TD><span class=admintext>Engl 316 </span>
<INPUT TYPE='text' NAME='WEng316' Size='5' MAXLENGTH='3'>
<span class=admintext>ChEn 475 </span>
<INPUT TYPE='text' NAME='WChEn475' Size='5' MAXLENGTH='3'>
<span class=admintext>ChEn 477 </span>
<INPUT TYPE='text' NAME='WChEn477' Size='5' MAXLENGTH='3'>
</TD>
</tr>
<tr>
<TD> </TD>
<td colspan=0 ><span class=admintext>Oral communication:</span></td>
<TD><span class=admintext>ChEn 391 </span>
<INPUT TYPE='text' NAME='OChEn391' Size='5' MAXLENGTH='3'>
<span class=admintext>ChEn 451 </span>
<INPUT TYPE='text' NAME='OChEn451' Size='5' MAXLENGTH='3'>
</TD>
</tr>
<tr>
<TD> </TD>
<td colspan=0 ><span class=admintext>Teamwork:</span></td>
<TD><span class=admintext>ChEn 451 </span>
<INPUT TYPE='text' NAME='TChEn451' Size='5' MAXLENGTH='3'>
<span class=admintext>ChEn 475 </span>
<INPUT TYPE='text' NAME='TChEn475' Size='5' MAXLENGTH='3'>
<span class=admintext>ChEn 477 </span>
<INPUT TYPE='text' NAME='TChEn477' Size='5' MAXLENGTH='3'>
</TD>
</tr>
<tr>
<TD> </TD>
<td colspan=0 ><span class=admintext>Year of test:</span></td>
<TD><INPUT TYPE='text' NAME='YearOf_Test' Size='10' MAXLENGTH='254'> <span class=admintext>(Example:<b>Y2002-03</b>)</span></TD>
</tr>
<tr>
<td colspan=3 > </td>
</tr>
<tr>
<TD> </TD>
<td colspan=2 >
<table cellSpacing=2 cellPadding=0 BORDER=0 align=left >
<tr><td>
<table cellSpacing=2 cellPadding=0 BORDER=1 align=left bgcolor=#003366>
<tr><td bgcolor=#cccccc> <a href="javascript:form_submit('return');" class=adminlink><b>Save and Return</b></a>
</td></tr>
</table>
</td>
<td> </td>
<td>
<table cellSpacing=2 cellPadding=0 BORDER=1 align=left bgcolor=#003366>
<tr><td bgcolor=#cccccc> <a href="javascript:form_submit('stay');" class=adminlink><b>Save and Add another</b></a>
</td></tr>
</table>
</td></tr>
</table>
</td>
</tr>
</table>
</FORM>
<% End If %>
<% Else %>
<HTML>
<HEAD>
<TITLE>Add Student
</TITLE>
<!--#include virtual ="/byu/admin/adminInclude.inc"-->
<%
call stylesheet
%>
<script language=javascript>
<!--
function form_submit(where)
{
if (where=="return")
{
document.form.submitValue.value=1;
} else {
document.form.submitValue.value=2;
}
var err = 0;
if (document.form.Student_Number.value == "")
{
err = 1;
alert("Please enter student's number.");
} else if (document.form.First_Name.value == "") {
err = 1;
alert("Please enter student's first name.");
} else if (document.form.Last_Name.value == "") {
err = 1;
alert("Please enter student's last name.");
}
if (document.form.WEng316.value == "")
document.form.WEng316.value = "0";
if (document.form.WChEn475.value == "")
document.form.WChEn475.value = "0";
if (document.form.WChEn477.value == "")
document.form.WChEn477.value = "0";
if (document.form.OChEn391.value == "")
document.form.OChEn391.value = "0";
if (document.form.OChEn451.value == "")
document.form.OChEn451.value = "0";
if (document.form.TChEn451.value == "")
document.form.TChEn451.value = "0";
if (document.form.TChEn475.value == "")
document.form.TChEn475.value = "0";
if (document.form.TChEn477.value == "")
document.form.TChEn477.value = "0";
if (err == 0)
{
document.form.submit();
}
}
-->
</script>
</HEAD>
<BODY bottomMargin=15 leftMargin=15 topMargin=15 rightMargin=15 marginwidth="0" marginheight="0">
<%
call header
%>
please help ASAP
thank you
View Replies
ADVERTISEMENT
Feb 11, 2008
Hi All,I am getting problem "Error: too few parameters. Expected 1" when following Query is executed to updated a Flag Value in a table on Click event of a Submit button. CurrentDb.Execute "UPDATE Scheduled_Appointment SET Is_Taken = 1 WHERE Scheduled_Appointment_ID LIKE Me.Sch_P_ID"Where:Table: Scheduled_AppointmentColumn: Scheduled_Appointment_ID [Primary Key]Column: Is_Taken [ColumnType = Number ]Text Field: Me.Sch_P_ID [contains the Scheduled_Appointment_ID value for the selected Record on the Form]Thanks in Advance.
View 2 Replies
View Related
Jul 1, 2005
Hello Access friends,
What is wrong with the following code (modified the module from http://members.iinet.net.au/~allenbrowne/AppInventory.html ):
I Keep getting a runtime error 3061 Too few parameters . Expected 1 on the line highlight below.
I have the reference MS DAO 3.6 selected and I am using access 2000 and calling the module from a command button in a form.
Thanks in advance for taking the time to help me out.
Dim db As DAO.Database 'CurrentDb()
Dim rs As DAO.Recordset 'Various recordsets.
Dim strProduct As String 'vProductID as a string.
Dim strAsOf As String 'vAsOfDate as a string.
Dim strSTDateLast As String 'Last Stock Take Date as a string.
Dim strDateClause As String 'Date clause to use in SQL statement.
Dim strSQL As String 'SQL statement.
Dim lngQtyLast As Long 'Quantity at last transaction.
Dim lngQtyAcq As Long 'Quantity acquired since incoming transaction.
Dim lngQtyUsed As Long 'Quantity used since outgoing transaction.
If Not IsNull(vProductID) Then
'Initialize: Validate and convert parameters.
Set db = CurrentDb()
strProduct = vProductID
If IsDate(vAsOfDate) Then
strAsOf = "#" & Format$(vAsOfDate, "mm/dd/yyyy") & "#"
End If
'Get the last transaction date and quantity for this product.
If Len(strAsOf) > 0 Then
strDateClause = " AND ([TransacDate] <= " & strAsOf & ")"
End If
strSQL = "SELECT TOP 1 [TransacDate], [Quantity] FROM [Transactions] " & _
"WHERE ((ProductID = " & strProduct & ")" & strDateClause & _
") ORDER BY TransacDate DESC;"
Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then
strSTDateLast = "#" & Format$(![TransacDate], "mm/dd/yyyy") & "#"
lngQtyLast = Nz(!Quantity, 0)
End If
End With
rs.Close
'Build the Date clause
If Len(strSTDateLast) > 0 Then
If Len(strAsOf) > 0 Then
strDateClause = " Between " & strSTDateLast & " And " & strAsOf
Else
strDateClause = " >= " & strSTDateLast
End If
Else
If Len(strAsOf) > 0 Then
strDateClause = " <= " & strAsOf
Else
strDateClause = vbNullString
End If
End If
'Get the quantity acquired since then.
strSQL = "SELECT Sum([Transactions].[Quantity]) AS QuantityAcq " & _
"FROM [Transactions]" & _
"WHERE (([Transactions].[ProductID] = " & strProduct & ") AND ([Transactions].[TransacType] = 'Incoming')"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND ([Transactions].[TransacDate] " & strDateClause & "));"
End If
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngQtyAcq = Nz(rs!QuantityAcq, 0)
End If
rs.Close
'Get the quantity used since then.
strSQL = "SELECT Sum([Transactions].[Quantity]) AS QuantityUsed " & _
"FROM [Transactions]" & _
"WHERE (([Transactions].[ProductID] = " & strProduct & ") AND ([Transactions].[TransacType] = 'Outgoing')"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND ([Transactions].[TransacDate] " & strDateClause & "));"
End If
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngQtyUsed = Nz(rs!QuantityUsed, 0)
End If
rs.Close
'Assign the return value
OnHand = lngQtyLast + lngQtyAcq - lngQtyUsed
End If
Set rs = Nothing
Set db = Nothing
Exit Function
End Function
View 3 Replies
View Related
Jun 15, 2005
I am running this code, and i am getting this error:
Code:Private Sub SendFormToConsultants_Click() On Error GoTo Err_SendFormToConsultants_Click Dim stWhere As String '-- Criteria for DLookup Dim varTo As Variant '-- Address for SendObject Dim stText As String '-- E-mail text Dim stSubject As String '-- Subject line of e-mail Dim stCOFNumber As String '-- The COF Number from form Dim stCustomerID As String '-- The Customer ID from form Dim stCompanyName As String '-- The Company Name from form Dim stContactName As String '-- The Contact Name from form Dim stAddress As String '-- The Company Address from form Dim stTRDW As String '-- The TRDW from form Dim stPreReq As String '-- The PreReq from form Dim stWorkLoc As String '-- The Location of Work from form Dim stDelivActiv As String '-- The Deliverables/Activities from form Dim stStartDate As Date '-- The Start Date from Subform Dim stEndDate As Date '-- The End Date from Subform Dim stWho As String '-- Reference to Resources Dim strSQL As String '-- Create SQL update statement Dim errLoop As Error '-- Combo of names to assign COF to stWho = Me.COF_Scheduled__Assigned_Resources__Subform1!Res ourceName stWhere = "Resources.ResourceName = " & "'" & stWho & "'" '-- Looks up email address from Resources varTo = DLookup("[ResourceEmail]", "Resources", stWhere) stCOFNumber = Me!COFNumber stCustomerID = Me.Consultancy_Order_Form_CustomerID stCompanyName = Me.CompanyName stContactName = Me!COFContact stAddress = Me.Address stTRDW = Me.TRDW stPreReq = Me.PreRequisites stWorkLoc = Me.WorkLocation stDelivActiv = Me.DeliverablesActivities stStartDate = Me.COF_Scheduled__Assigned_Resources__Subform1!Sta rtDate stEndDate = Me.COF_Scheduled__Assigned_Resources__Subform1!End Date stSubject = ":: New Consultancy Order Assigned ::" stText = "You have been assigned a new Consultancy Order." & vbCrLf & _ "Consultancy Order Form Number: " & stCOFNumber & _ vbCrLf & _ "Company ID: " & stCustomerID & _ vbCrLf & _ "Company Name: " & stCompanyName & _ vbCrLf & _ "Contact Name: " & stContactName & _ vbCrLf & _ "Address: " & stAddress & _ vbCrLf & _ "Terms of Reference / Description of Work: " & stTRDW & _ vbCrLf & _ "Pre-Requisites: " & stPreReq & _ vbCrLf & _ "Location of Work: " & stWorkLoc & _ vbCrLf & _ "Deliverables / Activities: " & stDelivActiv & _ vbCrLf & _ "Start Date: " & stStartDate & _ vbCrLf & _ "End Date: " & stEndDate & _ vbCrLf & _ "Please reply to confirm Consultancy Order Assignment." 'Write the e-mail content for sending to Consultant DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1 'Set the update statement to disable command button once e-mail is sent strSQL = "UPDATE [Consultancy Order Form] SET [Consultancy Order Form].COFSentToConsultants = 0 " & _ "Where [Consultancy Order Form].COFNumber = " & Me!COFNumber & ";" On Error GoTo Err_Execute CurrentDb.Execute strSQL, dbFailOnError On Error GoTo 0 'Requery checkbox to show checked 'after update statement has ran 'and disable send mail command button Me!COFSentToConsultants.Requery Me!COFSentToConsultants.SetFocus Me.SendFormToConsultants.Enabled = False Exit SubErr_Execute: ' Notify user of any errors that result from ' executing the query. If DBEngine.Errors.Count > 0 Then For Each errLoop In DBEngine.Errors MsgBox "Error number: " & errLoop.Number & vbCr & _ errLoop.Description Next errLoop End If Resume NextExit_SendFormToConsultants_Click: Exit SubErr_SendFormToConsultants_Click: MsgBox Err.Description Resume Exit_SendFormToConsultants_ClickEnd Sub
What does it mean? it doesn't say where i have a problem in my code. What do you think?
View 3 Replies
View Related
Jul 9, 2013
Access 2003
This statement works great.
Code:
db.Execute "INSERT INTO TBLFILESTEMP (TextFile) VALUES (""" & Replace$(strDelimiter & vbNewLine & var, """", """""") & """);"
I'd like to include FileID (number Long Integer) and use the value from MyForm RecordID (autonumber)
This gives me a Run-Time error '3061': Too few parameters. Expected 1.
Code:
db.Execute "INSERT INTO TBLFILESTEMP (TextFile, FileID) VALUES (""" & Replace$(strDelimiter & vbNewLine & var, """", """""") & """, me.RecordID);"
View 3 Replies
View Related
Sep 29, 2015
I have the error 80040e10-No value given for one or more required parameter inside a SQL string and I know that the problem is some/one of my variables passed, however I cannot found the mistake. See down here my code. I got experience in VBA but no much in ACCESS so the query have some problems.
I got the following parameters used inside of the string (I can see before open the recordset that all of them have valid values);
TotalGj is a number result of a dsumTemp1 is a textMketer is a text
My code is down here
Sub Enterdata()
Dim Mketer As String, MonthBR As String
Dim HECAmount As Currency
Dim RsCustomers As ADODB.Recordset, RsFfeesMonth As ADODB.Recordset
Dim Cnx As ADODB.Connection
[Code] .....
View 5 Replies
View Related
Sep 18, 2014
I have been struggling with getting the syntax right for the ConcatRelate function. I have looked at other peoples examples and mine seems to have exactly the same syntax but it is giving me an error.
My Sql is
SELECT qr_RiverGroup.River, ConcatRelated("Site_ID","qr_RiverGroup","[River] = '" & [River] & "'") AS Expr1
FROM qr_RiverGroup;
View 7 Replies
View Related
Sep 9, 2013
I have a library function that will allow the user to nominate a query (as one of its arguments) in the calling application which must have an email field. The function will then Do Loop the email field, concatenating it before creating an email and addressing it. The intended functionality is that a developer can easily create a group email, just by creating a query.
This works fine if the query is filtered "statically" - i.e. I specify which group of people by typing in their "Site_ID" in the criteria. However I want developers to be able to creating dynamically filtered queries (perhaps by the group's ID on a calling form). Within the query (to test it), the filter is therefore [Forms]![Test Function Calls]![Site_id]. When I run the code, I am then presented with "Run-time error 3061: Too few parameters. Expected 1". The code in question is:
Dim rst As DAO.Recordset
Dim stTo As String 'one of the function's arguments received from the calling function.
Dim stToString As String 'the built up concatenated emails
Set rst = CurrentDb.OpenRecordset(stTo, dbOpenDynaset, dbSeeChanges)
[Code] ....
View 6 Replies
View Related
Jun 11, 2013
Here's my Goal: To open a saved query that has a parameter, setting that parameter via a VBA sub.
Here's my Problem: I was getting various errors, but after debugging my program a bit, it comes down to a "Data Type Conversion Error"
Here's my Code:
Set db = CurrentDb
Set qd = db.QueryDefs("qryMY_DATA")
qd.Parameters(0) = Me.txt_ReferenceID
Set rs = qd.OpenRecordset("qryMY_DATA", dbDynaset)
Code:
'*** Database Variables
Dim db As DAO.Database, rs As DAO.Recordset, gq As DAO.QueryDef, prm As DAO.Recordset
I've been all over the forums and tried several different approaches, all to no avail. The Query runs fine in the QDT, but kicks back an error when I try to run it from my sub.
View 10 Replies
View Related
Oct 17, 2006
I am trying to pass parameters to my qury thru my combo selection. I keep getting this error "Data type mismatch criteria expression", does anyone have an idea why?
WHERE (((fShiftWorked([tblTimeLog].[timeStart])=[Forms]![frmOperatorWorkDone]![cboShift] Or IsNull([Forms]![frmOperatorWorkDone]![cboShift]))=True));
I have spent so much time onthis already and i am sick of it :mad:
Attached is my db. Please help me out here.
View 2 Replies
View Related
Aug 12, 2015
I keeping getting the error "Invalid use of Null" when i call my email function using optional parameters.
Code:
Email Me.ProjectID.Column(1), _
Me.ProjectAddress, _
Me.TaskDescID.Column(1), _
Me.TaskDescExt, _
strStatus, _
Me.TaskStatusID.Column(1), _
[Code] .....
View 4 Replies
View Related
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
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 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
