Access Query

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,406
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
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,584
Office Version
2013
Platform
Windows
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.
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
280
Office Version
365, 2016
Platform
Windows
Can you post the two records you are expecting it to return?
 

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,406
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.
 

Forum statistics

Threads
1,085,703
Messages
5,385,309
Members
401,938
Latest member
JustinTaster

Some videos you may like

This Week's Hot Topics

Top