iff statement

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

Guest

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
 
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]")
 
My Table is Named FY but I am not sure what [YourField] is.

schasteen said:
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 said:
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
Click to expand...
Click to expand...
 
The name of the field or control where the 7/25/05 date is.

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

schasteen said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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]")

schasteen said:
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 said:
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
Click to expand...
Click to expand...
 
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]")

Chey said:
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]")

schasteen said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
now it tells me that I have the wrong amount of arguments.

schasteen said:
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]")

Chey said:
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]")

schasteen said:
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
Click to expand...
Click to expand...
Click to expand...
 
now it tells me that I have the wrong amount of arguments.
Click to expand...

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]
 
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?

John Vinson said:
now it tells me that I have the wrong amount of arguments.
Click to expand...

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]
Click to expand...
 
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

John Vinson said:
now it tells me that I have the wrong amount of arguments.
Click to expand...

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]
Click to expand...
 
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

Chey said:
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?

John Vinson said:
now it tells me that I have the wrong amount of arguments.
Click to expand...

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]
Click to expand...
Click to expand...
 
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

Chey said:
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?

John Vinson said:
now it tells me that I have the wrong amount of arguments.
Click to expand...

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]
Click to expand...
Click to expand...



--
 
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

Pieter Wijnen said:
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

Chey said:
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?

John Vinson said:
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]
Click to expand...
Click to expand...



--
----------------------------------------
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
Click to expand...
 
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] & "#")
Click to expand...

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]
 
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.

John Vinson said:
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] & "#")
Click to expand...

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]
Click to expand...
 
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.
Click to expand...

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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

d look up 10
Fiscal YTD Filter 7
IF Function with Dates 9
Re: How can I convert a date to a FISCAL year 0
repeating values 1
Criteria Search stopped working 5
Date Formula Needed 1
Type mismatch in Function 5

Back
Top