0

I'm trying to make a MS Access report, where I use a text box to display a field value, then have another text box indicating if the first value is higher or lower than an entry in a separate table.

The report has a record source of "Table 1", and a textbox named "txt_Value1" which displays the number in Field: "Value1". I have a second table, "Customer_Criteria" which has a field "PassValue" that I want to compare against. My expression builder statement is: 

IIf([txt_Value1]<(DLookUp("[PassValue]","[Customer_Criteria]","[Customer] = 'ABC'")),"TRUE","FALSE")

This statement always returns false, regardless of what the correct logical result is.

I've tested it, writing:

IIf(1<(DLookUp("[PassValue]","[Customer_Criteria]","[Customer] = 'ABC'")),"TRUE","FALSE")

And I get the correct results. Also, if I write:

IIf([txt_Value1]< 1,"TRUE","FALSE")

I get the correct results. What am I missing to compare the textbox value vs. the Dlookup?

user2059972
  • 145
  • 2
  • 11

1 Answers1

1

As I understand, both fields are numeric. Access may consider those fields as text, so for correct comparing use type conversion. Try this:

IIf(CLng(Nz([txt_Value1],0))< _
    CLng(Nz(DLookUp("[PassValue]","[Customer_Criteria]","[Customer] = 'ABC'"),0)), _
    "TRUE","FALSE")

Nz required if fields may contain NULL values, in this case type conversion function will return error.

Sergey S.
  • 6,296
  • 1
  • 14
  • 29
  • `PassValue` is probably already an int. In that case the second `CLng` is not needed. Also depending on what other validation exists in the system to prevent `txt_Value1` from being null and/or the DLookup from returning null (either a null PassValue in a record or no record matching the criteria) you may also not need the `Nz`. Finally, you should make sure whatever is typed in `txt_Value1` is numeric. – SunKnight0 Jan 13 '17 at 14:18