Extract hour and minutes from time value

[SquirrelToothAnnie]

Hi,

I'm running a query and have a field containing the date and time. I'd like
the query to extract the Hours and Minutes from this field. I've used the
DatePart function to get the hour, but can't seem to get the minutes.

Thanks

SquirrelToothAnnie

 

[Scott Shearer]

You can use the DatePart function to get the minutes as well:
DatePart('n',[MyDateTimeField])

Search on DatePart in MS Access Help for additional details

 

[BruceM]

Try formatting the field. At the top of a blank column in query design view
put something like:

HourMin: Format([DateField],"hh:nn")

Use whatever name you choose (best to avoid spaces or characters other than
numbers, letters, and the underscore) for HourMin, and substitutute your
field name for DateField.

Use h:n as the format if you do not want leading zeros for values such as
08:07. Use AM/PM (or am/pm or A/P or a/p) for a twelve-hour clock:
Format([DateField],"hh:nn AM/PM")

 

[SquirrelToothAnnie]

Many thanks for that Bruce, it worked well. Sorry to trouble you again but
now I'd like to set the criteria for the query to be between 13:30 and 17:00
but the criteria section won't allow the :

Thanks

Try formatting the field. At the top of a blank column in query design view
put something like:

HourMin: Format([DateField],"hh:nn")

Use whatever name you choose (best to avoid spaces or characters other than
numbers, letters, and the underscore) for HourMin, and substitutute your
field name for DateField.

Use h:n as the format if you do not want leading zeros for values such as
08:07. Use AM/PM (or am/pm or A/P or a/p) for a twelve-hour clock:
Format([DateField],"hh:nn AM/PM")

Hi,

I'm running a query and have a field containing the date and time. I'd
like
the query to extract the Hours and Minutes from this field. I've used the
DatePart function to get the hour, but can't seem to get the minutes.

Thanks

SquirrelToothAnnie

 

[Ken Sheridan]

If you use a criterion of BETWEEN #13:30# AND #17:00# the query will actually
look for rows with date/time values between those times on 30 December 1899,
which I'd guess you won't have in your table. This is because there is no
such thing in Access as a time value per se, only a date/time value and day
zero in Access is 30 December 1899. Under the skin the values are
represented by a 64 bit floating point number with the integer part
representing days and the fractional part the time of day, zero being 30
December 1899 00:00:00.

What you have to do is return a value from your field which equates to its
time on 30 December 1899. The TimeValue function does this so the query's
WHERE clause would be like this:

WHERE TIMEVALUE([YourDateTimeField]) BETWEEN #13:30# AND #17:00#

To do this in design view put TimeValue([YourDateTimeField]) in the 'field'
row of a blank column, Between #13:30# And #17:00# in the criteria row, and
uncheck the 'show' checkbox.

Ken Sheridan
Stafford, England

Many thanks for that Bruce, it worked well. Sorry to trouble you again but
now I'd like to set the criteria for the query to be between 13:30 and 17:00
but the criteria section won't allow the :

Thanks

Try formatting the field. At the top of a blank column in query design view
put something like:

HourMin: Format([DateField],"hh:nn")

Use whatever name you choose (best to avoid spaces or characters other than
numbers, letters, and the underscore) for HourMin, and substitutute your
field name for DateField.

Use h:n as the format if you do not want leading zeros for values such as
08:07. Use AM/PM (or am/pm or A/P or a/p) for a twelve-hour clock:
Format([DateField],"hh:nn AM/PM")

Hi,

I'm running a query and have a field containing the date and time. I'd
like
the query to extract the Hours and Minutes from this field. I've used the
DatePart function to get the hour, but can't seem to get the minutes.

Thanks

SquirrelToothAnnie

 

[Rob]

Scott,

Is there a way to get the seconds using DatePart?

Thanks,

Rob

You can use the DatePart function to get the minutes as well:
DatePart('n',[MyDateTimeField])

Search on DatePart in MS Access Help for additional details

Hi,

I'm running a query and have a field containing the date and time. I'd like
the query to extract the Hours and Minutes from this field. I've used the
DatePart function to get the hour, but can't seem to get the minutes.

Thanks

SquirrelToothAnnie

 

[Ken Sheridan]

Rob:

Yes, use "s" as the interval argument:

DatePart("s",[MyDateTimeField])

That will give you just the seconds part of the date/time value, e.g. for 30
April 2008 23:06:12 it would return 12. Is that what you want?

Ken Sheridan
Stafford, England

Scott,

Is there a way to get the seconds using DatePart?

Thanks,

Rob

You can use the DatePart function to get the minutes as well:
DatePart('n',[MyDateTimeField])

Search on DatePart in MS Access Help for additional details

Hi,

I'm running a query and have a field containing the date and time. I'd like
the query to extract the Hours and Minutes from this field. I've used the
DatePart function to get the hour, but can't seem to get the minutes.

