Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

DateTime Field in a Query???

Discussion in 'Information Technology' started by Graham Feeley, Jul 28, 2006.

  1. Graham Feeley

    Graham Feeley
    Expand Collapse
    Guest

    Hi I have a Event Database with a Field called "RDate" which is a Datetime
    Field .I get a Minimun of 40 records a day. I would like to use this field
    to filter a particular day's events in a query. However when I use this
    field from a combobox it gives me only one record. ( because of the time
    part.
    In essence I would like to show all records only with date 27/06/2006.
    Any help would be appreciative.

    27/06/2006 11:17:00 AM
    27/06/2006 1:40:00 PM
    28/06/2006 2:23:00 PM
    etc
     
  2. Loading...


  3. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    A lot depends on where you get the data for the combo box. If you pick it
    from the the existing records in the table I suggest the following:

    SELECT DISTINCT Format(CLng([RDate]),"Short Date") AS Expr1
    FROM YourTable
    WHERE (((RDate) Is Not Null))
    ORDER BY Format(CLng([RDate]),"Short Date");

    This goes a little over the top to protect from nulls.

    Next make the criteria for the RDate field something like:
    Between [Forms]![FormName]!ComboBox] and [Forms]![FormName]!ComboBox] + .99999
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "Graham Feeley" wrote:

    > Hi I have a Event Database with a Field called "RDate" which is a Datetime
    > Field .I get a Minimun of 40 records a day. I would like to use this field
    > to filter a particular day's events in a query. However when I use this
    > field from a combobox it gives me only one record. ( because of the time
    > part.
    > In essence I would like to show all records only with date 27/06/2006.
    > Any help would be appreciative.
    >
    > 27/06/2006 11:17:00 AM
    > 27/06/2006 1:40:00 PM
    > 28/06/2006 2:23:00 PM
    > etc
     
  4. Graham Feeley

    Graham Feeley
    Expand Collapse
    Guest

    Thanks Jerry for your reponce ( and very quick I may add )
    I have tried this formula and it works in the combo box to give me short
    date to select but when i run the query to filter on the combobox I get
    different results
    I am still working on it.
    It may be a good idea to put date and time into different fields by a update
    query ????? Do u think??
    Regards
    Graham

    "Jerry Whittle" <JerryWhittle@discussions.microsoft.com> wrote in message
    news:78AD0B4E-0DEB-4E65-A476-610BF14734D8@microsoft.com...
    >A lot depends on where you get the data for the combo box. If you pick it
    > from the the existing records in the table I suggest the following:
    >
    > SELECT DISTINCT Format(CLng([RDate]),"Short Date") AS Expr1
    > FROM YourTable
    > WHERE (((RDate) Is Not Null))
    > ORDER BY Format(CLng([RDate]),"Short Date");
    >
    > This goes a little over the top to protect from nulls.
    >
    > Next make the criteria for the RDate field something like:
    > Between [Forms]![FormName]!ComboBox] and [Forms]![FormName]!ComboBox] +
    > .99999
    > --
    > Jerry Whittle
    > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    >
    >
    > "Graham Feeley" wrote:
    >
    >> Hi I have a Event Database with a Field called "RDate" which is a
    >> Datetime
    >> Field .I get a Minimun of 40 records a day. I would like to use this
    >> field
    >> to filter a particular day's events in a query. However when I use this
    >> field from a combobox it gives me only one record. ( because of the time
    >> part.
    >> In essence I would like to show all records only with date 27/06/2006.
    >> Any help would be appreciative.
    >>
    >> 27/06/2006 11:17:00 AM
    >> 27/06/2006 1:40:00 PM
    >> 28/06/2006 2:23:00 PM
    >> etc
     
  5. Jerry Whittle

    Jerry Whittle
    Expand Collapse
    Guest

    Please don't split up the date and time. You will regret it. Access has some
    very powerful tools when working with dates and times. Splitting them up will
    cause you problems elsewhere.
    --
    Jerry Whittle
    Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


    "Graham Feeley" wrote:

    > Thanks Jerry for your reponce ( and very quick I may add )
    > I have tried this formula and it works in the combo box to give me short
    > date to select but when i run the query to filter on the combobox I get
    > different results
    > I am still working on it.
    > It may be a good idea to put date and time into different fields by a update
    > query ????? Do u think??
    > Regards
    > Graham
    >
    > "Jerry Whittle" <JerryWhittle@discussions.microsoft.com> wrote in message
    > news:78AD0B4E-0DEB-4E65-A476-610BF14734D8@microsoft.com...
    > >A lot depends on where you get the data for the combo box. If you pick it
    > > from the the existing records in the table I suggest the following:
    > >
    > > SELECT DISTINCT Format(CLng([RDate]),"Short Date") AS Expr1
    > > FROM YourTable
    > > WHERE (((RDate) Is Not Null))
    > > ORDER BY Format(CLng([RDate]),"Short Date");
    > >
    > > This goes a little over the top to protect from nulls.
    > >
    > > Next make the criteria for the RDate field something like:
    > > Between [Forms]![FormName]!ComboBox] and [Forms]![FormName]!ComboBox] +
    > > .99999
    > > --
    > > Jerry Whittle
    > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
    > >
    > >
    > > "Graham Feeley" wrote:
    > >
    > >> Hi I have a Event Database with a Field called "RDate" which is a
    > >> Datetime
    > >> Field .I get a Minimun of 40 records a day. I would like to use this
    > >> field
    > >> to filter a particular day's events in a query. However when I use this
    > >> field from a combobox it gives me only one record. ( because of the time
    > >> part.
    > >> In essence I would like to show all records only with date 27/06/2006.
    > >> Any help would be appreciative.
    > >>
    > >> 27/06/2006 11:17:00 AM
    > >> 27/06/2006 1:40:00 PM
    > >> 28/06/2006 2:23:00 PM
    > >> etc

    >
    >
    >
     

Share This Page