Access Query

[mikeymay]

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

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

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

 

[Joe4]

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:

strQuery = "SELECT Agent FROM SageData WHERE InvDate = #" & Format(dtDate, "dd/mm/yyyy") & "# AND BoxRef = '" & rngCell.Offset(intCount, 0) & "'"
[COLOR=#ff0000]rsQuery.SQL = strQuery[/COLOR]
rsQuery.Open "SageData", cnConnection, adOpenKeyset, adLockOptimistic

 

[stumac]

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

[COLOR=#333333]rsQuery.Open "[/COLOR][COLOR=#FF0000]SageData[/COLOR][COLOR=#333333]", cnConnection, adOpenKeyset, adLockOptimistic[/COLOR]

try:

[COLOR=#333333]rsQuery.Open [/COLOR][COLOR=#333333]strQuery[/COLOR][COLOR=#333333], cnConnection, adOpenKeyset, adLockOptimistic[/COLOR]

 

[Kamolga]

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.

[LEFT][COLOR=#333333][FONT=monospace]SELECT Agent 
FROM SageData 
WHERE Supplier = 'CNG'
[/FONT][/COLOR][/LEFT]GROUP BY [COLOR=#222222][FONT=Verdana]Agent[/FONT][/COLOR]

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