dlookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here's the scenerio -

Example for Alaska -

If the f/c institute date is before or equal to 08/31/2005, it needs to be 7
If the f/c institute date is equal to or greater than 09/01/2005, it needs
to be 5

In the control I have:
=IIf(([f/c institute]=<8/31/2005),(DLookUp("[Timeframe]","[dd
timeframe]","State = [combo289]")),IIf(([f/c
institute]=>9/01/2005),(DLookUp("[91Timeframe]","[dd timeframe]","State =
[combo289]"))))

it is only pulling "5" from the table.

Can you see what I am doing wrong?
 
Here's the scenerio -

Example for Alaska -

If the f/c institute date is before or equal to 08/31/2005, it needs to be 7
If the f/c institute date is equal to or greater than 09/01/2005, it needs
to be 5

In the control I have:
=IIf(([f/c institute]=<8/31/2005),(DLookUp("[Timeframe]","[dd
timeframe]","State = [combo289]")),IIf(([f/c
institute]=>9/01/2005),(DLookUp("[91Timeframe]","[dd timeframe]","State =
[combo289]"))))

it is only pulling "5" from the table.

Can you see what I am doing wrong?
Click to expand...

1) Dates must be wrapped in the date delimiter symbol #.

2) When using the greater than or less than symbols with =, the =
must go last, i.e. >= or <=.
3) An IIf statement has a True and a False Value. You didn't enter a
False value in your second IIf().

4) Where do you get the 5 or 7 from? From the DLookUP?

5) What is the Datatype of the bound column of [Combo289]?

If it is text datatype, then:
=IIf([f/c institute]<= #8/31/2005#, DLookUp("[Timeframe]","[dd
timeframe]","[State] = '" & [combo289] & "'"),IIf([f/c
institute]>= #9/01/2005#, DLookUp("[91Timeframe]","[dd
timeframe]","[State] ='" & [combo289] & "'")))

However, if the bound column of the combo box is a Number datatype,
then use:

=IIf([f/c institute]>= #8/31/2005#, DLookUp("[Timeframe]","[dd
timeframe]","[State] = " & [combo289]),IIf([f/c
institute]>= #9/01/2005#, DLookUp("[91Timeframe]","[dd
timeframe]","[State] = " & [combo289]),""))

Actually, you really only have 2 choices?
If the date is <= 8/31/2005 then DLookUp [TimeFrame] otherwise DLookUp
[91 timeframe]. So you can use ....
Assuming the bound column of the combo box is text:

=IIf([f/c institute] <= #8/31/2005#, DLookUp("[Timeframe]","[dd
timeframe]","[State] = '" & [combo289] & "'"),
DLookUp("[91Timeframe]","[dd timeframe]","[State] = '" & [combo289] &
"'"))
 
That worked great, thank you so much for your help

fredg said:
Here's the scenerio -

Example for Alaska -

If the f/c institute date is before or equal to 08/31/2005, it needs to be 7
If the f/c institute date is equal to or greater than 09/01/2005, it needs
to be 5

In the control I have:
=IIf(([f/c institute]=<8/31/2005),(DLookUp("[Timeframe]","[dd
timeframe]","State = [combo289]")),IIf(([f/c
institute]=>9/01/2005),(DLookUp("[91Timeframe]","[dd timeframe]","State =
[combo289]"))))

it is only pulling "5" from the table.

Can you see what I am doing wrong?
Click to expand...

1) Dates must be wrapped in the date delimiter symbol #.

2) When using the greater than or less than symbols with =, the =
must go last, i.e. >= or <=.
3) An IIf statement has a True and a False Value. You didn't enter a
False value in your second IIf().

4) Where do you get the 5 or 7 from? From the DLookUP?

5) What is the Datatype of the bound column of [Combo289]?

If it is text datatype, then:
=IIf([f/c institute]<= #8/31/2005#, DLookUp("[Timeframe]","[dd
timeframe]","[State] = '" & [combo289] & "'"),IIf([f/c
institute]>= #9/01/2005#, DLookUp("[91Timeframe]","[dd
timeframe]","[State] ='" & [combo289] & "'")))

However, if the bound column of the combo box is a Number datatype,
then use:

=IIf([f/c institute]>= #8/31/2005#, DLookUp("[Timeframe]","[dd
timeframe]","[State] = " & [combo289]),IIf([f/c
institute]>= #9/01/2005#, DLookUp("[91Timeframe]","[dd
timeframe]","[State] = " & [combo289]),""))

Actually, you really only have 2 choices?
If the date is <= 8/31/2005 then DLookUp [TimeFrame] otherwise DLookUp
[91 timeframe]. So you can use ....
Assuming the bound column of the combo box is text:

=IIf([f/c institute] <= #8/31/2005#, DLookUp("[Timeframe]","[dd
timeframe]","[State] = '" & [combo289] & "'"),
DLookUp("[91Timeframe]","[dd timeframe]","[State] = '" & [combo289] &
"'"))
Click to expand...
 
Back
Top