Date Only from D&T field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a combo box that is based off of a date and time field.

it is used for selecting all entries from that particular day.

in the Row source, i'm looking for just distinct dates, not dates and times.
i.e. i may have an entry at 10:25 am, 10:55 AM, 12:13 PM, 1:38PM etc from
June 5th 2007, but i would need just the date of June 5th 2007 to show up
once in the combobox.

Then in the VBA code, how would i just use the date from the date/time field?

would that be something like (Date)[DateAndTimeField}?

thanks
 
In
Mike said:
I have a combo box that is based off of a date and time field.

it is used for selecting all entries from that particular day.

in the Row source, i'm looking for just distinct dates, not dates and
times. i.e. i may have an entry at 10:25 am, 10:55 AM, 12:13 PM,
1:38PM etc from June 5th 2007, but i would need just the date of
June 5th 2007 to show up once in the combobox.

Then in the VBA code, how would i just use the date from the
date/time field?

would that be something like (Date)[DateAndTimeField}?
Click to expand...

Make the combo box's RowSource query something like this:

SELECT DISTINCT DateValue(YourDateTimeField) As EntryDate
FROM YourTable
 
That's great.

But for the second part, say i wanted to select all records from june 1st.

woul the SQL statement be
"SELECT ... FROM tblCoils Where Datevalue(tblcoil.DateAndTime) = #
{DateComboBox}#;"



Dirk Goldgar said:
In
Mike said:
I have a combo box that is based off of a date and time field.

it is used for selecting all entries from that particular day.

in the Row source, i'm looking for just distinct dates, not dates and
times. i.e. i may have an entry at 10:25 am, 10:55 AM, 12:13 PM,
1:38PM etc from June 5th 2007, but i would need just the date of
June 5th 2007 to show up once in the combobox.

Then in the VBA code, how would i just use the date from the
date/time field?

would that be something like (Date)[DateAndTimeField}?
Click to expand...

Make the combo box's RowSource query something like this:

SELECT DISTINCT DateValue(YourDateTimeField) As EntryDate
FROM YourTable

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
If you're talking about building the SQL in VBA, try:

"SELECT ... FROM tblCoils " & _
"Where Datevalue(tblcoil.DateAndTime) = " & _
Format(Forms!NameOfForm!DateComboBox, "\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Mike said:
That's great.

But for the second part, say i wanted to select all records from june 1st.

woul the SQL statement be
"SELECT ... FROM tblCoils Where Datevalue(tblcoil.DateAndTime) = #
{DateComboBox}#;"



Dirk Goldgar said:
In
Mike said:
I have a combo box that is based off of a date and time field.

it is used for selecting all entries from that particular day.

in the Row source, i'm looking for just distinct dates, not dates and
times. i.e. i may have an entry at 10:25 am, 10:55 AM, 12:13 PM,
1:38PM etc from June 5th 2007, but i would need just the date of
June 5th 2007 to show up once in the combobox.

Then in the VBA code, how would i just use the date from the
date/time field?

would that be something like (Date)[DateAndTimeField}?
Click to expand...

Make the combo box's RowSource query something like this:

SELECT DISTINCT DateValue(YourDateTimeField) As EntryDate
FROM YourTable

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
In
Douglas J. Steele said:
If you're talking about building the SQL in VBA, try:

"SELECT ... FROM tblCoils " & _
"Where Datevalue(tblcoil.DateAndTime) = " & _
Format(Forms!NameOfForm!DateComboBox, "\#mm\/dd\/yyyy\#")
Click to expand...

Or, someone more complicated but probably more efficient to execute:

Dim sSQL As String
Dim sFmtDate As String

sFmtDate = Format(Forms!NameOfForm!DateComboBox, "\#mm\/dd\/yyyy\#")

sSQL = _
"SELECT ... FROM tblCoils " & _
"WHERE DateAndTime >= " & sFmtDate & _
" AND DateAndTime < (" & sFmtDate & " + 1)"
 
Thanks a bunch

Douglas J. Steele said:
If you're talking about building the SQL in VBA, try:

"SELECT ... FROM tblCoils " & _
"Where Datevalue(tblcoil.DateAndTime) = " & _
Format(Forms!NameOfForm!DateComboBox, "\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Mike said:
That's great.

But for the second part, say i wanted to select all records from june 1st.

woul the SQL statement be
"SELECT ... FROM tblCoils Where Datevalue(tblcoil.DateAndTime) = #
{DateComboBox}#;"



Dirk Goldgar said:
In I have a combo box that is based off of a date and time field.

it is used for selecting all entries from that particular day.

in the Row source, i'm looking for just distinct dates, not dates and
times. i.e. i may have an entry at 10:25 am, 10:55 AM, 12:13 PM,
1:38PM etc from June 5th 2007, but i would need just the date of
June 5th 2007 to show up once in the combobox.

Then in the VBA code, how would i just use the date from the
date/time field?

would that be something like (Date)[DateAndTimeField}?

Make the combo box's RowSource query something like this:

SELECT DISTINCT DateValue(YourDateTimeField) As EntryDate
FROM YourTable

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top