DLookup Help or other

L

Lou

Good Day everyone.

I have a table "tblFiscalYear" that I am trying to creat a
look up on. It contains, [Period], [Start Date], [End
Date], [Fiscal Year].

I have a report that has a field [DateCreated] in which I
want to look up the date in the Fiscal Year table and
bring Back the [Period], this is what I have so far,

=DLookup("[Period]","tblFiscalYear","[DateCreated]
=Between ?????????

This is where I need the help!! How do I finish the
lookup...

Thank you.
Lou
 
M

Marshall Barton

Lou said:
I have a table "tblFiscalYear" that I am trying to creat a
look up on. It contains, [Period], [Start Date], [End
Date], [Fiscal Year].

I have a report that has a field [DateCreated] in which I
want to look up the date in the Fiscal Year table and
bring Back the [Period], this is what I have so far,

=DLookup("[Period]","tblFiscalYear","[DateCreated]
=Between ?????????
Click to expand...

Try this:

=DLookup("[Period]","tblFiscalYear",
Format(DateCreated, "\#m\/d\/yyyy\#") & " Between [Start
Date] And [End Date]")
 
L

Lou

Hello Marsh;

Thank you, I don't think I gave you the correct info,

Lookup table = FiscalYear with [Period],[FY],[Start Date]
and [End Date].

My report is based of a table, tblMain2, it has a field
[CreateDate] which is in my report. I am using that field
to lookup and bring back the [Period] FROM THE FiscalYear
table.

With that being said, I am not sure if I gave you the
correct insight on how to write the Dlookup. I did try the
first one but it did not find the [CreateDate] field.

Thank you for helping, I will await your reply.

Lou
-----Original Message-----
Lou said:
I have a table "tblFiscalYear" that I am trying to creat a
look up on. It contains, [Period], [Start Date], [End
Date], [Fiscal Year].

I have a report that has a field [DateCreated] in which I
want to look up the date in the Fiscal Year table and
bring Back the [Period], this is what I have so far,

=DLookup("[Period]","tblFiscalYear","[DateCreated]
=Between ?????????
Click to expand...

Try this:

=DLookup("[Period]","tblFiscalYear",
Format(DateCreated, "\#m\/d\/yyyy\#") & " Between [Start
Date] And [End Date]")
Click to expand...
 
M

Marshall Barton

Lou said:
Thank you, I don't think I gave you the correct info,

Lookup table = FiscalYear with [Period],[FY],[Start Date]
and [End Date].

My report is based of a table, tblMain2, it has a field
[CreateDate] which is in my report. I am using that field
to lookup and bring back the [Period] FROM THE FiscalYear
table.

With that being said, I am not sure if I gave you the
correct insight on how to write the Dlookup. I did try the
first one but it did not find the [CreateDate] field.
Click to expand...

You use different table and field names earlier. Just
change them to the ones you're really using.

=DLookup("[Period]","FiscalYear",
Format(CreateDate, "\#m\/d\/yyyy\#") & " Between [Start
Date] And [End Date]")
--
Marsh
MVP [MS Access]



-----Original Message-----
Lou said:
I have a table "tblFiscalYear" that I am trying to creat a
look up on. It contains, [Period], [Start Date], [End
Date], [Fiscal Year].

I have a report that has a field [DateCreated] in which I
want to look up the date in the Fiscal Year table and
bring Back the [Period], this is what I have so far,

=DLookup("[Period]","tblFiscalYear","[DateCreated]
=Between ?????????
Click to expand...

Try this:

=DLookup("[Period]","tblFiscalYear",
Format(DateCreated, "\#m\/d\/yyyy\#") & " Between [Start
Date] And [End Date]")
Click to expand...
Click to expand...
 
L

Lou

Thank you Marsh!

It worked, but now I am trying to figure out why it is
putting the wrong period in a specific record.

example; "CreateDate" is 8/21/04 making it period 11 of
FY04. The query is sasying that it is period 1 of 04 for
some reason. To the DLookup can we add = Start Date or is
Between Start Date and End Date..? Because some dates are
on the same day.

Thank you.
Lou
-----Original Message-----
Lou said:
Thank you, I don't think I gave you the correct info,

Lookup table = FiscalYear with [Period],[FY],[Start Date]
and [End Date].

My report is based of a table, tblMain2, it has a field
[CreateDate] which is in my report. I am using that field
to lookup and bring back the [Period] FROM THE FiscalYear
table.

With that being said, I am not sure if I gave you the
correct insight on how to write the Dlookup. I did try the
first one but it did not find the [CreateDate] field.
Click to expand...

You use different table and field names earlier. Just
change them to the ones you're really using.

=DLookup("[Period]","FiscalYear",
Format(CreateDate, "\#m\/d\/yyyy\#") & " Between [Start
Date] And [End Date]")
--
Marsh
MVP [MS Access]



-----Original Message-----
Lou wrote:
I have a table "tblFiscalYear" that I am trying to
Click to expand...
creat
a
look up on. It contains, [Period], [Start Date], [End
Date], [Fiscal Year].

I have a report that has a field [DateCreated] in
Click to expand...
which
I
want to look up the date in the Fiscal Year table and
bring Back the [Period], this is what I have so far,

=DLookup("[Period]","tblFiscalYear","[DateCreated]
=Between ?????????

Try this:

=DLookup("[Period]","tblFiscalYear",
Format(DateCreated, "\#m\/d\/yyyy\#") & " Between [Start
Date] And [End Date]")
Click to expand...
Click to expand...
.
Click to expand...
 
L

Lou

Good Day!

I figured it out MARSH, I had a bad date in my lookup
table that was skewing all the 2004 dates.

Thank you for the help!
Lou
-----Original Message-----
Thank you Marsh!

It worked, but now I am trying to figure out why it is
putting the wrong period in a specific record.

example; "CreateDate" is 8/21/04 making it period 11 of
FY04. The query is sasying that it is period 1 of 04 for
some reason. To the DLookup can we add = Start Date or is
Between Start Date and End Date..? Because some dates are
on the same day.

Thank you.
Lou
-----Original Message-----
Lou said:
Thank you, I don't think I gave you the correct info,

Lookup table = FiscalYear with [Period],[FY],[Start Date]
and [End Date].

My report is based of a table, tblMain2, it has a field
[CreateDate] which is in my report. I am using that field
to lookup and bring back the [Period] FROM THE FiscalYear
table.

With that being said, I am not sure if I gave you the
correct insight on how to write the Dlookup. I did try the
first one but it did not find the [CreateDate] field.
Click to expand...

You use different table and field names earlier. Just
change them to the ones you're really using.

=DLookup("[Period]","FiscalYear",
Format(CreateDate, "\#m\/d\/yyyy\#") & " Between [Start
Date] And [End Date]")
--
Marsh
MVP [MS Access]



-----Original Message-----
Lou wrote:
I have a table "tblFiscalYear" that I am trying to creat
a
look up on. It contains, [Period], [Start Date], [End
Date], [Fiscal Year].

I have a report that has a field [DateCreated] in which
I
want to look up the date in the Fiscal Year table and
bring Back the [Period], this is what I have so far,

=DLookup("[Period]","tblFiscalYear","[DateCreated]
=Between ?????????

Try this:

=DLookup("[Period]","tblFiscalYear",
Format(DateCreated, "\#m\/d\/yyyy\#") & " Between [Start
Date] And [End Date]")
Click to expand...
.
Click to expand...
.
Click to expand...
 
Top