iff statement

[Chey]

I have a table with the following informtion
Headers
FY, CC, LC, Start Date, End Date,
each year this will be poplulated
I need a code that will assign the proper information with the proper date.
Example these are Fiscal numbers

If date leave is between start date, and start end then that row of
information. However How do I get it to call from the right row.

Right now my table looks like this
FY CC LC Start Date End Date
06 123 456 7/1/2005 6/30/2006
07 789 321 7/1/2006 6/30/2007

so if there trip was in 7/25/05 then it shows 06 123 456
I hope this makes sense.
Thanks
Chey

 

[schasteen]

Try
=DLookUp("FY","YourTable","#" & [YourField] & "# Between [Start Date] and
[End Date]") & " " & DLookUp("CC","YourTable","#" & [YourField] & "# Between
[Start Date] and [End Date]") & " " & DLookUp("LC","YourTable","#" &
[YourField] & "# Between [Start Date] and [End Date]")

 

[Chey]

My Table is Named FY but I am not sure what [YourField] is.

Try
=DLookUp("FY","YourTable","#" & [YourField] & "# Between [Start Date] and
[End Date]") & " " & DLookUp("CC","YourTable","#" & [YourField] & "# Between
[Start Date] and [End Date]") & " " & DLookUp("LC","YourTable","#" &
[YourField] & "# Between [Start Date] and [End Date]")

I have a table with the following informtion
Headers
FY, CC, LC, Start Date, End Date,
each year this will be poplulated
I need a code that will assign the proper information with the proper date.
Example these are Fiscal numbers

If date leave is between start date, and start end then that row of
information. However How do I get it to call from the right row.

Right now my table looks like this
FY CC LC Start Date End Date
06 123 456 7/1/2005 6/30/2006
07 789 321 7/1/2006 6/30/2007

so if there trip was in 7/25/05 then it shows 06 123 456
I hope this makes sense.
Thanks
Chey

 

[schasteen]

The name of the field or control where the 7/25/05 date is.

My Table is Named FY but I am not sure what [YourField] is.

Try
=DLookUp("FY","YourTable","#" & [YourField] & "# Between [Start Date] and
[End Date]") & " " & DLookUp("CC","YourTable","#" & [YourField] & "# Between
[Start Date] and [End Date]") & " " & DLookUp("LC","YourTable","#" &
[YourField] & "# Between [Start Date] and [End Date]")

I have a table with the following informtion
Headers
FY, CC, LC, Start Date, End Date,
each year this will be poplulated
I need a code that will assign the proper information with the proper date.
Example these are Fiscal numbers

If date leave is between start date, and start end then that row of
information. However How do I get it to call from the right row.

Right now my table looks like this
FY CC LC Start Date End Date
06 123 456 7/1/2005 6/30/2006
07 789 321 7/1/2006 6/30/2007

so if there trip was in 7/25/05 then it shows 06 123 456
I hope this makes sense.
Thanks
Chey

 

[Chey]

this is what I put and now it gives me an error message.
=DLookUp("FY","FY","#" & [Date Leave] & "# Between [FY Start Date] and [End
Date]") & " " & DLookUp("CC","FY","#" & [Date Leave] & "# Between [FY Start
Date] and [FY End Date]") & " " & LookUp("LC","FY","#" & [Date Leave] & "#
Between [FY Start Date] and [FY End Date]")

Try
=DLookUp("FY","YourTable","#" & [YourField] & "# Between [Start Date] and
[End Date]") & " " & DLookUp("CC","YourTable","#" & [YourField] & "# Between
[Start Date] and [End Date]") & " " & DLookUp("LC","YourTable","#" &
[YourField] & "# Between [Start Date] and [End Date]")

I have a table with the following informtion
Headers
FY, CC, LC, Start Date, End Date,
each year this will be poplulated
I need a code that will assign the proper information with the proper date.
Example these are Fiscal numbers

If date leave is between start date, and start end then that row of
information. However How do I get it to call from the right row.

Right now my table looks like this
FY CC LC Start Date End Date
06 123 456 7/1/2005 6/30/2006
07 789 321 7/1/2006 6/30/2007

so if there trip was in 7/25/05 then it shows 06 123 456
I hope this makes sense.
Thanks
Chey

 

[schasteen]

Don't know if a typo from transfering, but the first end date does not have
FY End Date and the others do.

=DLookUp("FY","FY","#" & [Date Leave] & "# Between [FY Start Date] and [FY
End
Date]") & " " & DLookUp("CC","FY","#" & [Date Leave] & "# Between [FY Start
Date] and [FY End Date]") & " " & LookUp("LC","FY","#" & [Date Leave] & "#
Between [FY Start Date] and [FY End Date]")

this is what I put and now it gives me an error message.
=DLookUp("FY","FY","#" & [Date Leave] & "# Between [FY Start Date] and [End
Date]") & " " & DLookUp("CC","FY","#" & [Date Leave] & "# Between [FY Start
Date] and [FY End Date]") & " " & LookUp("LC","FY","#" & [Date Leave] & "#
Between [FY Start Date] and [FY End Date]")

