Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Location
    Rio Bravo, Mexico
    Posts
    27

    Question Unanswered: Help, Help, Help

    Hi, need an SQL statement in VB that will find a recordset in a table that has
    3 keys. Year, Month, Week

    I can do the the code with only one key, but having problems concatinating the 3 strings my criteria will be based on to find a particular record.

    In other words I need to find within the payroll table if the Year, Month and week exist. Year=2004, Month=9, Week=1

    Thanks for your help
    Carlos

  2. #2
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404
    I'm guessing that:

    SELECT * FROM payroll
    WHERE YEAR =2004
    AND month=9
    AND Week=1

    is not what you're looking for. Could you tell us more about the table structure (column names and types that sort of thing), and how you're getting the information to search by (in a form, combo boxes, text boxes etc)?


  3. #3
    Join Date
    Sep 2004
    Location
    Rio Bravo, Mexico
    Posts
    27

    Question

    Ryker, thanks for your response. Basically what Im trying to do (not very knowledgeable with access) is to find a record within the payroll table. This is the code that i have, my only problem is the concatination of the 3 strings my criteria will be based upon.

    Dim rst As New ADODB.Recordset, strQSt as string
    strQSt = "SELECT [Year],[Month], [Week] FROM tblPayroll WHERE [Year] = " & Me!txtYear & "[Month]=" & Me!txtMonth & "[Week] =" & Me!txtweek & ""

    rst.Open strQSt, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    If Not rst.EOF Then
    MsgBox ("Record exists"), vbInformation
    rst.Close
    Set rst = Nothing
    Exit Sub
    My problem is with the using of quotes to concatinate, having a hard time trying to figure out how the syntax works when using more the one string
    I works just fine if i use only the [Year] but when adding the rest, it crashes.Its the quote things im sure

    Thanks for your help

  4. #4
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404
    It's definitely going to crash. You must have the word AND when using more than one criteria:

    This

    strQSt = "SELECT [Year],[Month], [Week] FROM tblPayroll WHERE [Year] = " & Me!txtYear & " AND [Month]=" & Me!txtMonth & "AND [Week] =" & Me!txtweek & ""

    is a bit closer to what you want, but I'm still not sure it'll work. Try stepping throught he code. Use the debug window to get the value of strQST and copy it, then poste it into a query (just open a new query in SQL view), run it, and see if it gives you any errors.

    I'm not usre what the last & "" is for?


  5. #5
    Join Date
    Sep 2004
    Location
    Rio Bravo, Mexico
    Posts
    27

    Thumbs up

    Ryker it worked, it really did thanks a lot m8, your're a life saver.

    Many many thanks
    Carlos

Posting Permissions

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