Thanks

SquirrelToothAnnie

 

[Rob]

Ken,

I tried that. But I keep getting a run-time error that states "The value
you entered isn't valid for this field". I am trying to place information in
a number field called Transaction Number. my line of code looks like this:

Me.Transaction_Number = DatePart("m", Now) & DatePart("d", Now) &
DatePart("yyyy", Now) & DatePart("s", Now) & Me.cmbCustomer.Column(2)

Everything works until I add the seconds. Do I need to convert the seconds
to value before using it? I think that I tried Val(DatePart("s",Now). That
gave me the same error.

Rob:

Yes, use "s" as the interval argument:

DatePart("s",[MyDateTimeField])

That will give you just the seconds part of the date/time value, e.g. for 30
April 2008 23:06:12 it would return 12. Is that what you want?

Ken Sheridan
Stafford, England

Scott,

Is there a way to get the seconds using DatePart?

Thanks,

Rob

You can use the DatePart function to get the minutes as well:
DatePart('n',[MyDateTimeField])

Search on DatePart in MS Access Help for additional details

:

Hi,

I'm running a query and have a field containing the date and time. I'd like
the query to extract the Hours and Minutes from this field. I've used the
DatePart function to get the hour, but can't seem to get the minutes.

Thanks

SquirrelToothAnnie

 

[Ken Sheridan]

Rob:

My guess would be that the Transaction_Number field is the wrong data type,
so, if I'm right, it would need changing. For a number data type use a long
integer, though as the number is essentially a 'code' rather than a numeric
value per se a Text data type might be more appropriate. However, once
you've corrected that, you can assign a value more simply with the Format
function:

Me.Transaction_Number = Format(Now(),"mdyyyys") & Me.cmbCustomer.Column(2)

If you want leading zeros either use "mmddyyyyss" and a Text data type for
the field rather than a number, or "mddyyyyss" with a long integer or text
data type and set the fields' Format property to 0000000000.

Ken Sheridan
Stafford, England

Ken,

I tried that. But I keep getting a run-time error that states "The value
you entered isn't valid for this field". I am trying to place information in
a number field called Transaction Number. my line of code looks like this:

Me.Transaction_Number = DatePart("m", Now) & DatePart("d", Now) &
DatePart("yyyy", Now) & DatePart("s", Now) & Me.cmbCustomer.Column(2)

Everything works until I add the seconds. Do I need to convert the seconds
to value before using it? I think that I tried Val(DatePart("s",Now). That
gave me the same error.

Rob:

Yes, use "s" as the interval argument:

DatePart("s",[MyDateTimeField])

That will give you just the seconds part of the date/time value, e.g. for 30
April 2008 23:06:12 it would return 12. Is that what you want?

Ken Sheridan
Stafford, England

Scott,

Is there a way to get the seconds using DatePart?

Thanks,

Rob

:

You can use the DatePart function to get the minutes as well:
DatePart('n',[MyDateTimeField])

Search on DatePart in MS Access Help for additional details

:

Hi,

I'm running a query and have a field containing the date and time. I'd like
the query to extract the Hours and Minutes from this field. I've used the
DatePart function to get the hour, but can't seem to get the minutes.

Thanks

SquirrelToothAnnie

 

[Rob]

Ken,

That worked great. One more question. How do you add minutes? Since m is
for month what is used for minutes? I want to make sure that the chance of a
duplicate is minimized. Since my back end is SQL I can't use the typical
Access auto number - which I am not a big fan of since it does not always
number sequentially.

Rob

Rob:

My guess would be that the Transaction_Number field is the wrong data type,
so, if I'm right, it would need changing. For a number data type use a long
integer, though as the number is essentially a 'code' rather than a numeric
value per se a Text data type might be more appropriate. However, once
you've corrected that, you can assign a value more simply with the Format
function:

Me.Transaction_Number = Format(Now(),"mdyyyys") & Me.cmbCustomer.Column(2)

If you want leading zeros either use "mmddyyyyss" and a Text data type for
the field rather than a number, or "mddyyyyss" with a long integer or text
data type and set the fields' Format property to 0000000000.

Ken Sheridan
Stafford, England

Ken,

I tried that. But I keep getting a run-time error that states "The value
you entered isn't valid for this field". I am trying to place information in
a number field called Transaction Number. my line of code looks like this:

Me.Transaction_Number = DatePart("m", Now) & DatePart("d", Now) &
DatePart("yyyy", Now) & DatePart("s", Now) & Me.cmbCustomer.Column(2)

Everything works until I add the seconds. Do I need to convert the seconds
to value before using it? I think that I tried Val(DatePart("s",Now). That
gave me the same error.

Rob:

Yes, use "s" as the interval argument:

DatePart("s",[MyDateTimeField])

That will give you just the seconds part of the date/time value, e.g. for 30
April 2008 23:06:12 it would return 12. Is that what you want?

Ken Sheridan
Stafford, England

:

Scott,

Is there a way to get the seconds using DatePart?

Thanks,

Rob

:

You can use the DatePart function to get the minutes as well:
DatePart('n',[MyDateTimeField])

Search on DatePart in MS Access Help for additional details

:

Hi,

I'm running a query and have a field containing the date and time. I'd like
the query to extract the Hours and Minutes from this field. I've used the
DatePart function to get the hour, but can't seem to get the minutes.

Thanks

SquirrelToothAnnie

 

[Rob]

Ken,

I figured it out. I did not realize that the order of the items determined
what the format function selected. I used mmddyyyyhhmmss and it gave me all
the data that I needed. Thanks again for the help.

Regards,

Rob

Rob:

My guess would be that the Transaction_Number field is the wrong data type,
so, if I'm right, it would need changing. For a number data type use a long
integer, though as the number is essentially a 'code' rather than a numeric
value per se a Text data type might be more appropriate. However, once
you've corrected that, you can assign a value more simply with the Format
function:

Me.Transaction_Number = Format(Now(),"mdyyyys") & Me.cmbCustomer.Column(2)

If you want leading zeros either use "mmddyyyyss" and a Text data type for
the field rather than a number, or "mddyyyyss" with a long integer or text
data type and set the fields' Format property to 0000000000.

Ken Sheridan
Stafford, England

Ken,

I tried that. But I keep getting a run-time error that states "The value
you entered isn't valid for this field". I am trying to place information in
a number field called Transaction Number. my line of code looks like this:

Me.Transaction_Number = DatePart("m", Now) & DatePart("d", Now) &
DatePart("yyyy", Now) & DatePart("s", Now) & Me.cmbCustomer.Column(2)

Everything works until I add the seconds. Do I need to convert the seconds
to value before using it? I think that I tried Val(DatePart("s",Now). That
gave me the same error.

Rob:

Yes, use "s" as the interval argument:

DatePart("s",[MyDateTimeField])

That will give you just the seconds part of the date/time value, e.g. for 30
April 2008 23:06:12 it would return 12. Is that what you want?

Ken Sheridan
Stafford, England

:

Scott,

Is there a way to get the seconds using DatePart?

Thanks,

Rob

:

You can use the DatePart function to get the minutes as well:
DatePart('n',[MyDateTimeField])

Search on DatePart in MS Access Help for additional details

:

Hi,

I'm running a query and have a field containing the date and time. I'd like
the query to extract the Hours and Minutes from this field. I've used the
DatePart function to get the hour, but can't seem to get the minutes.

Thanks

SquirrelToothAnnie

 

[Douglas J. Steele]

While Access is smart enough to interpret mmddyyyyhhmmss correctly,
officially you're supposed to use n for minutes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ken,

I figured it out. I did not realize that the order of the items
determined
what the format function selected. I used mmddyyyyhhmmss and it gave me
all
the data that I needed. Thanks again for the help.

Regards,

Rob

Rob:

My guess would be that the Transaction_Number field is the wrong data
type,
so, if I'm right, it would need changing. For a number data type use a
long
integer, though as the number is essentially a 'code' rather than a
numeric
value per se a Text data type might be more appropriate. However, once
you've corrected that, you can assign a value more simply with the Format
function:

Me.Transaction_Number = Format(Now(),"mdyyyys") &
Me.cmbCustomer.Column(2)

If you want leading zeros either use "mmddyyyyss" and a Text data type
for
the field rather than a number, or "mddyyyyss" with a long integer or
text
data type and set the fields' Format property to 0000000000.

Ken Sheridan
Stafford, England

Ken,

I tried that. But I keep getting a run-time error that states "The
value
you entered isn't valid for this field". I am trying to place
information in
a number field called Transaction Number. my line of code looks like
this:

Me.Transaction_Number = DatePart("m", Now) & DatePart("d", Now) &
DatePart("yyyy", Now) & DatePart("s", Now) & Me.cmbCustomer.Column(2)

Everything works until I add the seconds. Do I need to convert the
seconds
to value before using it? I think that I tried Val(DatePart("s",Now).
That
gave me the same error.


:

Rob:

Yes, use "s" as the interval argument:

DatePart("s",[MyDateTimeField])

That will give you just the seconds part of the date/time value, e.g.
for 30
April 2008 23:06:12 it would return 12. Is that what you want?

Ken Sheridan
Stafford, England

:

Scott,

Is there a way to get the seconds using DatePart?

Thanks,

Rob

:

You can use the DatePart function to get the minutes as well:
DatePart('n',[MyDateTimeField])

Search on DatePart in MS Access Help for additional details

:

Hi,

I'm running a query and have a field containing the date and
time. I'd like
the query to extract the Hours and Minutes from this field.
I've used the
DatePart function to get the hour, but can't seem to get the
minutes.

Thanks

SquirrelToothAnnie