Dlookup Vs Record set query (1 Viewer)

[nector]

What is the problem with the record set query below , instead of returning a unique record its return one record in all the product lines, but If i use Dlookup its return the correct unique record see below:

Option Compare Database
Option Explicit
Private Sub Report_Load()
Dim db As DAO.Database
Dim strSQL As String
Dim prm As DAO.Parameter
Set db = CurrentDb
strSQL = "SELECT Quanties FROM [ViewSalesUsedForClosingValuation] WHERE [ProductID] =" & Me.ProductID
With db.CreateQueryDef("", strSQL)
For Each prm In .Parameters
prm.Value = Eval(prm.Name)
Next
    With .OpenRecordset(dbOpenSnapshot, dbSeeChanges)
        If Not .EOF() Then
         Me.txtSales = Nz(.Fields(0).Value, 0)
    End If
    End With
End With
Set prm = Nothing
Set db = Nothing
End Sub




=Nz(DLookUp("Quanties","[ViewSalesUsedForClosingValuation]","[ProductID] =" & [ProductID]),0)

 

[gemma-the-husky]

The select query will return all (multiple if there is more than one match) matches. The dlookup will return a single (random) selection from the available matches.

Not random as far as the database engine is concerned - presumably the first in whatever way it does the lookup - but random as far as we are concerned.

I am not sure about your code after you have assigned the strSQL string.