DLookup Help or other

  • Thread starter Thread starter Lou
  • Start date Start date
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
 
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]")
 
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...
 
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...
 
Back
Top