Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: SQL statement is give me error 3061 Too few parameters. Expected 1

  1. #11
    New Member
    Join Date
    Oct 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    Sorry, here is the entire code: First part is the loading of the list box. The second a mod of the building of the three tags.

    Option Compare Database
    Option Explicit
    Public rstTag As Recordset
    Public rstToPrint As Recordset
    Private Sub cboEquipNumber_Click()
    Dim strSQL As String
    strSQL = "SELECT tblTag.TagNumber, tblTag.TagID, tblEquipment.Equipment, tblEquipment.EquipID " _
    & "FROM tblEquipment INNER JOIN tblTag ON tblEquipment.EquipID = tblTag.EquipID " _
    & "WHERE tblEquipment.EquipID = " & Me.cboEquipNumber & ""

    Set rstTag = CurrentDb.OpenRecordset(strSQL)

    Do Until rstTag.EOF
    Me.lstTag.AddItem (Format(rstTag!TagNumber, "0000") & ";" & rstTag!TagID)
    rstTag.MoveNext
    Loop

    rstTag.Close
    Set rstTag = Nothing
    End Sub
    Private Sub cmdAddToList_Click()
    Dim varList As Variant
    Dim strRemoveList As String
    Dim varToRemove As Variant
    Dim x As Integer

    For Each varList In lstToPrint.ItemsSelected

    lstTag.AddItem (lstToPrint.Column(0, varList) & ";" & lstToPrint.Column(1, varList))

    Next

    For Each varList In lstToPrint.ItemsSelected

    strRemoveList = strRemoveList & "," & varList

    Next

    varToRemove = Split(Mid(strRemoveList, 2), ",")

    For x = UBound(varToRemove) To 0 Step -1

    lstToPrint.RemoveItem (CInt(varToRemove(x)))

    Next x
    End Sub
    Private Sub cmdAddToPrint_Click()

    Dim varList As Variant
    Dim strRemoveList As String
    Dim varToRemove As Variant
    Dim x As Integer

    For Each varList In lstTag.ItemsSelected

    lstToPrint.AddItem (lstTag.Column(0, varList) & ";" & lstTag.Column(1, varList))

    Next

    For Each varList In lstTag.ItemsSelected

    strRemoveList = strRemoveList & "," & varList

    Next

    varToRemove = Split(Mid(strRemoveList, 2), ",")

    For x = UBound(varToRemove) To 0 Step -1

    lstTag.RemoveItem (CInt(varToRemove(x)))

    Next x



    End Sub
    Public Function BuildInList() As String
    Dim x As Integer
    Dim strIN As String
    For x = 0 To Me.lstToPrint.ListCount - 1

    If x = 0 Then
    strIN = lstToPrint.Column(1, x)
    Else
    strIN = strIN & ", " & lstToPrint.Column(1, x)
    End If

    Next x
    strIN = "(" & strIN & ")"

    BuildInList = strIN

    End Function
    Private Sub cmdLoadReport_Click()
    Dim strSQL As String
    strSQL = "SELECT * FROM qryTagReport WHERE TagID IN " & BuildInList


    'Debug.Print strSQL
    If Me.lstToPrint.ListCount = 0 Then
    Exit Sub
    Else
    'Debug.Print "cmdLoadReport: " & strSQL
    basTagReport.BuildTagReport_table strSQL

    Select Case Me.frmReportType.Value

    Case 1

    DoCmd.OpenReport "rptSmartBlind", acViewPreview

    Case 2
    DoCmd.OpenReport "rptConfinedSpaceTag", acViewPreview

    Case 3
    DoCmd.OpenReport "rptLOTOTag", acViewPreview

    End Select

    End If

    End Sub

    'Second Part:

    Option Compare Database
    Option Explicit
    Private Type TagReport

    TagNumber As Long
    Location As String
    JobNumber As String
    Equipment As String
    DrawingRef As String
    SizeRating As String
    Type As String
    FirstPosition As String
    SecondPosition As String
    Service As String
    SpreadSide As String
    AirJob As String

    End Type

    Public Sub BuildTagReport_table(strQuery As String)
    Dim TAG1 As TagReport
    Dim TAG2 As TagReport
    Dim TAG3 As TagReport
    Dim rst As Recordset
    Dim intColumn As Integer
    Dim x As Integer
    Dim intRecord As Long
    Dim strSQL As String

    On Error GoTo ErrHandler

    strSQL = "DELETE * from tblTagReport"

    ' Erase the Temporary table
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    intColumn = 1

    ' Change DataSource to the Name of the Query which will hold the filtered Records

    Set rst = CurrentDb.OpenRecordset(strQuery)
    rst.MoveLast
    x = rst.RecordCount
    rst.MoveFirst

    For intRecord = 1 To x

    Select Case intColumn
    Case 1
    TAG1.TagNumber = Nz(rst!TagNumber, " ")
    TAG1.Location = Nz(rst!Location, " ")
    TAG1.Equipment = Nz(rst!Equipment, " ")
    TAG1.DrawingRef = Nz(rst!DrawingRef, " ")
    TAG1.JobNumber = Nz(rst!JobNumber, " ")
    TAG1.SizeRating = Nz(rst!SizeRating, " ")
    TAG1.Type = Nz(rst!Type, " ")
    TAG1.Service = Nz(rst!Service, " ")
    TAG1.SpreadSide = Nz(rst!SpreadSide, " ")
    TAG1.FirstPosition = Nz(rst!FirstPosition, " ")
    TAG1.SecondPosition = Nz(rst!SecondPosition, " ")
    TAG1.AirJob = Nz(rst!AirJob, " ")
    intColumn = 2
    rst.MoveNext
    Case 2

    TAG2.TagNumber = Nz(rst!TagNumber, " ")
    TAG2.Location = Nz(rst!Location, " ")
    TAG2.Equipment = Nz(rst!Equipment, " ")
    TAG2.DrawingRef = Nz(rst!DrawingRef, " ")
    TAG2.JobNumber = Nz(rst!JobNumber, " ")
    TAG2.SizeRating = Nz(rst!SizeRating, " ")
    TAG2.Type = Nz(rst!Type, " ")
    TAG2.Service = Nz(rst!Service, " ")
    TAG2.SpreadSide = Nz(rst!SpreadSide, " ")
    TAG2.FirstPosition = Nz(rst!FirstPosition, " ")
    TAG2.SecondPosition = Nz(rst!SecondPosition, " ")
    TAG2.AirJob = Nz(rst!AirJob, " ")
    intColumn = 3
    rst.MoveNext
    Case 3

    TAG3.TagNumber = Nz(rst!TagNumber, " ")
    TAG3.Location = Nz(rst!Location, " ")
    TAG3.Equipment = Nz(rst!Equipment, " ")
    TAG3.DrawingRef = Nz(rst!DrawingRef, " ")
    TAG3.JobNumber = Nz(rst!JobNumber, " ")
    TAG3.SizeRating = Nz(rst!SizeRating, " ")
    TAG3.Type = Nz(rst!Type, " ")
    TAG3.Service = Nz(rst!Service, " ")
    TAG3.SpreadSide = Nz(rst!SpreadSide, " ")
    TAG3.FirstPosition = Nz(rst!FirstPosition, " ")
    TAG3.SecondPosition = Nz(rst!SecondPosition, " ")
    TAG3.AirJob = Nz(rst!AirJob, " ")
    intColumn = 1
    BuildReport TAG1, TAG2, TAG3
    rst.MoveNext

    End Select

    Next intRecord
    Dim tagBlank As TagReport
    Select Case x Mod 3
    Case 0
    ' BuildReport TAG1, TAG2, TAG3
    Case 1
    BuildReport TAG1, tagBlank, tagBlank
    Case 2
    BuildReport TAG1, TAG2, tagBlank

    End Select


    Set rst = Nothing
    ExitHere:
    Exit Sub
    ErrHandler:

    GoTo ExitHere

    End Sub

    Public Sub BuildReport(TAG1 As TagReport, TAG2 As TagReport, TAG3 As TagReport)
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("tblTagReport")

    rst.AddNew
    rst!TagNumber1 = TAG1.TagNumber
    rst!Location1 = TAG1.Location
    rst!JobNumber1 = TAG1.JobNumber
    rst!Equipment1 = TAG1.Equipment
    rst!DrawingRef1 = TAG1.DrawingRef
    rst!SizeRating1 = TAG1.SizeRating
    rst!Type1 = TAG1.Type
    rst!FirstPosition1 = TAG1.FirstPosition
    rst!SecondPosition1 = TAG1.SecondPosition
    rst!SpreadSide1 = TAG1.SpreadSide
    rst!Service1 = TAG1.Service
    rst!AirJob1 = TAG1.AirJob

    rst!TagNumber2 = TAG2.TagNumber
    rst!Location2 = TAG2.Location
    rst!JobNumber2 = TAG2.JobNumber
    rst!Equipment2 = TAG2.Equipment
    rst!DrawingRef2 = TAG2.DrawingRef
    rst!SizeRating2 = TAG2.SizeRating
    rst!Type2 = TAG2.Type
    rst!FirstPosition2 = TAG2.FirstPosition
    rst!SecondPosition2 = TAG2.SecondPosition
    rst!SpreadSide2 = TAG2.SpreadSide
    rst!Service2 = TAG2.Service
    rst!AirJob2 = TAG2.AirJob

    rst!TagNumber3 = TAG3.TagNumber
    rst!Location3 = TAG3.Location
    rst!JobNumber3 = TAG3.JobNumber
    rst!Equipment3 = TAG3.Equipment
    rst!DrawingRef3 = TAG3.DrawingRef
    rst!SizeRating3 = TAG3.SizeRating
    rst!Type3 = TAG3.Type
    rst!FirstPosition3 = TAG3.FirstPosition
    rst!SecondPosition3 = TAG3.SecondPosition
    rst!SpreadSide3 = TAG3.SpreadSide
    rst!Service3 = TAG3.Service
    rst!AirJob3 = TAG3.AirJob
    rst.Update

    Set rst = Nothing
    End Sub

  2. #12
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,467
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    That might be too much information now.

    It loooks like the problem is in how you are using strSQL, in the sub cboEquipNumber_Click()

    What is the value of me.cboEquipNumber when you are running this code?

    For example,

    Edit this sub:
    Code:
    Private Sub cboEquipNumber_Click()
    
    Dim strSQL As String
    strSQL = "SELECT tblTag.TagNumber, tblTag.TagID, tblEquipment.Equipment, tblEquipment.EquipID " _
    & "FROM tblEquipment INNER JOIN tblTag ON tblEquipment.EquipID = tblTag.EquipID " _
    & "WHERE tblEquipment.EquipID = " & Me.cboEquipNumber & ""
    
    Set rstTag = CurrentDb.OpenRecordset(strSQL)
    
    Do Until rstTag.EOF
        Me.lstTag.AddItem (Format(rstTag!TagNumber, "0000") & ";" & rstTag!TagID)
        rstTag.MoveNext
    Loop
    
    rstTag.Close
    Set rstTag = Nothing
    
    End Sub

    Change it to this:
    Code:
    Private Sub cboEquipNumber_Click()
    
    MsgBox Me.cboEquipNumber
    
    End Sub
    • What value does the message box display?
    • Is it the correct value for an EquipID?
    • What is the datatype of EquipID in the underlying table (tblEquipment) - is it numeric or string/text?




    Next also you can change it to this:
    Code:
    Private Sub cboEquipNumber_Click()
    
    Dim strSQL As String
    strSQL = "SELECT tblTag.TagNumber, tblTag.TagID, tblEquipment.Equipment, tblEquipment.EquipID " _
    & "FROM tblEquipment INNER JOIN tblTag ON tblEquipment.EquipID = tblTag.EquipID " _
    & "WHERE tblEquipment.EquipID = " & Me.cboEquipNumber & ""
    
    MsgBox strSQL
    
    End Sub

    • Is that good sql?
    • Can it be run as is?
    • Can you put it into a new query as raw sql and actually run it with correct results?


    Try to keep in mind that your goal is to produce a raw SQL string that is correct and can be run as is. So all you need to really do it keep tweaking this until strSQL is correct and runnable as is. As far as quotes or no quotes the rule is simple - text goes in quotes, numbers do not.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #13
    New Member
    Join Date
    Oct 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    Answers to your questions:
    What value does the message box display?
    (The value is a list of Equipment. It shows the equipID when running code)

    What value does the message box display? (1 which is the equipID for that piece of equipment)
    Is it the correct value for an EquipID? (Yes)
    What is the datatype of EquipID in the underlying table (tblEquipment) - is it numeric or string/text? (Auto Number "Primary Key "Long Integer)


    • Is that good sql? (Yes)
    • Can it be run as is? (Yes)
    • Can you put it into a new query as raw sql and actually run it with correct results?(Yes)


    When I run it from the raw query it works perfect. But when I run it from the combo box it populates the list box with the correct information but does not populate the report, it is blank. Can not understand what is happening.

    The SQL populates a list box. Then I choose records out of the list box to run a report, but the report does not populate with the records. Everything you have done works fine, just not getting to the report.


    Last edited by bama4954; Aug 16th, 2019 at 06:15 PM.

  4. #14
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,467
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    When I run it from the raw query it works perfect. But when I run it from the combo box it populates the list box with the correct information but does not populate the report, it is blank. Can not understand what is happening.
    The sql appears to be working correctly. I cannot understand what is happening either. Why would sql from a combobox populate a report? Its job is to populate the combobox.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #15
    New Member
    Join Date
    Oct 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    I finally figured it out. Thanks to everyone that helped me through this. I just had to walk away from it for a bit it just popped into my head what I was doing wrong. It is working great now but I could not have done it without every ones help. Thanks again!!

  6. #16
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,467
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    I just had to walk away from it for a bit it just popped into my head what I was doing wrong.
    Yes this is another good debugging tip for all of us.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #17
    New Member
    Join Date
    Oct 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    So now I am wanting to change the combo box to search for JobNumber instead of EquipID. Here is the new sql statement. I am getting the same error: Missing Parameter expected 1. What am I doing wrong?

    strSQL = "SELECT tblTag.TagNumber, tblTag.TagID " & vbCrLf & _
    "FROM tblTag " & vbCrLf & _
    "WHERE tblTag.JobNumber = & [Forms]![frmTagReport]![cboJobID] & ;"

  8. #18
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,467
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    What is the jobID you are testing on?

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  9. #19
    New Member
    Join Date
    Oct 2018
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    JobID is a number. 384456

  10. #20
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,467
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SQL statement is give me error 3061 Too few parameters. Expected 1

    Okay well the basic rule for testing is:

    1) does the raw sql work.
    2) does the string I have created match the raw sql I wanted to create?

    In this case, the raw sql is:
    Code:
    SELECT tblTag.TagNumber, tblTag.TagID 
    FROM tblTag 
    WHERE tblTag.JobNumber = 384456;
    and the string you have created is:
    Code:
    SELECT tblTag.TagNumber, tblTag.TagID 
    FROM tblTag 
    WHERE tblTag.JobNumber = & [Forms]![frmTagReport]![cboJobID] & ;
    As you can see, they don't match.

    What you meant (probably) was:
    Code:
    strSQL = "SELECT tblTag.TagNumber, tblTag.TagID " & vbCrLf & _
    "FROM tblTag " & vbCrLf & _
    "WHERE tblTag.JobNumber = " & [Forms]![frmTagReport]![cboJobID] & ";"

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •