DateTime Field in a Query???

[Graham Feeley]

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

 

[Jerry Whittle]

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

 

[Graham Feeley]

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

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.

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

 

[Jerry Whittle]

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.

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

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.

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