Dates and the 'Between' predicate

[Guest]

Re Access 97:-

I have a table (tblPeriodDates) with the following fields;
PeriodID (number)
StartDate (Date/Time)
EndDate (Date/Time)

In a separate table (tblMain) I have a field;
CRMDate (Date/Time)

I wish to return the PeriodID where the CRMDate falls between the StartDate
& EndDate from tblPeriodDates.

I have created the following column in an Access query;

PeriodNo: DLookUp("PeriodID","tblPeriodDates",[CRMDate] & " Between
[StartDate] And [EndDate]")

A single table (tblMain) is the only table included in the query, which
includes the CRMDate field from this table.

My problem is that the query does not return the correct PeriodID in the
above Dlookup function. I presume I need to convert some of these fields to
some other format perhaps. But having tried various date functions, the
solution still alludes me.

Please help.

Many thanks.

 

[Marshall Barton]

Re Access 97:-

I have a table (tblPeriodDates) with the following fields;
PeriodID (number)
StartDate (Date/Time)
EndDate (Date/Time)

In a separate table (tblMain) I have a field;
CRMDate (Date/Time)

I wish to return the PeriodID where the CRMDate falls between the StartDate
& EndDate from tblPeriodDates.

I have created the following column in an Access query;

PeriodNo: DLookUp("PeriodID","tblPeriodDates",[CRMDate] & " Between
[StartDate] And [EndDate]")

A single table (tblMain) is the only table included in the query, which
includes the CRMDate field from this table.

My problem is that the query does not return the correct PeriodID in the
above Dlookup function. I presume I need to convert some of these fields to
some other format perhaps. But having tried various date functions, the
solution still alludes me.

I think you might be right about the format being the key
here. THe concatenation is invoking an implicit conversion
to a string that may not be interpreted as a date in the
DLookup.

This is how I would do it:

. . . , Format(CRMDate, "\#m\/d\/yyyy\#") & " Between . . .

 

[John Vinson]

Re Access 97:-

I have a table (tblPeriodDates) with the following fields;
PeriodID (number)
StartDate (Date/Time)
EndDate (Date/Time)

In a separate table (tblMain) I have a field;
CRMDate (Date/Time)

I wish to return the PeriodID where the CRMDate falls between the StartDate
& EndDate from tblPeriodDates.

I have created the following column in an Access query;

PeriodNo: DLookUp("PeriodID","tblPeriodDates",[CRMDate] & " Between
[StartDate] And [EndDate]")

I'd suggest a different approach: you don't really need a DLookUp.

Instead, create a query joining tblMain to tblPeriodDates. Initially
join CRMDate to StartDate. Then view the query in SQL view and edit it
to something like

SELECT tblPeriodDates.PeriodID, tblMain.*
FROM tblPeriodDates
INNER JOIN tblMain
ON tblMain.CRMDate >= tblPeriodDates.StartDate
AND tblMain.CRMDate <= tblPeriodDates.EndDate

This "non equi join" won't show up in the query grid but will work in
the SQL window.

John W. Vinson[MVP]

 

[Marshall Barton]

Re Access 97:-

I have a table (tblPeriodDates) with the following fields;
PeriodID (number)
StartDate (Date/Time)
EndDate (Date/Time)

In a separate table (tblMain) I have a field;
CRMDate (Date/Time)

I wish to return the PeriodID where the CRMDate falls between the StartDate
& EndDate from tblPeriodDates.

I have created the following column in an Access query;

PeriodNo: DLookUp("PeriodID","tblPeriodDates",[CRMDate] & " Between
[StartDate] And [EndDate]")

I'd suggest a different approach: you don't really need a DLookUp.

Instead, create a query joining tblMain to tblPeriodDates. Initially
join CRMDate to StartDate. Then view the query in SQL view and edit it
to something like

SELECT tblPeriodDates.PeriodID, tblMain.*
FROM tblPeriodDates
INNER JOIN tblMain
ON tblMain.CRMDate >= tblPeriodDates.StartDate
AND tblMain.CRMDate <= tblPeriodDates.EndDate

This "non equi join" won't show up in the query grid but will work in
the SQL window.

Much better John.
Actually, its way more than much better ;-)

 

[John Vinson]

Much better John.
Actually, its way more than much better ;-)

<blush> I just suggested it because I couldn't figure out how to fix
the DLookUp!

John W. Vinson[MVP]

 

[Guest]

Both work correctly.

John's solution is elegant and very fast!

Time to blush a little more John!

However, I am curious as to how Marshall's solution works (with all the
Hieroglyphics) ;-)

My sincere thanks to you both.

 

[Marshall Barton]

However, I am curious as to how Marshall's solution works (with all the
Hieroglyphics) ;-)

All the format function does is explicitly convert the date
value to a standard USA date literal. The \ is the Format
function's "escape" character, which means the next
character is to be used as is. The #'s identify a date
literal value and the / characters are "escaped" as literals
because your Windows locale settings may specify that / be
changed to some other non-USA date separator. If you try
the Format function, you'll see that the result is an
unambiguous USA date literal such as #3/17/2005#.

Criptic it is, but once you understand the various
formatting code characters (see Format Property in Help),
fairly easy to decipher.

 

[Guest]

Makes sense.

Thanks for the 'master class' Marsh.

 

[Guest]

Re Access 97:-

I have a table (tblPeriodDates) with the following fields;
PeriodID (number)
StartDate (Date/Time)
EndDate (Date/Time)

In a separate table (tblMain) I have a field;
CRMDate (Date/Time)

I wish to return the PeriodID where the CRMDate falls between the StartDate
& EndDate from tblPeriodDates.

I have created the following column in an Access query;

PeriodNo: DLookUp("PeriodID","tblPeriodDates",[CRMDate] & " Between
[StartDate] And [EndDate]")

I'd suggest a different approach: you don't really need a DLookUp.

Instead, create a query joining tblMain to tblPeriodDates. Initially
join CRMDate to StartDate. Then view the query in SQL view and edit it
to something like

SELECT tblPeriodDates.PeriodID, tblMain.*
FROM tblPeriodDates
INNER JOIN tblMain
ON tblMain.CRMDate >= tblPeriodDates.StartDate
AND tblMain.CRMDate <= tblPeriodDates.EndDate

This "non equi join" won't show up in the query grid but will work in
the SQL window.

Much better John.
Actually, its way more than much better ;-)

And the DLookUp, unless you have a really small Period table, will be as
slow as!

Bruce.