PC Review


Reply
Thread Tools Rate Thread

Expression Problems?

 
 
Richard C
Guest
Posts: n/a
 
      6th Sep 2004
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
 
Reply With Quote
 
 
 
 
Nikos Yannacopoulos
Guest
Posts: n/a
 
      6th Sep 2004
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


"Richard C" <(E-Mail Removed)> wrote in message
news:6a9001c493f7$a4cfe630$(E-Mail Removed)...
> 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



 
Reply With Quote
 
 
 
 
Allen Browne
Guest
Posts: n/a
 
      6th Sep 2004
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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard C" <(E-Mail Removed)> wrote in message
news:6a9001c493f7$a4cfe630$(E-Mail Removed)...
>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



 
Reply With Quote
 
Richard C
Guest
Posts: n/a
 
      6th Sep 2004
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

>-----Original Message-----
>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
>
>
>"Richard C" <(E-Mail Removed)> wrote

in message
>news:6a9001c493f7$a4cfe630$(E-Mail Removed)...
>> 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

>
>
>.
>

 
Reply With Quote
 
Richard C
Guest
Posts: n/a
 
      6th Sep 2004
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.
>Tips for Access users - http://allenbrowne.com/tips.html
>Reply to group, rather than allenbrowne at mvps dot org.
>
>"Richard C" <(E-Mail Removed)> wrote

in message
>news:6a9001c493f7$a4cfe630$(E-Mail Removed)...
>>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

>
>
>.
>

 
Reply With Quote
 
Allen Browne
Guest
Posts: n/a
 
      6th Sep 2004
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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Richard C" <(E-Mail Removed)> wrote in message
news:694a01c49400$147d66d0$(E-Mail Removed)...
> 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
>>
>>
>>"Richard C" <(E-Mail Removed)> wrote

> in message
>>news:6a9001c493f7$a4cfe630$(E-Mail Removed)...
>>>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



 
Reply With Quote
 
Nikos Yannacopoulos
Guest
Posts: n/a
 
      6th Sep 2004
Richard,

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

Nikos

"Richard C" <(E-Mail Removed)> wrote in message
news:052c01c493fb$cbcab3b0$(E-Mail Removed)...
> 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
>
> >-----Original Message-----
> >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
> >
> >
> >"Richard C" <(E-Mail Removed)> wrote

> in message
> >news:6a9001c493f7$a4cfe630$(E-Mail Removed)...
> >> 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

> >
> >
> >.
> >



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Expression Column based on another expression column - Is this possible? APops Microsoft ADO .NET 3 27th Apr 2005 10:13 PM
DataSet.Merge with Column.Expression. expression not =?Utf-8?B?R2llZHJpdXM=?= Microsoft ADO .NET 4 12th Jan 2005 10:45 AM
expression builder erased my expression =?Utf-8?B?U3RlZmFubw==?= Microsoft Access 1 6th Dec 2004 11:17 PM
Customizing Regular Expression Editor for Regular Expression Validator Control Jason Timmerman Microsoft Dot NET Framework 0 27th Oct 2003 09:16 PM
Re: Macro Error: & "Function is not available in expression in criteria expression" Douglas J. Steele Microsoft Access 1 4th Aug 2003 10:17 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:16 AM.