Dlookup #Error looking up text based on numerical value

[Udaman]

I have tried many variations of this code but so far I only get the same #Error message. Here are the details:

On form "WOFilterandFluidsForm" I inserted a text box and named it "txtOilFilterType".

I am trying to find the value of field [OilFilterType] (text format) from table [AssetFFT] based on the value of field [AssetNo] (Number format) from the same table. So on my form when I choose a value from the drop down list of the bound control [AssetNo], I then was hoping it would find the value of the OilFilterType field in my text box.

My code that is in the Control Source of the text box currently looks like this:

=DLookUp("[OilFilterType]","[AssetFFT]","[AssetNo]=" & [AssetNo])

But I only get an #Error message in the text box. I don't know if it's because I am trying to obtain a text value based on a numerical value or what.

I have also tried this code:

=DLookUp("[OilFilterType]","[AssetFFT]","[AssetNo]=" & Forms!WOFilterandFluidsForm![AssetNo])

Again, same error message. Can anyone shed some light on this for me please?

 

[sheetspread]

=DLookUp("[OilFilterType]","[AssetFFT]","[AssetNo]='" & [AssetNo] & "'")


work?

 

[Udaman]

It does! So the single quotes forces it to look for a text value, is that correct? Just trying to make sure I understand the fix.

 

[sheetspread]

yes

 

[Udaman]

Sweet! Thanks so much for the quick reply!

 

[Udaman]

Just when you thought you got rid of me...

I have a very similar situation where I am looking up a different field value from a different table, but my criteria is the same, except this time I am getting a numerical value result. The numerical value is correct though, I just need it to display in text format. Here is my code:

=DLookUp("[AssetGroup]","[Assets]","[AssetNo]='" & [AssetNo] & "'")

I tried getting rid of the single quotes, but then I get an #Error again. What am I missing?

 

[sheetspread]

Try either the format() function or concatenating with &"" to convert number to string.

 

[Udaman]

I have been looking up online ways to convert numbers to text using the two options you mentioned, but I am not finding anything related to the specific method I am using. Most are talking about changing formats in my tables or queries, and I would prefer not to change any of those items for something I am trying to show in an unbound text box. Could you by chance give me an example? In case it wasn't obvious, I am still noobalicious...

 

[sheetspread]

You can also use the Str function to return text from a number or the Val function to return a number from text. Both can be used in form or report expressions.