Results 1 to 4 of 4

Thread: Access Query
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2006
    Location
    Leeds, UK
    Posts
    1,385
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Access Query

    I am trying to return a single record from an Access table but I am struggling to return the specific record

    I have been using
    Code:
    strQuery = "SELECT Agent FROM SageData WHERE InvDate = #" & Format(dtDate, "dd/mm/yyyy") & "# AND BoxRef = '" & rngCell.Offset(intCount, 0) & "'"
    rsQuery.Open "SageData", cnConnection, adOpenKeyset, adLockOptimistic
    but this returns all the records in the table.

    When I have reduced the query down to
    Code:
    strQuery = "SELECT Agent FROM SageData WHERE Supplier = 'CNG'"
    it is still returning all the records despite there only being a few in the table.

    I can't work out what isn't right as I use this command quite a lot.


    Thanks

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Access Query

    You are building the query string (strQuery), but I do not see you using it in the open query command.
    I think you actually need to assign that SQL code to your rsQuery object. Otherwise, the query string you just built is in no way related to rsQuery.

    Assuming rsQuery is defined as a query object, maybe something like this:
    Code:
    strQuery = "SELECT Agent FROM SageData WHERE InvDate = #" & Format(dtDate, "dd/mm/yyyy") & "# AND BoxRef = '" & rngCell.Offset(intCount, 0) & "'"
    rsQuery.SQL = strQuery
    rsQuery.Open "SageData", cnConnection, adOpenKeyset, adLockOptimistic
    Last edited by Joe4; Jun 21st, 2019 at 10:15 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Jul 2010
    Posts
    401
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Query

    Looks like you are building a query string then just ignoring it and asking for the table to be returned:

    Code:
    rsQuery.Open "SageData", cnConnection, adOpenKeyset, adLockOptimistic
    try:
    Code:
    rsQuery.Open strQuery, cnConnection, adOpenKeyset, adLockOptimistic
    Last edited by stumac; Jun 21st, 2019 at 10:13 AM.

  4. #4
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,109
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Query

    SELECT Agent FROM SageData WHERE Supplier = 'CNG'
    should give you the list of Agent that have CNG as supplier. You might have duplicate agents because every time it reads CNG in supplier, it gives the 'agent' value...not all the records but all the records with CNG in supplier column.
    Code:
    SELECT Agent FROM SageData WHERE Supplier = 'CNG'
    GROUP BY Agent
    should give you the list of unique agents that have CNG in the column Supplier (reduced list).

    If it is not the case, then I do not get it
    Last edited by Kamolga; Jun 21st, 2019 at 10:17 AM.

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
  •