Mid Function

[Tony Feole]

Good afternoon,

I have a table that has dates in the Date/Time format (i.e. 04/15/2004 03:44 a.m.). I need to trim (get rid of) the time so I have only a dates. I've been using the Mid function in a query with no success. Does anyone have any suggestions?

--
Regards,

Tony Feole
Invnentory/Distribution Analyst
Ormco Corporation

 

[fredg]

Good afternoon,

I have a table that has dates in the Date/Time format (i.e.
04/15/2004 03:44 a.m.). I need to trim (get rid of) the time so I
have only a dates. I've been using the Mid function in a query
with no success. Does anyone have any suggestions?

To simply not display the Time value:
set the format property to
mm/dd/yyyy

or ...
To remove the Time value:
=Int([DateField])

 

[Tony Feole]

Hi Fred,

What i'm attempting to do is run a query that will tell me how many orders of the same item were issued on the same day. The dates consist of the Date and Time format so when i run it it doesn't group them properly with the time. So if I can remove the time portion it will work correctly. If I use the =Int([MyDate]) function it turns the date into a numeric value (i.e. 37714). I hope i'm explaining this clearly. Thanks for your help.


--
Regards,

Tony Feole
Invnentory/Distribution Analyst
Ormco Corporation

Good afternoon,

I have a table that has dates in the Date/Time format (i.e.
04/15/2004 03:44 a.m.). I need to trim (get rid of) the time so I
have only a dates. I've been using the Mid function in a query
with no success. Does anyone have any suggestions?

To simply not display the Time value:
set the format property to
mm/dd/yyyy

or ...
To remove the Time value:
=Int([DateField])

 

[Tony Feole]

Hi Bob,

That works until I hit dates with 9 digits (i.e. 4/3/2003) then I picks up the first digit of the time (i.e. 4/3/2003 8). Anything else I can do?

Thanks for your help!
--
Regards,

Tony Feole
Invnentory/Distribution Analyst
Ormco Corporation

 

[Van T. Dinh]

Create a Calculated Field in your Query:

DateOnly: DateValue([YourDateTimeField])

and then group them by this Calculated Field.

HTH
Van T. Dinh
MVP (Access)

-----Original Message-----
Hi Fred,

What i'm attempting to do is run a query that will tell

me how many orders of the same item were issued on the
same day. The dates consist of the Date and Time format
so when i run it it doesn't group them properly with the
time. So if I can remove the time portion it will work
correctly. If I use the =Int([MyDate]) function it turns
the date into a numeric value (i.e. 37714). I hope i'm
explaining this clearly. Thanks for your help.

--
Regards,

Tony Feole
Invnentory/Distribution Analyst
Ormco Corporation

Good afternoon,

I have a table that has dates in the Date/Time format (i.e.
04/15/2004 03:44 a.m.). I need to trim (get rid of) the time so I
have only a dates. I've been using the Mid function in a query
with no success. Does anyone have any suggestions?

To simply not display the Time value:
set the format property to
mm/dd/yyyy

or ...
To remove the Time value:
=Int([DateField])

.

 

[Bob M.]

Dim LengthOfDate as Integer
Dim Loopi as Interger
Dim LookForSpace as String
Dim DateOnly as String

LengthOfDate = Len( recordSet!DateTimeFieldName)
For Loopi = 1 to LengthOfDate
LookForSpace = Mid(recordSet!DateTimeFieldName,Loopi , 1)
If LookForSpace = " " Then
' first space character found which should be first space after the
date
DateOnly = left ( recordSet!DateTimeFieldName, loopi-1)
end if
Next loopi

_+_+_+_+_+_+_+_+_

Hi Bob,

That works until I hit dates with 9 digits (i.e. 4/3/2003) then I picks up

the first digit of the time (i.e. 4/3/2003 8). Anything else I can do?

 

[fredg]

Hi Fred,

What i'm attempting to do is run a query that will tell me how many
orders of the same item were issued on the same day. The dates
consist of the Date and Time format so when i run it it doesn't
group them properly with the time. So if I can remove the time
portion it will work correctly. If I use the =Int([MyDate])
function it turns the date into a numeric value (i.e. 37714). I
hope i'm explaining this clearly. Thanks for your help.

That's because the date is actually stored as a double decimal number,
with the integer portion (i.e. 37714) as the date (4/3/2003), and the
decimal portion (i.e. .5) representing the percentage of the 24 hour
day, so .5 represents Noon, .25 represents 6:00 AM., .75 represents
6:00 PM.
Today is 38181.

You could simply use
Where Format([DateField],"m/d/yyyy") Between [Start Date] and [End
Date]

The date would then be entered using m/d/yyyy format.

Or ..
Where [DateField] Between [Enter Start] and [Enter End] + 1

leaving the time field in the date as it is, but just having the query
add 1 day automatically to whatever the end date is, to include
records of that last day, regardless of their time value.
If you do it this way, you must declare the [Enter Start] and [Enter
End] parameters as Date/Time in the Query Parameter dialog box..
In design view, click Query + Parameters.

If you wish to remove permanently all the time values in the date
field, you could run an update query:
Update YourTable Set YourTable.[DateField] = int([DateField])
Where [DateField] Is Not Null;

You'll remove all the time values, but it will still be a Date/Time
field.

I hope this clarifies and helps.

 

[Bob M.]

How bout using the left

DateOnly = left ( recordSet!DateTimeFieldName,10)

then re-write using the DateOnly string

Good afternoon,

I have a table that has dates in the Date/Time format (i.e. 04/15/2004

03:44 a.m.). I need to trim (get rid of) the time so I have only a dates.
I've been using the Mid function in a query with no success. Does anyone
have any suggestions?