Expression Problems?

Discussion in 'Microsoft Access Queries' started by Richard C, Sep 6, 2004.

  1. Richard C

    Richard C Guest

    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
     
    Richard C, Sep 6, 2004
    #1
    1. Advertisements

  2. 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" <> wrote in message
    news:6a9001c493f7$a4cfe630$...
    > 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, Sep 6, 2004
    #2
    1. Advertisements

  3. Richard C

    Allen Browne Guest

    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" <> wrote in message
    news:6a9001c493f7$a4cfe630$...
    >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, Sep 6, 2004
    #3
  4. Richard C

    Richard C Guest

    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" <> wrote

    in message
    >news:6a9001c493f7$a4cfe630$...
    >> 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

    >
    >
    >.
    >
     
    Richard C, Sep 6, 2004
    #4
  5. Richard C

    Richard C Guest

    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" <> wrote

    in message
    >news:6a9001c493f7$a4cfe630$...
    >>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

    >
    >
    >.
    >
     
    Richard C, Sep 6, 2004
    #5
  6. Richard C

    Allen Browne Guest

    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" <> wrote in message
    news:694a01c49400$147d66d0$...
    > 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" <> wrote

    > in message
    >>news:6a9001c493f7$a4cfe630$...
    >>>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, Sep 6, 2004
    #6
  7. Richard,

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

    Nikos

    "Richard C" <> wrote in message
    news:052c01c493fb$cbcab3b0$...
    > 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" <> wrote

    > in message
    > >news:6a9001c493f7$a4cfe630$...
    > >> 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, Sep 6, 2004
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Use result of expression in another expression??

    Guest, Jan 14, 2007, in forum: Microsoft Access Queries
    Replies:
    9
    Views:
    413
    Smartin
    Jan 15, 2007
  2. Susan

    Expression too complex in query expression

    Susan, Feb 3, 2008, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    1,773
    John W. Vinson
    Feb 4, 2008
  3. Steve D
    Replies:
    7
    Views:
    178
    John Spencer
    Oct 3, 2008
  4. NG

    RE: Expression too complex in query expression

    NG, Aug 12, 2009, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    399
  5. vanderghast

    Re: Expression too complex in query expression

    vanderghast, Aug 12, 2009, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    922
    vanderghast
    Aug 12, 2009
Loading...

Share This Page