Try
=DLookUp("FY","YourTable","#" & [YourField] & "# Between [Start Date] and
[End Date]") & " " & DLookUp("CC","YourTable","#" & [YourField] & "# Between
[Start Date] and [End Date]") & " " & DLookUp("LC","YourTable","#" &
[YourField] & "# Between [Start Date] and [End Date]")

I have a table with the following informtion
Headers
FY, CC, LC, Start Date, End Date,
each year this will be poplulated
I need a code that will assign the proper information with the proper date.
Example these are Fiscal numbers

If date leave is between start date, and start end then that row of
information. However How do I get it to call from the right row.

Right now my table looks like this
FY CC LC Start Date End Date
06 123 456 7/1/2005 6/30/2006
07 789 321 7/1/2006 6/30/2007

so if there trip was in 7/25/05 then it shows 06 123 456
I hope this makes sense.
Thanks
Chey

 

[Chey]

now it tells me that I have the wrong amount of arguments.

Don't know if a typo from transfering, but the first end date does not have
FY End Date and the others do.

=DLookUp("FY","FY","#" & [Date Leave] & "# Between [FY Start Date] and [FY
End
Date]") & " " & DLookUp("CC","FY","#" & [Date Leave] & "# Between [FY Start
Date] and [FY End Date]") & " " & LookUp("LC","FY","#" & [Date Leave] & "#
Between [FY Start Date] and [FY End Date]")

this is what I put and now it gives me an error message.
=DLookUp("FY","FY","#" & [Date Leave] & "# Between [FY Start Date] and [End
Date]") & " " & DLookUp("CC","FY","#" & [Date Leave] & "# Between [FY Start
Date] and [FY End Date]") & " " & LookUp("LC","FY","#" & [Date Leave] & "#
Between [FY Start Date] and [FY End Date]")

Try
=DLookUp("FY","YourTable","#" & [YourField] & "# Between [Start Date] and
[End Date]") & " " & DLookUp("CC","YourTable","#" & [YourField] & "# Between
[Start Date] and [End Date]") & " " & DLookUp("LC","YourTable","#" &
[YourField] & "# Between [Start Date] and [End Date]")

:

I have a table with the following informtion
Headers
FY, CC, LC, Start Date, End Date,
each year this will be poplulated
I need a code that will assign the proper information with the proper date.
Example these are Fiscal numbers

If date leave is between start date, and start end then that row of
information. However How do I get it to call from the right row.

Right now my table looks like this
FY CC LC Start Date End Date
06 123 456 7/1/2005 6/30/2006
07 789 321 7/1/2006 6/30/2007

so if there trip was in 7/25/05 then it shows 06 123 456
I hope this makes sense.
Thanks
Chey

 

[John Vinson]

now it tells me that I have the wrong amount of arguments.

There are some extra octothorpes in there and a lot of misplaced
quotes: you don't need # around the name of the field being searched,
just around the date literals. Try

=DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
& LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#")

