Results 1 to 5 of 5

Thread: Access Query

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

    Default Access Query

    I am wanting to return all records in an Access table that meet a query criteria.

    The query I have built via VBA code is -
    SELECT ID, Price, QtyOut, ValueOut, InStock, ValueInStock, Allocated FROM GoodsIn WHERE Item = '9oz Squat Plastic Cup' AND DateIn <# 22/08/2019# AND Allocated = False ORDER BY DateIn ASC, QtyIn ASC

    There are 2 records in the table that meet the criteria but the query is only returning 1 record.

    I am using the following to open and read the recordset
    Code:
    rsQuery.Open strQuery, cnConnection, adOpenKeyset, adLockOptimistic
    I have used this code before to return all matching records so I am at a bit of a loss as to why this is n't working.

    Any insights provided would be hugely appreciated.

    TIA

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

    Default Re: Access Query

    The query looks okay except I'm not used to seeing boolean False as a value in Access query. Access uses numeric values to store true and false (-1 and 0 respectively). Otherwise, you'd probably have to post your code and some sample data in order to debug and test this.

    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. #3
    Board Regular JonXL's Avatar
    Join Date
    Feb 2018
    Posts
    214
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Query

    Can you post the two records you are expecting it to return?

  4. #4
    Board Regular
    Join Date
    May 2013
    Posts
    651
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access Query

    Any errant spaces in Item ?
    Office 2007
    Access novice. Sometimes trying to give something back

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

    Default Re: Access Query

    Thanks for the input all. I managed to get this to work without fixing anything so not entirely sure why it didn't work initially.

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
  •