Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169

    Unanswered: SQL Query Problem

    I am trying to create a switchboard in access and do an SQL query that filter the switchboard depending on what there security level is. This is the code

    Const conNumButtons = 8


    Dim rs As DAO.Recordset
    Dim stSql As String
    Dim intOption As Integer
    Dim dbs As DAO.Database

    ' Set the focus to the first button on the form,
    ' and then hide all of the buttons on the form
    ' but the first. You can't hide the field with the focus.
    Me![Option1].SetFocus
    For intOption = 2 To conNumButtons
    Me("Option" & intOption).Visible = False
    Me("OptionLabel" & intOption).Visible = False
    Next intOption

    ' Open the table of Switchboard Items, and find
    ' the first item for this Switchboard Page.
    Set dbs = CurrentDb()


    stSql = stSql & "SELECT DISTINCT [Switchboard Items].[Security Number], [Switchboard Items].ItemNumber, [Switchboard Items].SwitchboardID, *"
    stSql = stSql & "FROM [Switchboard Items]"
    stSql = stSql & "WHERE ((([Switchboard Items].[Security Number]) > [Forms]![Password Login]![User Level]) And (([Switchboard Items].ItemNumber) > 0) And (([Switchboard Items].SwitchboardID) = [Me]![SwitchboardID]))"
    stSql = stSql & "ORDER BY [Switchboard Items].ItemNumber;"

    Set rs = dbs.OpenRecordset(stSql)

    ' If there are no options for this Switchboard Page,
    ' display a message. Otherwise, fill the page with the items.
    If (rs.EOF) Then
    Me![OptionLabel1].Caption = "There are no items for this switchboard page"
    Else
    While (Not (rs.EOF))
    Me("Option" & rs![ItemNumber]).Visible = True
    Me("OptionLabel" & rs![ItemNumber]).Visible = True
    Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
    rs.MoveNext
    Wend
    End If

    ' Close the recordset and the database.
    rs.close
    Set rs = Nothing

    I am getting an error when it runs, too few parameters. expected 2.
    Does anyone know how i can resolve this, i have tried to put the parameters in at the top of the SQL and it gives me a parameter error then?

  2. #2
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    I put your code in the switch board on open & got same error msg but it expected 3. I compared your statements to similar in my db. It seems I use "Set db = CurrentDb" where you say "Dim dbs As DAO.Database" as well as "Set dbs = CurrentDb". I'm not sure of the dim statement. Try commenting it out.

  3. #3
    Join Date
    Mar 2004
    Location
    Slovenia
    Posts
    56

    sql

    stSql = stSql & "SELECT DISTINCT [Switchboard Items].[Security Number], [Switchboard Items].ItemNumber, [Switchboard Items].SwitchboardID, *"
    stSql = stSql & "FROM [Switchboard Items]"
    stSql = stSql & "WHERE ((([Switchboard Items].[Security Number]) > [Forms]![Password Login]![User Level]) And (([Switchboard Items].ItemNumber) > 0) And (([Switchboard Items].SwitchboardID) = [Me]![SwitchboardID]))"
    stSql = stSql & "ORDER BY [Switchboard Items].ItemNumber;"

    1)
    [Forms]![Password Login]![User Level] and [Me]![SwitchboardID] are the missing parameters!
    2) try this --- not tested

    stSql = stSql & "WHERE (([Switchboard Items].[Security Number]) >" &
    [Forms]![Password Login]![User Level]) & ")"
    stSql = stSql & " And (([Switchboard Items].ItemNumber) > 0) And (([Switchboard Items].SwitchboardID) = " & [Me]![SwitchboardID] & "))"

    stSql = stSql & " And (([Switchboard Items].ItemNumber) > 0) And (([Switchboard Items].SwitchboardID) = [Me]![SwitchboardID]))"
    stSql = stSql & "ORDER BY [Switchboard Items].ItemNumber;"
    Back to the basics...

  4. #4
    Join Date
    Mar 2004
    Location
    Slovenia
    Posts
    56

    " ORDER BY"

    and another space in front of ORDER BY

    instead "ORDER BY" do " ORDER BY"

    ...
    Back to the basics...

  5. #5
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    Getting expected end of statement in a few places and try and run the switchboard and get an error stating that access cannot find the field referred to in my expression.

    Tried replacing a few of the " with ' and that solved some of the problem, here is the revised code

    stSql = stSql & "SELECT DISTINCT [Switchboard Items].[Security Number], [Switchboard Items].ItemNumber, [Switchboard Items].SwitchboardID, *"
    stSql = stSql & "FROM [Switchboard Items]"
    stSql = stSql & "WHERE (([Switchboard Items].[Security Number]) >' & [Forms]![Password Login]![User Level]) & ')"
    stSql = stSql & "And (([Switchboard Items].ItemNumber) > 0) And (([Switchboard Items].SwitchboardID) = ' & [Me]![SwitchboardID] & '))"
    stSql = stSql & " ORDER BY [Switchboard Items].ItemNumber;"

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    SQL ignores excess spaces, but hates missing spaces. spaces come free with the keyboard - use more.

    your current SQL reads (in part):

    ].SwitchboardID, *FROM 'wont work
    Switchboard Items]WHERE 'wont work
    Level]) & ')And 'might work, but add a space anyway...
    ]![SwitchboardID] & ')) ORDER '...just like you did here


    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    oh yes, you don't mention what is in strSQL when you start, but i guess
    stSql = "SELECT DISTINCT [Switchboard...
    will be safer in case you have some leftovers in strSQL from earlier

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and another thought:

    SELECT DISTINCT this, that, * FROM .....
    might disappoint you.

    why * ??

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    Created the code in an access query then copied it from the SQL view to the VBA code?

  10. #10
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    Right getting a different error now, ive jigged the code around a bit and now i am getting mismatch criteria, which is the parameters being set up wrong if im correct? The parameters here have been setup to format them for text i think? What are the ones for numbers as the fields are numbers which the query is working with?

  11. #11
    Join Date
    Mar 2004
    Location
    Slovenia
    Posts
    56

    variables or parameters

    text:
    enclose with '
    variable = "'" & me.firstname & "'"
    numbers: you don't have to enclose
    variable = me.OfferID
    date
    enclose with #
    variable = "#" & me.date1 & "#"
    Back to the basics...

  12. #12
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    So what should i do then to get this SQL query to work?? When i dont put the ' around them it is saying not enough parameters and when i do i get a data type mismatch? Help???

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    example

    WRONG:
    "WHERE (([Switchboard Items].[Security Number]) >' & [Forms]![Password Login]![User Level]) & ')"

    if [Security Number] is a number use:
    "WHERE (([Switchboard Items].[Security Number]) >" & [Forms]![Password Login]![User Level]) & ")"

    if text, use:
    "WHERE (([Switchboard Items].[Security Text]) >'" & [Forms]![Password Login]![SomeText]) & "')"

    if it was date, use:
    "WHERE (([Switchboard Items].[Security Date]) >#" & [Forms]![Password Login]![SomeDate]) & "#)"
    currently using SS 2008R2

  14. #14
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    Right, Brilliant, nearly there!
    Now there is an error saying can't find the field "forms" referred to in your expression!

    SQL is now
    stSql = stSql & "SELECT DISTINCT [Switchboard Items].[Security Number], [Switchboard Items].ItemNumber, [Switchboard Items].SwitchboardID, *"
    stSql = stSql & "FROM [Switchboard Items]"
    stSql = stSql & "WHERE (([Switchboard Items].[Security Number]) > " & [Forms]![Password Login]![User Level] & ")"
    stSql = stSql & " And ((([Switchboard Items].ItemNumber) > 0) And ([Switchboard Items].SwitchboardID) = " & [Me]![SwitchboardID] & ")"
    stSql = stSql & " ORDER BY [Switchboard Items].ItemNumber;"

    As far as i can see i havn't got any of the form names wrong or referred to any tables wrong, soooo, has anyone any idea on this one?

    I know i am a pain but this is the only thing that wont bloody work in my program now!

    Thanks

  15. #15
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    Not a problem, sorted it myself

Posting Permissions

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