For reasonable start and end dates, the criteria for the DLookups
(once they've been evaluated) might look like

[Date Leave] BETWEEN #7/1/2006# AND #7/31/2006#


John W. Vinson[MVP]

 

[Chey]

Hello,
It still tells me that I have the wrong number of aurguments.
I copied and pasted what you gave me.
=DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
& LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#")

I pasted it in an unbound field. Is this right?

now it tells me that I have the wrong amount of arguments.

There are some extra octothorpes in there and a lot of misplaced
quotes: you don't need # around the name of the field being searched,
just around the date literals. Try

=DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
& LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#")

For reasonable start and end dates, the criteria for the DLookups
(once they've been evaluated) might look like

[Date Leave] BETWEEN #7/1/2006# AND #7/31/2006#


John W. Vinson[MVP]

 

[Chey]

Now I am thinking of something else.
I have 3 columns. FY CC and LC
In the first field I need it to return FY If it is between FY Start Date and
FY End date.
Then the sencond field it needs ot rertun LC "
"
then the thrid field needs to return CC "
"
so I tried this
=DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#")
But if I am reading it right where does it tell it to show the proper FY it
could be 06 or 07 depending on the date. When I do it like this it returns
ERROR
Thanks

now it tells me that I have the wrong amount of arguments.

There are some extra octothorpes in there and a lot of misplaced
quotes: you don't need # around the name of the field being searched,
just around the date literals. Try

=DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
& LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#")

For reasonable start and end dates, the criteria for the DLookups
(once they've been evaluated) might look like

[Date Leave] BETWEEN #7/1/2006# AND #7/31/2006#


John W. Vinson[MVP]

 

[Pieter Wijnen]

such a complex statement is better of in VBA code, me thinks
(hopeless to debug) & reliant on US Date Format (to top it of)

Pieter

Hello,
It still tells me that I have the wrong number of aurguments.
I copied and pasted what you gave me.
=DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
& LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#")

I pasted it in an unbound field. Is this right?

now it tells me that I have the wrong amount of arguments.

There are some extra octothorpes in there and a lot of misplaced
quotes: you don't need # around the name of the field being searched,
just around the date literals. Try

=DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
& LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#")

For reasonable start and end dates, the criteria for the DLookups
(once they've been evaluated) might look like

[Date Leave] BETWEEN #7/1/2006# AND #7/31/2006#


John W. Vinson[MVP]

 

[Pieter Wijnen]

such a complex statement is better of in VBA code, me thinks
(hopeless to debug) & reliant on US Date Format (to top it of)

Pieter

Hello,
It still tells me that I have the wrong number of aurguments.
I copied and pasted what you gave me.
=DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
& LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#")

I pasted it in an unbound field. Is this right?

now it tells me that I have the wrong amount of arguments.

There are some extra octothorpes in there and a lot of misplaced
quotes: you don't need # around the name of the field being searched,
just around the date literals. Try

=DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
& LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#")

For reasonable start and end dates, the criteria for the DLookups
(once they've been evaluated) might look like

[Date Leave] BETWEEN #7/1/2006# AND #7/31/2006#


John W. Vinson[MVP]

--

 

[Chey]

would you be able to walk me through something so I can get this to visually
look right. I have something else that is similar that I could use it on.
Thanks

such a complex statement is better of in VBA code, me thinks
(hopeless to debug) & reliant on US Date Format (to top it of)

Pieter

Hello,
It still tells me that I have the wrong number of aurguments.
I copied and pasted what you gave me.
=DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
& LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#")

I pasted it in an unbound field. Is this right?

On Thu, 13 Jul 2006 14:07:02 -0700, Chey

now it tells me that I have the wrong amount of arguments.

There are some extra octothorpes in there and a lot of misplaced
quotes: you don't need # around the name of the field being searched,
just around the date literals. Try

=DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
& LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#")

For reasonable start and end dates, the criteria for the DLookups
(once they've been evaluated) might look like

[Date Leave] BETWEEN #7/1/2006# AND #7/31/2006#


John W. Vinson[MVP]

--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4367 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

 

[John Vinson]

Hello,
It still tells me that I have the wrong number of aurguments.
I copied and pasted what you gave me.
=DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
& LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#")

OK, let's break this down:

=DLookUp("FY","FY",
"[Date Leave] Between #" & [FY Start Date]
& "# and #" & [FY End Date] & "#")
& " "
& DLookUp("CC","FY",
"[Date Leave] Between # " & [FY Start Date]
& "# and #" & [FY End Date] & "#")
& " " & LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] &

^^^^^^

There you are. Apparently a typo - should be DLookUp. I don't know if
there is a Lookup function in your database, but perhaps there is and
it requires a different number of arguments.

"# and #" & [FY End Date] & "#")

John W. Vinson[MVP]

 

[Chey]

I am getting an error. I do have 3 fields that all need to DLookUp
something. I was thing of using an actual text field istead of a date field.
I was going to do a range. Another thing I was thinking is this is all
going on in a report. It is the subreport where I want the DLookUp to be.
So how would I do it if I call it out of a table Called FY and If TA Number
on Form TA for 3 Flights is between TA Start and TA Stop, which is out of my
table called FY then show the apprpriate FY for that range. Does that make
sense?
so now it would be the same for the other 3 except it would then be CC and
the next one would be LC.

Thanks for your time.

Hello,
It still tells me that I have the wrong number of aurguments.
I copied and pasted what you gave me.
=DLookUp("FY","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#") & " " & DLookUp("CC","FY","[Date Leave]
Between # " & [FY Start Date] & "# and #" & [FY End Date] & "#") & " "
& LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] & "# and
#" & [FY End Date] & "#")

OK, let's break this down:

=DLookUp("FY","FY",
"[Date Leave] Between #" & [FY Start Date]
& "# and #" & [FY End Date] & "#")
& " "
& DLookUp("CC","FY",
"[Date Leave] Between # " & [FY Start Date]
& "# and #" & [FY End Date] & "#")
& " " & LookUp("LC","FY","[Date Leave] Between #" & [FY Start Date] &

^^^^^^

There you are. Apparently a typo - should be DLookUp. I don't know if
there is a Lookup function in your database, but perhaps there is and
it requires a different number of arguments.

"# and #" & [FY End Date] & "#")

John W. Vinson[MVP]

 

[John Vinson]

I am getting an error. I do have 3 fields that all need to DLookUp
something. I was thing of using an actual text field istead of a date field.
I was going to do a range. Another thing I was thinking is this is all
going on in a report. It is the subreport where I want the DLookUp to be.
So how would I do it if I call it out of a table Called FY and If TA Number
on Form TA for 3 Flights is between TA Start and TA Stop, which is out of my
table called FY then show the apprpriate FY for that range. Does that make
sense?
so now it would be the same for the other 3 except it would then be CC and
the next one would be LC.

Chey, I simply don't understand.

If you are looking up three fields, why not look up three fields, in
three textboxes? In fact why use DLookUp at all?? You're making your
life much harder by trying to concatenate multiple fields which might
or might not exist.

Or do you want to look up just ONE FY value, if it matches any of the
three criteria for date range? If so, you only need one DLookUp, not
three.

John W. Vinson[MVP]