Dlookup

[floydforbes]

Hello,

I'm trying to use Dlookup to find MICA_Grant Amount if Rank equal a number and Rank_code equal a code and the
range between FM1 and FM2. Is this possible to do?

 

[Minty]

Yes - what have you tried that didn't work - check out the links in my signature?

 

[floydforbes]

I have never written anything this complex. I do understand a simple Dlookup but don't
know how to use between function.

 

[Minty]

Break it down into stages. The following does the range bit

Dlookup("MICA_Grant","YourTableOrQuery","FM1 >= MyRangeLowerValue AND FM2 <= MyRangeHigherValue")

If you are doing this in VBA create the whole criteria in a string :

Dim strWhere as String

  strWhere = "([FM1] >= MyRangeLowerValue AND [FM2] <= MyRangeHigherValue) AND [Rank_code] ='123abc'  AND [Rank] = 10"
  Debug.print strWhere 
  
  Dlookup("MICA_Grant","YourTableOrQuery", strWhere)

If you want to pull data from a form you use the same technique, and build the where clause concatenating the form variables into the string.

 

[plog]

You posted in the query section of this forum: Dlookups do not belong in queries. If you are trying to get the MICA_Grant value into your query you should use a JOIN, not a Dlookup. If this value is to go elsewhere (Report or Form) then Dlookup is appropriate.

Minty's method in his strWhere is still the correct method, you just need to tweak it a little and then use it in SQL itself, not in a Dlookup. If you can post your existing SQL we can help insert the correct JOIN to get MICA_Grant in there.

 

[floydforbes]

Minty,

How would I create stages for this? I do understand a little Dlookup but not SQL.