CODEWITHSUNDEEP
ms-accessvbams-access-2013
vxd128
vxd128

Reputation: 71

Comparing value in Access report field to a query using VBA

I currently have a report that lists steps for various tasks and tracks the revision count. Each page is one task, with all of the steps under it. At the end of the report is a Revision history where it will list what was changed to update the revision count. I have two queries for the report, one to generate the report data and the other to generate the revision history.

What I need to do, is to show which tasks have changes on them. I want to do that by comparing the Task_ID on each report page to the Task_ID found in the revision history query.

I've tried a few varitions of dlookups and dcounts without any luck. Whenever I monitor the code, it keeps treating it as false and will end it. 

If DLookup("[Task_ID]", "[qry_revision_history_conversions]") = [Reports]![rpt_WI_Book].[Report]![Task] Then
[Reports]![rpt_WI_Book].[Report]![Rev_Change].Visible = False

It's probably something really simple I'm missing, but I can't seem to wrap my head around it.

Here's the code I used to make it work. 

Dim LookupTask As Variant
Dim lngRed As Long, lngYellow As Long, lngWhite As Long
lngRed = RGB(255, 0, 0)
 lngBlack = RGB(0, 0, 0)
 lngYellow = RGB(255, 255, 0)
 lngWhite = RGB(255, 255, 255)

LookupTask = DLookup("[Task_ID]", "[qry_task_check]", [Reports]![rpt_WI_Book].[Report]![Text474])

If LookupTask = [Reports]![rpt_WI_Book].[Report]![Text474] Then
    [Reports]![rpt_WI_Book].[Report]![Text474].BackColor = lngYellow

Else
[Reports]![rpt_WI_Book].[Report]![Text474].BackColor = lngWhite
End If

Upvotes: 0

Views: 528

Answers (1)

dbmitch
dbmitch

Reputation: 5386

Not sure exactly what fields are in your table, but maybe I can assume you're trying to match TaskID to the Report Task field?

From the DLookup docs

if you don't supply a value for criteria, the DLookup function returns a random value

Use your criteria inside DLookup and then check for Null:

Dim LookupTask as Variant

LookupTask = DLookup("[Task_ID]", "[qry_revision_history_conversions]", "[Task_ID] = " & [Reports]![rpt_WI_Book]![Task])

If Not IsNull(LookupTask) Then
    [Reports]![rpt_WI_Book]![Rev_Change].Visible = False
End If

Upvotes: 1

Related Questions