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 you post your code then we can see if we notice anything that needs to be changed. -
If I remember from previous posts qryDummy is a query definition. You cannot use this in a DLookup.Originally posted by ineedahelpComment
-
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 cmbCaptureLSDat a_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.Loca teDate, DailyPrice.Symb ol, DailyPrice.Mark etPrice " & _
"FROM DailyPrice WHERE (((DailyPrice.L ocateDate) = #" & mNewDate & "#)) " & _
"ORDER BY DailyPrice.Symb ol;"
Debug.Print strSQL
Set db = CurrentDb()
Set rst = db.OpenRecordse t(tblName)
Set qdf = CurrentDb.Query Defs("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 SubComment
-
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?Comment
-
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???Comment
-
Although it is true to say that SQL query strings are not supported in the Domain Aggregate functions, defined queries are supported.Originally posted by mmccarthy
Originally posted by Access HelpComment
-
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 cmbCaptureLSDat a_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.Loca teDate, DailyPrice.Symb ol, DailyPrice.Mark etPrice " & _
"FROM DailyPrice WHERE (((DailyPrice.L ocateDate) = #" & mNewDate & "#)) " & _
"ORDER BY DailyPrice.Symb ol;"
Debug.Print strSQL
Set db = CurrentDb()
Set rst = db.OpenRecordse t(tblName)
Set qdf = CurrentDb.Query Defs("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]Comment
Comment