Variables And Dlookup

[ineedahelp]

Can someone help me with a variable usage? I keep getting an error message in my DLOOKUP line. It doesn't like me using qryDummy. Maybe I haven't assigned it properly. Can I use a variable with dLOOKUP? when I use an actual table everything works fine! thanks for any help!

[NeoPa]

If you post your code then we can see if we notice anything that needs to be changed.

[MMcCarthy]

Can someone help me with a variable usage? I keep getting an error message in my DLOOKUP line. It doesn't like me using qryDummy. Maybe I haven't assigned it properly. Can I use a variable with dLOOKUP? when I use an actual table everything works fine! thanks for any help!

If I remember from previous posts qryDummy is a query definition. You cannot use this in a DLookup.

[ineedahelp]

I am sorry I didn't post the code. The code below actually works. My problem now is that with this code I am trying to lookup values using a variable "symbol". Both qryDummy and rst have 5000- 6000 records. When I run the code below, it only processes 1000 records in 5 minutes. I feel that either the DLOOKUP function is too slow (which doesn't really make sense) or that there is a problem in the code. Whenever I run and CTRL BREAK out, I notice that the line **If IsNull(varRate) Then** is always yellowed out in my debug window. It IS processing records though....any help?


Private Sub cmbCaptureLSData_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varRate As Variant
Dim strSymbol As String
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim mDate As String
Dim mNewDate As Date
mDate = Left(MyValue, 2) & "/" & Mid(MyValue, 3, 2) & "/" & Right(MyValue, 2)
Debug.Print mDate
mNewDate = CDate(mDate)
strSQL = "SELECT DailyPrice.LocateDate, DailyPrice.Symbol, DailyPrice.MarketPrice " & _
"FROM DailyPrice WHERE (((DailyPrice.LocateDate) = #" & mNewDate & "#)) " & _
"ORDER BY DailyPrice.Symbol;"
Debug.Print strSQL

Set db = CurrentDb()
Set rst = db.OpenRecordset(tblName)
Set qdf = CurrentDb.QueryDefs("qryDummy")
qdf.SQL = strSQL

If rst.BOF And rst.EOF Then
MsgBox "No records to process"
Else

rst.MoveFirst
Do Until rst.EOF
strSymbol = rst!Symbol '** If symbol is field in recordset but use ! **
varRate = DLookup("[MarketPrice]", "qryDummy", "[symbol] = '" & strSymbol & "'")

If IsNull(varRate) Then
rst.Edit
rst!LSRate = 0
rst.Update
Else
rst.Edit
rst!LSRate = varRate
rst.Update
End If

rst.MoveNext
Loop

End If

rst.Close
qdf.Close
Set rst = Nothing
Set db = Nothing
Set qdf = Nothing

End Sub

[ineedahelp]

I wanted to also add that I have just run this code on a small (50 record) subset and the program ran fine and it took 5 seconds to complete. Am I expecting too much from Access to run 5000 to 6000 records in a shorter time?

[ineedahelp]

I am sorry to keep adding to my post, but I wanted to mention that I ran a make table off my qryDummy and used the TABLE instead of the QUERY and it ran in 20 seconds. I will of course change my code and MAKE a TABLE now, but I am still curious why the code ran with the query qryDummy but SO MUCH SLOWER! Any thoughts???

[NeoPa]

If I remember from previous posts qryDummy is a query definition. You cannot use this in a DLookup.

Although it is true to say that SQL query strings are not supported in the Domain Aggregate functions, defined queries are supported.

domain A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name.

[MMcCarthy]

OK this makes more sense. I misunderstood your original question. Your problem here is that you are trying to do a Dlookup on a queryDef while its still open. Close it first and then run the Dlookup as follows:

[code]

Private Sub cmbCaptureLSData_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varRate As Variant
Dim strSymbol As String
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim mDate As String
Dim mNewDate As Date
mDate = Left(MyValue, 2) & "/" & Mid(MyValue, 3, 2) & "/" & Right(MyValue, 2)
Debug.Print mDate
mNewDate = CDate(mDate)
strSQL = "SELECT DailyPrice.LocateDate, DailyPrice.Symbol, DailyPrice.MarketPrice " & _
"FROM DailyPrice WHERE (((DailyPrice.LocateDate) = #" & mNewDate & "#)) " & _
"ORDER BY DailyPrice.Symbol;"
Debug.Print strSQL

Set db = CurrentDb()
Set rst = db.OpenRecordset(tblName)
Set qdf = CurrentDb.QueryDefs("qryDummy")
qdf.SQL = strSQL

qdf.Close
Set qdf = Nothing

If rst.BOF And rst.EOF Then
MsgBox "No records to process"
Else

rst.MoveFirst
Do Until rst.EOF
strSymbol = rst!Symbol '** If symbol is field in recordset but use ! **
varRate = DLookup("[MarketPrice]", "qryDummy", "[symbol] = '" & strSymbol & "'")

If IsNull(varRate) Then
rst.Edit
rst!LSRate = 0
rst.Update
Else
rst.Edit
rst!LSRate = varRate
rst.Update
End If

rst.MoveNext
Loop

End If

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

[/quote]