Dlookup in a report

[DataFanatic]

I have a report based on a query. It is grouped on a equipment type. I am trying to use dlookup to place the description in the group header. I can't seem to figure out the third parameter for dlookup. Any thoughts as to how to refer to the value from the report. I have tried qry_ByCodes.EQUIP and get a prompt for a value.

Thanks in advance for any help.

 

[THWatson]

DataFanatic
It's not clear to me why you need to use DLookup to pull in the description.

Is the EQUIP field the description? If so, then as long as that is in your query, you should be able to place a control to get the data.

But, if the description is in a different query, or table, than the one which populates the report, the correct syntax for a DLookup expression is...
DLookup("[field]","table")

Or perhaps in your case...
DLookup("[EQUIP]","qry_ByCodes")

It is usually better not to use DLookup unless you need to...but if you need to, it's a handy tool.

Tom

 

[mikethorpe]

I have used dlookup for fields in a report and the code I have used which is put in the Data tab Control Source is

=DLookUp("Description","tblProduct","ProductId = " & [ProductId])

This returns the Description field of the product from the table tblProduct. ProductId is a field in the data source table of the report.

Hope this solves your problem.

 

[DataFanatic]

Tom, Thanks for the reply. The EQUIP code is the identifier and there is a field name Description. My dlookup is as follows: Dlookup("[Description]","tbl_Codes","[EQUIP] = '"& qry_ByCodes.Equip & "'") This produces as pop up request te enter a value for qry_Bycodes. I constructed a second statement: Dlookup("[Description]","tbl_Codes","[EQUIP] = '"& [EQUIP] & "'") as the EQUIP field is from the available fields in the report. It produces an incorrect find.

Thanks again for the look.

 

[THWatson]

DataFanatic
Because of the underscore (_) character in the table and query names, you might try enclosing in [], as in...
"[tbl_Codes]"

But I'm still not clear. You are grouping by equipment type. If all the equipment in that group have the same Description, and the Description field is in the query that populates the report, can't you just pull in the Description into the Group Header?
Or maybe I am missing something.

Tom

 

[DataFanatic]

Tom
I'm sorry I did not fully explain. The query is being constructed by multiple selections of EQUIP from a single table. Code is:
-------------------------------------------
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qry_Bycodes")
' Loop through the selected items in the list box and build a text string
If Me!List0.ItemsSelected.Count > 0 Then
For Each varItem In Me!List0.ItemsSelected
strCriteria = strCriteria & "(DDDATA.Equip) = " & Chr(34) _
& Me!List0.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "DDDATA.Equip Like '*'"
End If
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM DDDATA " & _
"WHERE " & "(" & strCriteria
strSQL = strSQL & ") and ((DDDATA.DDate) >= #" & Format(Me.Sdate, "mm/dd/yy") & "#);"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "qry_Bycodes"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
----------------------------------------------
If I was really good I guess I could create the sql string to join the table holding the description. I was just focusing on using the DLOOKUP and nothing else.

 

[THWatson]

Well, I do notice one thing...

Your query seems to be named "qry_Bycodes"

One of your DLookup expressions used "qryByCodes" (note the capital C in Codes)

Tom

 

[DataFanatic]

Tom, Again thanks for the time. I believe I will revert back to your first question and create the sql statement to include the other table necesary for the description to be included.

I was being lazy.