Expression Problems?

[Richard C]

I have created a query to work with my Calendar, that
checks todays events. Each event is given a start date and
an End date, but if you have multi day events, then the
middle date is missed. I have a the query detect start and
end dates, that was easy, but Im not sure how to build an
expression to detect the bit between the start and end
dates.

What would the expression be? And would I need any more
fields or could I just stick with the start and end dates
and then an expression?

Richard

 

[Nikos Yannacopoulos]

Richard,

No, you don't need any additional field(s). In your query the crirerion
should be:

=Date()

under the Start date field, and

<=Date()

under the end date field, in the same criteria row in the grid, so it's
actually an AND combination of the two.

HTH,
Nikos

 

[Allen Browne]

The in-between dates have to come from somewhere, so you will need to create
a table of dates, and use it in conjunction with your table that contains
the start and end dates.

1. Create a table with one field:
TheDate Date/Time
Make this field as primary key.
Save the table with the name tblDate.

2. Enter all the possible dates into this table, one per record. The
function below populates the tables with all the dates for the next 10
years. Change the dates in the function to get the maximum range of dates
you will ever need.

3. Create a query that contains both your events table, and this table. If
you see any line joining the 2 tables in query design view, delete the line:
it is the lack of a join (known as a Cartesian Product) that gives you every
possible combination.

4. Drag the fields you want from your event table into the grid. Drag
TheDate from tblDate into the grid, and enter this into the Criteria row
under this field:
Between [StartDate] And [EndDate]
Substitute your actual field names if they are not StartDate and EndDate.

The query returns a row for every date of the event.

Here is the function to help populate tblDate:
Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2004# To #12/31/2013#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

 

[Richard C]

Thanks for the quick reply!! I just did what you
suggested, only a problem has occurred with the results!!
This was the test data used in the query!!!

ItemID txtDateStart txtDateEnd Event_Name
9 06/09/2004 16/09/2004 Test 1 Just to
see if it gets Start Date

10 01/09/2004 06/09/2004 Test 2 To see if
it gets the End Date

11 05/09/2004 07/09/2004 Test 3 To See if
it gets the middle undefined date

The 1st 2 are to check the start and end date part of the
expression. The 3rd test was to see if it got the middle
date which would be today, but using the expression you
gave. It displayed the 1st two fine, but it didnt display
the third test!!! Wierd!!

Rich

 

[Richard C]

Is there not a more simple way of doing it, rather than
create every possible date that might be used. The way I
have this set up, is a table that takes in the event name
start date and end date etc. Then when the query is run it
will search for the matching date and displays it.

To actually create a table and enter the next 10 years of
dates sounds a bit much and not very efficient. In all
likley hood this database wont still be used by then, but
Im still not inclined to implement that solution. There
has to be an easier way??? I would have thought an
expression could have solved it, though the expression
given, doesnt quite work!!! Have you any other ideas?

Thanks for the help!!!

Rich

-----Original Message-----
The in-between dates have to come from somewhere, so you will need to create
a table of dates, and use it in conjunction with your table that contains
the start and end dates.

1. Create a table with one field:
TheDate Date/Time
Make this field as primary key.
Save the table with the name tblDate.

2. Enter all the possible dates into this table, one per record. The
function below populates the tables with all the dates for the next 10
years. Change the dates in the function to get the maximum range of dates
you will ever need.

3. Create a query that contains both your events table, and this table. If
you see any line joining the 2 tables in query design view, delete the line:
it is the lack of a join (known as a Cartesian Product) that gives you every
possible combination.

4. Drag the fields you want from your event table into the grid. Drag
TheDate from tblDate into the grid, and enter this into the Criteria row
under this field:
Between [StartDate] And [EndDate]
Substitute your actual field names if they are not StartDate and EndDate.

The query returns a row for every date of the event.

Here is the function to help populate tblDate:
Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2004# To #12/31/2013#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have created a query to work with my Calendar, that
checks todays events. Each event is given a start date and
an End date, but if you have multi day events, then the
middle date is missed. I have a the query detect start and
end dates, that was easy, but Im not sure how to build an
expression to detect the bit between the start and end
dates.

What would the expression be? And would I need any more
fields or could I just stick with the start and end dates
and then an expression?

Richard

.

 

[Allen Browne]

The Cartesian Product is the simplest and most efficient solution for
generating an entry for every entry between two discrete values.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Is there not a more simple way of doing it, rather than
create every possible date that might be used. The way I
have this set up, is a table that takes in the event name
start date and end date etc. Then when the query is run it
will search for the matching date and displays it.

To actually create a table and enter the next 10 years of
dates sounds a bit much and not very efficient. In all
likley hood this database wont still be used by then, but
Im still not inclined to implement that solution. There
has to be an easier way??? I would have thought an
expression could have solved it, though the expression
given, doesnt quite work!!! Have you any other ideas?

Thanks for the help!!!

Rich

-----Original Message-----
The in-between dates have to come from somewhere, so you will need to create
a table of dates, and use it in conjunction with your table that contains
the start and end dates.

1. Create a table with one field:
TheDate Date/Time
Make this field as primary key.
Save the table with the name tblDate.

2. Enter all the possible dates into this table, one per record. The
function below populates the tables with all the dates for the next 10
years. Change the dates in the function to get the maximum range of dates
you will ever need.

3. Create a query that contains both your events table, and this table. If
you see any line joining the 2 tables in query design view, delete the line:
it is the lack of a join (known as a Cartesian Product) that gives you every
possible combination.

4. Drag the fields you want from your event table into the grid. Drag
TheDate from tblDate into the grid, and enter this into the Criteria row
under this field:
Between [StartDate] And [EndDate]
Substitute your actual field names if they are not StartDate and EndDate.

The query returns a row for every date of the event.

Here is the function to help populate tblDate:
Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2004# To #12/31/2013#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

I have created a query to work with my Calendar, that
checks todays events. Each event is given a start date and
an End date, but if you have multi day events, then the
middle date is missed. I have a the query detect start and
end dates, that was easy, but Im not sure how to build an
expression to detect the bit between the start and end
dates.

What would the expression be? And would I need any more
fields or could I just stick with the start and end dates
and then an expression?

Richard

 

[Nikos Yannacopoulos]

Richard,

I just realized I had the two criteria the wrong way around!

Nikos