Results 1 to 4 of 4

DAO.RecordSet looking for different types of data (integer and string)

  1. #1
    sparky23 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    9

    DAO.RecordSet looking for different types of data (integer and string)

    I'm struggling to make SQL query that search through one table that contain row with exact all parameters given in variables (integer and string).

    I got error "Too few parameters. Expected 1"

    Here is my code:


    Private Sub RoomCombo_AfterUpdate()


    Dim r As DAO.Recordset
    Dim sSQL As String
    Dim FN As Integer 'FRA Number
    Dim RN As String 'Room Name


    'gather not-defaultet panels
    FRACombo.SetFocus
    FN = Me.FRACombo.Text
    RoomCombo.SetFocus
    RN = Me.RoomCombo.Text



    'open recordset to get panel ports number
    sSQL = "SELECT NumberOfPorts"
    sSQL = sSQL & " FROM tblPort_Number_Exception10"
    sSQL = sSQL & " WHERE [FRA] = " & FN & " AND [Room] LIKE " & RN

    'I got error here
    Set r = CurrentDb.OpenRecordset(sSQL)
    Me.Side_A = r!NumberOfPorts


    If r!NumberOfPorts = 48 Then


    Port_A_Combo.RowSource = "SELECT Port.PortID, Port.Port_48 FROM Port;"

    ElseIf r!NumberOfPorts = 12 Then Port_A_Combo.RowSource = "SELECT Port.PortID, Port.Port_12 FROM Port;"

    Else: Port_A_Combo.RowSource = "SELECT Port.PortID, Port.Port_Default FROM Port;"

    End If
    r.Close
    Set r = Nothing




    End Sub

    Please help

  2. #2
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,362
    As RN is a string you need to escape it with quotes - in simplest ways you would do this;

    Code:
    sSQL = "SELECT NumberOfPorts"
    sSQL = sSQL & " FROM tblPort_Number_Exception10"
    sSQL = sSQL & " WHERE [FRA] = " & FN & " AND [Room] LIKE '" & RN & "'"
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,787
    This should help:

    http://www.baldyweb.com/ImmediateWindow.htm

    There is no need to set focus to your combos if you use the .Value property rather than .Text.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    sparky23 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    9
    Thank you Minty.
    This did the magic

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Convert string to integer
    By AmanKaur123 in forum Programming
    Replies: 2
    Last Post: 01-16-2017, 09:35 AM
  2. Integer Data Type
    By dvgef2 in forum Access
    Replies: 3
    Last Post: 06-08-2013, 10:03 AM
  3. Adding column as INTEGER makes it a long integer?
    By luckycharms in forum Programming
    Replies: 2
    Last Post: 10-20-2010, 02:47 PM
  4. Using a string to DIM a Recordset
    By ColPat in forum Programming
    Replies: 10
    Last Post: 09-25-2010, 03:53 PM
  5. Replies: 1
    Last Post: 07-31-2009, 03:57 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums