Using Time Intervals

[John]

I was asked to create a time sheet where people enter time into their certain
ID # so I can show a trend of their time. I want to use Access because you
can have a standalone form for someone to enter into and then the next person
comes behind them and enters theirs and so on.

I made the time sheet work but they are asking for a count of all the IDs
that fall within a certain time frame; E.I, between 2:00pm and 2:15pm there
where 5 people to enter their times.

I have been playing with a Field named [Time]. I wrote out [Time] between
#2:00:00pm# and #2:15:00pm# but keep getting an error. What am I doing wrong
with this??

For the experts out there here is the format they want;
time Total IDs
2pm - 2:15 pm 5
2:16pm - 2:30 pm 2

It would be awesome if I can TEXT the times listed and write a SQL which
pulls all [Time] between those TEXT times then calculate how many people
entered their IDs

Thanks in advance
John

 

[Douglas J. Steele]

Is the field populated using the Time function or the Now function? If it's
the Now function, it includes both date and time, so you need to wrap the
TimeValue function around the field to extract only the time.

Incidentally, rename your field. Time is a reserved word, and you should
never use reserved words for your own purposes. For a comprehensive list of
names to avoid (as well as a link to a free utility to check your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

 

[John]

Thank you very much for your insite. I created the field using the Date/Time
criteria and thanks for the lesson on "Reserved Words" never knew that.

Would my application of the Time field work??

Is the field populated using the Time function or the Now function? If it's
the Now function, it includes both date and time, so you need to wrap the
TimeValue function around the field to extract only the time.

Incidentally, rename your field. Time is a reserved word, and you should
never use reserved words for your own purposes. For a comprehensive list of
names to avoid (as well as a link to a free utility to check your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I was asked to create a time sheet where people enter time into their
certain
ID # so I can show a trend of their time. I want to use Access because
you
can have a standalone form for someone to enter into and then the next
person
comes behind them and enters theirs and so on.

I made the time sheet work but they are asking for a count of all the IDs
that fall within a certain time frame; E.I, between 2:00pm and 2:15pm
there
where 5 people to enter their times.

I have been playing with a Field named [Time]. I wrote out [Time] between
#2:00:00pm# and #2:15:00pm# but keep getting an error. What am I doing
wrong
with this??

For the experts out there here is the format they want;
time Total IDs
2pm - 2:15 pm 5
2:16pm - 2:30 pm 2

It would be awesome if I can TEXT the times listed and write a SQL which
pulls all [Time] between those TEXT times then calculate how many people
entered their IDs

Thanks in advance
John

 

[Douglas J. Steele]

I'm sorry, I don't understand what you mean by "I created the field using
the Date/Time criteria".

My question as to how the field is populated refers to how the values are
being put into the field. Are you keying values into the field, or are you
populating the field automatically? If the field is being populated
automatically, how are you doing that?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you very much for your insite. I created the field using the
Date/Time
criteria and thanks for the lesson on "Reserved Words" never knew that.

Would my application of the Time field work??

Is the field populated using the Time function or the Now function? If
it's
the Now function, it includes both date and time, so you need to wrap the
TimeValue function around the field to extract only the time.

Incidentally, rename your field. Time is a reserved word, and you should
never use reserved words for your own purposes. For a comprehensive list
of
names to avoid (as well as a link to a free utility to check your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I was asked to create a time sheet where people enter time into their
certain
ID # so I can show a trend of their time. I want to use Access because
you
can have a standalone form for someone to enter into and then the next
person
comes behind them and enters theirs and so on.

I made the time sheet work but they are asking for a count of all the
IDs
that fall within a certain time frame; E.I, between 2:00pm and 2:15pm
there
where 5 people to enter their times.

I have been playing with a Field named [Time]. I wrote out [Time]
between
#2:00:00pm# and #2:15:00pm# but keep getting an error. What am I doing
wrong
with this??

For the experts out there here is the format they want;
time Total IDs
2pm - 2:15 pm 5
2:16pm - 2:30 pm 2

It would be awesome if I can TEXT the times listed and write a SQL
which
pulls all [Time] between those TEXT times then calculate how many
people
entered their IDs

Thanks in advance
John

 

[John]

Sorry - I am learning Access so I am not familiar with all the correct
wording. I created a table which includes fields. The [Time] field has a
Default Value of time() -- is this a Now Function

I'm sorry, I don't understand what you mean by "I created the field using
the Date/Time criteria".

My question as to how the field is populated refers to how the values are
being put into the field. Are you keying values into the field, or are you
populating the field automatically? If the field is being populated
automatically, how are you doing that?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you very much for your insite. I created the field using the
Date/Time
criteria and thanks for the lesson on "Reserved Words" never knew that.

Would my application of the Time field work??

Is the field populated using the Time function or the Now function? If
it's
the Now function, it includes both date and time, so you need to wrap the
TimeValue function around the field to extract only the time.

Incidentally, rename your field. Time is a reserved word, and you should
never use reserved words for your own purposes. For a comprehensive list
of
names to avoid (as well as a link to a free utility to check your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I was asked to create a time sheet where people enter time into their
certain
ID # so I can show a trend of their time. I want to use Access because
you
can have a standalone form for someone to enter into and then the next
person
comes behind them and enters theirs and so on.

I made the time sheet work but they are asking for a count of all the
IDs
that fall within a certain time frame; E.I, between 2:00pm and 2:15pm
there
where 5 people to enter their times.

I have been playing with a Field named [Time]. I wrote out [Time]
between
#2:00:00pm# and #2:15:00pm# but keep getting an error. What am I doing
wrong
with this??

For the experts out there here is the format they want;
time Total IDs
2pm - 2:15 pm 5
2:16pm - 2:30 pm 2

It would be awesome if I can TEXT the times listed and write a SQL
which
pulls all [Time] between those TEXT times then calculate how many
people
entered their IDs

Thanks in advance
John

 

[Douglas J. Steele]

No, that's the Time() function: the Now function would be using Now()
instead. But if you've got a time-only field, theoretically your query
should work.

What's the actual SQL of the query you're trying to run? And what's the
actual error you're getting when you try and run it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry - I am learning Access so I am not familiar with all the correct
wording. I created a table which includes fields. The [Time] field has a
Default Value of time() -- is this a Now Function

I'm sorry, I don't understand what you mean by "I created the field using
the Date/Time criteria".

My question as to how the field is populated refers to how the values are
being put into the field. Are you keying values into the field, or are
you
populating the field automatically? If the field is being populated
automatically, how are you doing that?

Thank you very much for your insite. I created the field using the
Date/Time
criteria and thanks for the lesson on "Reserved Words" never knew that.

Would my application of the Time field work??

:

Is the field populated using the Time function or the Now function? If
it's
the Now function, it includes both date and time, so you need to wrap
the
TimeValue function around the field to extract only the time.

Incidentally, rename your field. Time is a reserved word, and you
should
never use reserved words for your own purposes. For a comprehensive
list
of
names to avoid (as well as a link to a free utility to check your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

I was asked to create a time sheet where people enter time into their
certain
ID # so I can show a trend of their time. I want to use Access
because
you
can have a standalone form for someone to enter into and then the
next
person
comes behind them and enters theirs and so on.

I made the time sheet work but they are asking for a count of all
the
IDs
that fall within a certain time frame; E.I, between 2:00pm and
2:15pm
there
where 5 people to enter their times.

I have been playing with a Field named [Time]. I wrote out [Time]
between
#2:00:00pm# and #2:15:00pm# but keep getting an error. What am I
doing
wrong
with this??

For the experts out there here is the format they want;
time Total IDs
2pm - 2:15 pm 5
2:16pm - 2:30 pm 2

It would be awesome if I can TEXT the times listed and write a SQL
which
pulls all [Time] between those TEXT times then calculate how many
people
entered their IDs

Thanks in advance
John

 

[john]

In the Report Creation I went into the Build Element for the Groupheader
[Time]. Where [Time] has the Grouping of : Group On : Minute and Group
Interval 15.

In the Private Sub GroupPrint I am writing
if [Time] between #2:00:00pm# And #2:14:59pm# then [Sum of IDs] else
if [Time] between #2:15:00pm# And #2:29:59pm# then [Sum of IDs]

It gives me the error "a then is missing" while highlighting the first "#"
after the between statement

[Sum of IDs] -- was created by a Query which adds all the IDs for the latest
date. I wanted to create this query to sum the IDs for each time period but
that turned out to be a huge query which slowed my computer down.

No, that's the Time() function: the Now function would be using Now()
instead. But if you've got a time-only field, theoretically your query
should work.

What's the actual SQL of the query you're trying to run? And what's the
actual error you're getting when you try and run it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry - I am learning Access so I am not familiar with all the correct
wording. I created a table which includes fields. The [Time] field has a
Default Value of time() -- is this a Now Function

I'm sorry, I don't understand what you mean by "I created the field using
the Date/Time criteria".

My question as to how the field is populated refers to how the values are
being put into the field. Are you keying values into the field, or are
you
populating the field automatically? If the field is being populated
automatically, how are you doing that?

Thank you very much for your insite. I created the field using the
Date/Time
criteria and thanks for the lesson on "Reserved Words" never knew that.

Would my application of the Time field work??

:

Is the field populated using the Time function or the Now function? If
it's
the Now function, it includes both date and time, so you need to wrap
the
TimeValue function around the field to extract only the time.

Incidentally, rename your field. Time is a reserved word, and you
should
never use reserved words for your own purposes. For a comprehensive
list
of
names to avoid (as well as a link to a free utility to check your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

I was asked to create a time sheet where people enter time into their
certain
ID # so I can show a trend of their time. I want to use Access
because
you
can have a standalone form for someone to enter into and then the
next
person
comes behind them and enters theirs and so on.

I made the time sheet work but they are asking for a count of all
the
IDs
that fall within a certain time frame; E.I, between 2:00pm and
2:15pm
there
where 5 people to enter their times.

I have been playing with a Field named [Time]. I wrote out [Time]
between
#2:00:00pm# and #2:15:00pm# but keep getting an error. What am I
doing
wrong
with this??

For the experts out there here is the format they want;
time Total IDs
2pm - 2:15 pm 5
2:16pm - 2:30 pm 2

It would be awesome if I can TEXT the times listed and write a SQL
which
pulls all [Time] between those TEXT times then calculate how many
people
entered their IDs

Thanks in advance
John

 

[Douglas J. Steele]

I don't believe you can use Between in VBA. I think you need to use

If [Time] >= #2:00:00pm# And [Time] <= #2:14:59pm# Then

However, just putting [Sum of IDs] like that is meaningless, and I'm sorry,
but I can't figure out what you're trying to do.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

In the Report Creation I went into the Build Element for the Groupheader
[Time]. Where [Time] has the Grouping of : Group On : Minute and Group
Interval 15.

In the Private Sub GroupPrint I am writing
if [Time] between #2:00:00pm# And #2:14:59pm# then [Sum of IDs] else
if [Time] between #2:15:00pm# And #2:29:59pm# then [Sum of IDs]

It gives me the error "a then is missing" while highlighting the first "#"
after the between statement

[Sum of IDs] -- was created by a Query which adds all the IDs for the
latest
date. I wanted to create this query to sum the IDs for each time period
but
that turned out to be a huge query which slowed my computer down.

No, that's the Time() function: the Now function would be using Now()
instead. But if you've got a time-only field, theoretically your query
should work.

What's the actual SQL of the query you're trying to run? And what's the
actual error you're getting when you try and run it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry - I am learning Access so I am not familiar with all the correct
wording. I created a table which includes fields. The [Time] field
has a
Default Value of time() -- is this a Now Function



:

I'm sorry, I don't understand what you mean by "I created the field
using
the Date/Time criteria".

My question as to how the field is populated refers to how the values
are
being put into the field. Are you keying values into the field, or are
you
populating the field automatically? If the field is being populated
automatically, how are you doing that?

Thank you very much for your insite. I created the field using the
Date/Time
criteria and thanks for the lesson on "Reserved Words" never knew
that.

Would my application of the Time field work??

:

Is the field populated using the Time function or the Now function?
If
it's
the Now function, it includes both date and time, so you need to
wrap
the
TimeValue function around the field to extract only the time.

Incidentally, rename your field. Time is a reserved word, and you
should
never use reserved words for your own purposes. For a comprehensive
list
of
names to avoid (as well as a link to a free utility to check your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

I was asked to create a time sheet where people enter time into
their
certain
ID # so I can show a trend of their time. I want to use Access
because
you
can have a standalone form for someone to enter into and then the
next
person
comes behind them and enters theirs and so on.

I made the time sheet work but they are asking for a count of all
the
IDs
that fall within a certain time frame; E.I, between 2:00pm and
2:15pm
there
where 5 people to enter their times.

I have been playing with a Field named [Time]. I wrote out
[Time]
between
#2:00:00pm# and #2:15:00pm# but keep getting an error. What am I
doing
wrong
with this??

For the experts out there here is the format they want;
time Total IDs
2pm - 2:15 pm 5
2:16pm - 2:30 pm 2

It would be awesome if I can TEXT the times listed and write a
SQL
which
pulls all [Time] between those TEXT times then calculate how many
people
entered their IDs

Thanks in advance
John

 

[john]

I wanted to make the discussion easy so I stated I wanted IDs but I am
looking to track 50 courier routes (what time they come in) and how many
items they bring in during that time. The Items (IDs) have 4 catagories so
the whole thing should look like;

Time Rts Arrival Item 1 Item2 Item3
Item4 Tota
--------------------------------------------------------------------------------------------
2:00-2:15 5 (rts 1 (item of 2 3
4 10
came in) this type)
2:16-2:30 4 5 6 7
8 26
all the way to 9:45pm

then have total of each colume at the bottom

As mentioned earlier I wanted to use Access so I can make a very easy menu
for Couriers and a very easy data entry area for them. This is what the Data
Entry form looks like

Section above the data entry field so they can see it but it is not part of
the normal
Entry Field
******************************************************
Date -- (with Date()) so autofill Time -- (with Time()) so autofil
----------------------------------------------------------------------------------
Route Item 1 Item2 Item 3 Item 4

******************************************************
They fill in these 5 fields and press enter and then the next courier fills
in their data

The Time on the form is what I need to use for the report I am trying to
create

Thanks
John

I don't believe you can use Between in VBA. I think you need to use

If [Time] >= #2:00:00pm# And [Time] <= #2:14:59pm# Then

However, just putting [Sum of IDs] like that is meaningless, and I'm sorry,
but I can't figure out what you're trying to do.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

In the Report Creation I went into the Build Element for the Groupheader
[Time]. Where [Time] has the Grouping of : Group On : Minute and Group
Interval 15.

In the Private Sub GroupPrint I am writing
if [Time] between #2:00:00pm# And #2:14:59pm# then [Sum of IDs] else
if [Time] between #2:15:00pm# And #2:29:59pm# then [Sum of IDs]

It gives me the error "a then is missing" while highlighting the first "#"
after the between statement

[Sum of IDs] -- was created by a Query which adds all the IDs for the
latest
date. I wanted to create this query to sum the IDs for each time period
but
that turned out to be a huge query which slowed my computer down.

No, that's the Time() function: the Now function would be using Now()
instead. But if you've got a time-only field, theoretically your query
should work.

What's the actual SQL of the query you're trying to run? And what's the
actual error you're getting when you try and run it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sorry - I am learning Access so I am not familiar with all the correct
wording. I created a table which includes fields. The [Time] field
has a
Default Value of time() -- is this a Now Function



:

I'm sorry, I don't understand what you mean by "I created the field
using
the Date/Time criteria".

My question as to how the field is populated refers to how the values
are
being put into the field. Are you keying values into the field, or are
you
populating the field automatically? If the field is being populated
automatically, how are you doing that?

Thank you very much for your insite. I created the field using the
Date/Time
criteria and thanks for the lesson on "Reserved Words" never knew
that.

Would my application of the Time field work??

:

Is the field populated using the Time function or the Now function?
If
it's
the Now function, it includes both date and time, so you need to
wrap
the
TimeValue function around the field to extract only the time.

Incidentally, rename your field. Time is a reserved word, and you
should
never use reserved words for your own purposes. For a comprehensive
list
of
names to avoid (as well as a link to a free utility to check your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

I was asked to create a time sheet where people enter time into
their
certain
ID # so I can show a trend of their time. I want to use Access
because
you
can have a standalone form for someone to enter into and then the
next
person
comes behind them and enters theirs and so on.

I made the time sheet work but they are asking for a count of all
the
IDs
that fall within a certain time frame; E.I, between 2:00pm and
2:15pm
there
where 5 people to enter their times.

I have been playing with a Field named [Time]. I wrote out
[Time]
between
#2:00:00pm# and #2:15:00pm# but keep getting an error. What am I
doing
wrong
with this??

For the experts out there here is the format they want;
time Total IDs
2pm - 2:15 pm 5
2:16pm - 2:30 pm 2

It would be awesome if I can TEXT the times listed and write a
SQL
which
pulls all [Time] between those TEXT times then calculate how many
people
entered their IDs

Thanks in advance
John

 

[Douglas J. Steele]

I'll try and take a look at this, but I probably won't get to it tonight.

One thing, though. Despite my admonition, you still have fields named Date
and Time. That's going to bite you at some point!

And in my personal opinion, you should never have date and time in separate
fields: you should always store them in a single field, the way Access
intends the Date/Time field to be used.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I wanted to make the discussion easy so I stated I wanted IDs but I am
looking to track 50 courier routes (what time they come in) and how many
items they bring in during that time. The Items (IDs) have 4 catagories
so
the whole thing should look like;

Time Rts Arrival Item 1 Item2 Item3
Item4 Total
--------------------------------------------------------------------------------------------
2:00-2:15 5 (rts 1 (item of 2 3
4 10
came in) this type)
2:16-2:30 4 5 6 7
8 26
all the way to 9:45pm

then have total of each colume at the bottom

As mentioned earlier I wanted to use Access so I can make a very easy menu
for Couriers and a very easy data entry area for them. This is what the
Data
Entry form looks like

Section above the data entry field so they can see it but it is not part
of
the normal
Entry Field
******************************************************
Date -- (with Date()) so autofill Time -- (with Time()) so
autofill
----------------------------------------------------------------------------------
Route Item 1 Item2 Item 3 Item 4

******************************************************
They fill in these 5 fields and press enter and then the next courier
fills
in their data

The Time on the form is what I need to use for the report I am trying to
create

Thanks
John

I don't believe you can use Between in VBA. I think you need to use

If [Time] >= #2:00:00pm# And [Time] <= #2:14:59pm# Then

However, just putting [Sum of IDs] like that is meaningless, and I'm
sorry,
but I can't figure out what you're trying to do.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

In the Report Creation I went into the Build Element for the
Groupheader
[Time]. Where [Time] has the Grouping of : Group On : Minute and Group
Interval 15.

In the Private Sub GroupPrint I am writing
if [Time] between #2:00:00pm# And #2:14:59pm# then [Sum of IDs] else
if [Time] between #2:15:00pm# And #2:29:59pm# then [Sum of IDs]

It gives me the error "a then is missing" while highlighting the first
"#"
after the between statement

[Sum of IDs] -- was created by a Query which adds all the IDs for the
latest
date. I wanted to create this query to sum the IDs for each time
period
but
that turned out to be a huge query which slowed my computer down.

:

No, that's the Time() function: the Now function would be using Now()
instead. But if you've got a time-only field, theoretically your query
should work.

What's the actual SQL of the query you're trying to run? And what's
the
actual error you're getting when you try and run it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sorry - I am learning Access so I am not familiar with all the
correct
wording. I created a table which includes fields. The [Time] field
has a
Default Value of time() -- is this a Now Function



:

I'm sorry, I don't understand what you mean by "I created the field
using
the Date/Time criteria".

My question as to how the field is populated refers to how the
values
are
being put into the field. Are you keying values into the field, or
are
you
populating the field automatically? If the field is being populated
automatically, how are you doing that?

Thank you very much for your insite. I created the field using
the
Date/Time
criteria and thanks for the lesson on "Reserved Words" never knew
that.

Would my application of the Time field work??

:

Is the field populated using the Time function or the Now
function?
If
it's
the Now function, it includes both date and time, so you need to
wrap
the
TimeValue function around the field to extract only the time.

Incidentally, rename your field. Time is a reserved word, and
you
should
never use reserved words for your own purposes. For a
comprehensive
list
of
names to avoid (as well as a link to a free utility to check
your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

I was asked to create a time sheet where people enter time into
their
certain
ID # so I can show a trend of their time. I want to use
Access
because
you
can have a standalone form for someone to enter into and then
the
next
person
comes behind them and enters theirs and so on.

I made the time sheet work but they are asking for a count of
all
the
IDs
that fall within a certain time frame; E.I, between 2:00pm and
2:15pm
there
where 5 people to enter their times.

I have been playing with a Field named [Time]. I wrote out
[Time]
between
#2:00:00pm# and #2:15:00pm# but keep getting an error. What
am I
doing
wrong
with this??

For the experts out there here is the format they want;
time Total IDs
2pm - 2:15 pm 5
2:16pm - 2:30 pm 2

It would be awesome if I can TEXT the times listed and write a
SQL
which
pulls all [Time] between those TEXT times then calculate how
many
people
entered their IDs

Thanks in advance
John

 

[Douglas J. Steele]

Okay, I've had a little bit of time to look at this.

What does the table in which the data's being stored look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'll try and take a look at this, but I probably won't get to it tonight.

One thing, though. Despite my admonition, you still have fields named Date
and Time. That's going to bite you at some point!

And in my personal opinion, you should never have date and time in
separate fields: you should always store them in a single field, the way
Access intends the Date/Time field to be used.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I wanted to make the discussion easy so I stated I wanted IDs but I am
looking to track 50 courier routes (what time they come in) and how many
items they bring in during that time. The Items (IDs) have 4 catagories
so
the whole thing should look like;

Time Rts Arrival Item 1 Item2 Item3
Item4 Total
--------------------------------------------------------------------------------------------
2:00-2:15 5 (rts 1 (item of 2 3
4 10
came in) this type)
2:16-2:30 4 5 6 7
8 26
all the way to 9:45pm

then have total of each colume at the bottom

As mentioned earlier I wanted to use Access so I can make a very easy
menu
for Couriers and a very easy data entry area for them. This is what the
Data
Entry form looks like

Section above the data entry field so they can see it but it is not part
of
the normal
Entry Field
******************************************************
Date -- (with Date()) so autofill Time -- (with Time()) so
autofill
----------------------------------------------------------------------------------
Route Item 1 Item2 Item 3 Item 4

******************************************************
They fill in these 5 fields and press enter and then the next courier
fills
in their data

The Time on the form is what I need to use for the report I am trying to
create

Thanks
John

I don't believe you can use Between in VBA. I think you need to use

If [Time] >= #2:00:00pm# And [Time] <= #2:14:59pm# Then

However, just putting [Sum of IDs] like that is meaningless, and I'm
sorry,
but I can't figure out what you're trying to do.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In the Report Creation I went into the Build Element for the
Groupheader
[Time]. Where [Time] has the Grouping of : Group On : Minute and
Group
Interval 15.

In the Private Sub GroupPrint I am writing
if [Time] between #2:00:00pm# And #2:14:59pm# then [Sum of IDs] else
if [Time] between #2:15:00pm# And #2:29:59pm# then [Sum of IDs]

It gives me the error "a then is missing" while highlighting the first
"#"
after the between statement

[Sum of IDs] -- was created by a Query which adds all the IDs for the
latest
date. I wanted to create this query to sum the IDs for each time
period
but
that turned out to be a huge query which slowed my computer down.

:

No, that's the Time() function: the Now function would be using Now()
instead. But if you've got a time-only field, theoretically your
query
should work.

What's the actual SQL of the query you're trying to run? And what's
the
actual error you're getting when you try and run it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sorry - I am learning Access so I am not familiar with all the
correct
wording. I created a table which includes fields. The [Time]
field
has a
Default Value of time() -- is this a Now Function



:

I'm sorry, I don't understand what you mean by "I created the
field
using
the Date/Time criteria".

My question as to how the field is populated refers to how the
values
are
being put into the field. Are you keying values into the field, or
are
you
populating the field automatically? If the field is being
populated
automatically, how are you doing that?

Thank you very much for your insite. I created the field using
the
Date/Time
criteria and thanks for the lesson on "Reserved Words" never
knew
that.

Would my application of the Time field work??

:

Is the field populated using the Time function or the Now
function?
If
it's
the Now function, it includes both date and time, so you need
to
wrap
the
TimeValue function around the field to extract only the time.

Incidentally, rename your field. Time is a reserved word, and
you
should
never use reserved words for your own purposes. For a
comprehensive
list
of
names to avoid (as well as a link to a free utility to check
your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

I was asked to create a time sheet where people enter time
into
their
certain
ID # so I can show a trend of their time. I want to use
Access
because
you
can have a standalone form for someone to enter into and then
the
next
person
comes behind them and enters theirs and so on.

I made the time sheet work but they are asking for a count of
all
the
IDs
that fall within a certain time frame; E.I, between 2:00pm
and
2:15pm
there
where 5 people to enter their times.

I have been playing with a Field named [Time]. I wrote out
[Time]
between
#2:00:00pm# and #2:15:00pm# but keep getting an error. What
am I
doing
wrong
with this??

For the experts out there here is the format they want;
time Total IDs
2pm - 2:15 pm 5
2:16pm - 2:30 pm 2

It would be awesome if I can TEXT the times listed and write
a
SQL
which
pulls all [Time] between those TEXT times then calculate how
many
people
entered their IDs

Thanks in advance
John

 

[john]

Straight down the list of fields -- About the field names I will change them
so they are not [Date] for date and [Time] for time but I got tied up doing
something else. All your recommendations and suggestions will be used.
Consequently why is it recommended to have Date/time combined?? What
advantages does that bring??

Table
Autonumber -- is an autocount so I can have a key
Date -- input Date()
Time -- input Time()
Route # -- text variable -- do not need to add this
Courier Initials -- text variable
Item 1 thru Item 4 = number variable

That is it for the table

Okay, I've had a little bit of time to look at this.

What does the table in which the data's being stored look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'll try and take a look at this, but I probably won't get to it tonight.

One thing, though. Despite my admonition, you still have fields named Date
and Time. That's going to bite you at some point!

And in my personal opinion, you should never have date and time in
separate fields: you should always store them in a single field, the way
Access intends the Date/Time field to be used.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I wanted to make the discussion easy so I stated I wanted IDs but I am
looking to track 50 courier routes (what time they come in) and how many
items they bring in during that time. The Items (IDs) have 4 catagories
so
the whole thing should look like;

Time Rts Arrival Item 1 Item2 Item3
Item4 Total
--------------------------------------------------------------------------------------------
2:00-2:15 5 (rts 1 (item of 2 3
4 10
came in) this type)
2:16-2:30 4 5 6 7
8 26
all the way to 9:45pm

then have total of each colume at the bottom

As mentioned earlier I wanted to use Access so I can make a very easy
menu
for Couriers and a very easy data entry area for them. This is what the
Data
Entry form looks like

Section above the data entry field so they can see it but it is not part
of
the normal
Entry Field
******************************************************
Date -- (with Date()) so autofill Time -- (with Time()) so
autofill
----------------------------------------------------------------------------------
Route Item 1 Item2 Item 3 Item 4

******************************************************
They fill in these 5 fields and press enter and then the next courier
fills
in their data

The Time on the form is what I need to use for the report I am trying to
create

Thanks
John


:

I don't believe you can use Between in VBA. I think you need to use

If [Time] >= #2:00:00pm# And [Time] <= #2:14:59pm# Then

However, just putting [Sum of IDs] like that is meaningless, and I'm
sorry,
but I can't figure out what you're trying to do.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In the Report Creation I went into the Build Element for the
Groupheader
[Time]. Where [Time] has the Grouping of : Group On : Minute and
Group
Interval 15.

In the Private Sub GroupPrint I am writing
if [Time] between #2:00:00pm# And #2:14:59pm# then [Sum of IDs] else
if [Time] between #2:15:00pm# And #2:29:59pm# then [Sum of IDs]

It gives me the error "a then is missing" while highlighting the first
"#"
after the between statement

[Sum of IDs] -- was created by a Query which adds all the IDs for the
latest
date. I wanted to create this query to sum the IDs for each time
period
but
that turned out to be a huge query which slowed my computer down.

:

No, that's the Time() function: the Now function would be using Now()
instead. But if you've got a time-only field, theoretically your
query
should work.

What's the actual SQL of the query you're trying to run? And what's
the
actual error you're getting when you try and run it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sorry - I am learning Access so I am not familiar with all the
correct
wording. I created a table which includes fields. The [Time]
field
has a
Default Value of time() -- is this a Now Function



:

I'm sorry, I don't understand what you mean by "I created the
field
using
the Date/Time criteria".

My question as to how the field is populated refers to how the
values
are
being put into the field. Are you keying values into the field, or
are
you
populating the field automatically? If the field is being
populated
automatically, how are you doing that?

Thank you very much for your insite. I created the field using
the
Date/Time
criteria and thanks for the lesson on "Reserved Words" never
knew
that.

Would my application of the Time field work??

:

Is the field populated using the Time function or the Now
function?
If
it's
the Now function, it includes both date and time, so you need
to
wrap
the
TimeValue function around the field to extract only the time.

Incidentally, rename your field. Time is a reserved word, and
you
should
never use reserved words for your own purposes. For a
comprehensive
list
of
names to avoid (as well as a link to a free utility to check
your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

I was asked to create a time sheet where people enter time
into
their
certain
ID # so I can show a trend of their time. I want to use
Access
because
you
can have a standalone form for someone to enter into and then
the
next
person
comes behind them and enters theirs and so on.

I made the time sheet work but they are asking for a count of
all
the
IDs
that fall within a certain time frame; E.I, between 2:00pm
and
2:15pm
there
where 5 people to enter their times.

I have been playing with a Field named [Time]. I wrote out
[Time]
between
#2:00:00pm# and #2:15:00pm# but keep getting an error. What
am I
doing
wrong
with this??

For the experts out there here is the format they want;
time Total IDs
2pm - 2:15 pm 5
2:16pm - 2:30 pm 2

It would be awesome if I can TEXT the times listed and write
a
SQL
which
pulls all [Time] between those TEXT times then calculate how
many
people
entered their IDs

Thanks in advance
John

 

[Douglas J. Steele]

The Date/Time data type in Access is actually an 8 byte floating point
number, where the integer portion represents the date as the number of days
relative to 30 Dec, 1899, and the decimal portion represents the time as a
fraction of a day. You can take advantage of that and do arithmetic to get
your ranges. Since you're interested in 15 minutes intervals, there are 96
such intervals in a day. If you use an expression like

Format(Int(TimeValue([CreatedDtm])*96)/96,"Short Time") & " to " &
Format((Int(TimeValue([CreatedDtm])*96)+1)/96,"Short Time")

you'll get the intervals you want.

I'm afraid I don't really understand what the totals you're looking for are
supposed to represent, though, so that's about all I can offer at the
moment. And just so you know, I'm on the road at the moment, so I may not be
able to get back to this before the end of the week.

There are several reasons why I suggest combinining Date and Time into a
single field. Some queries are made far easier by having Date and Time
combined. When you need just the date or just the time, you can use the
DateValue or TimeValue functions (as I did above)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Straight down the list of fields -- About the field names I will change
them
so they are not [Date] for date and [Time] for time but I got tied up
doing
something else. All your recommendations and suggestions will be used.
Consequently why is it recommended to have Date/time combined?? What
advantages does that bring??

Table
Autonumber -- is an autocount so I can have a key
Date -- input Date()
Time -- input Time()
Route # -- text variable -- do not need to add this
Courier Initials -- text variable
Item 1 thru Item 4 = number variable

That is it for the table

Okay, I've had a little bit of time to look at this.

What does the table in which the data's being stored look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'll try and take a look at this, but I probably won't get to it
tonight.

One thing, though. Despite my admonition, you still have fields named
Date
and Time. That's going to bite you at some point!

And in my personal opinion, you should never have date and time in
separate fields: you should always store them in a single field, the
way
Access intends the Date/Time field to be used.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I wanted to make the discussion easy so I stated I wanted IDs but I am
looking to track 50 courier routes (what time they come in) and how
many
items they bring in during that time. The Items (IDs) have 4
catagories
so
the whole thing should look like;

Time Rts Arrival Item 1 Item2 Item3
Item4 Total
--------------------------------------------------------------------------------------------
2:00-2:15 5 (rts 1 (item of 2 3
4 10
came in) this type)
2:16-2:30 4 5 6
7
8 26
all the way to 9:45pm

then have total of each colume at the bottom

As mentioned earlier I wanted to use Access so I can make a very easy
menu
for Couriers and a very easy data entry area for them. This is what
the
Data
Entry form looks like

Section above the data entry field so they can see it but it is not
part
of
the normal
Entry Field
******************************************************
Date -- (with Date()) so autofill Time -- (with Time()) so
autofill
----------------------------------------------------------------------------------
Route Item 1 Item2 Item 3 Item 4

******************************************************
They fill in these 5 fields and press enter and then the next courier
fills
in their data

The Time on the form is what I need to use for the report I am trying
to
create

Thanks
John


:

I don't believe you can use Between in VBA. I think you need to use

If [Time] >= #2:00:00pm# And [Time] <= #2:14:59pm# Then

However, just putting [Sum of IDs] like that is meaningless, and I'm
sorry,
but I can't figure out what you're trying to do.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


In the Report Creation I went into the Build Element for the
Groupheader
[Time]. Where [Time] has the Grouping of : Group On : Minute and
Group
Interval 15.

In the Private Sub GroupPrint I am writing
if [Time] between #2:00:00pm# And #2:14:59pm# then [Sum of IDs]
else
if [Time] between #2:15:00pm# And #2:29:59pm# then [Sum of IDs]

It gives me the error "a then is missing" while highlighting the
first
"#"
after the between statement

[Sum of IDs] -- was created by a Query which adds all the IDs for
the
latest
date. I wanted to create this query to sum the IDs for each time
period
but
that turned out to be a huge query which slowed my computer down.

:

No, that's the Time() function: the Now function would be using
Now()
instead. But if you've got a time-only field, theoretically your
query
should work.

What's the actual SQL of the query you're trying to run? And
what's
the
actual error you're getting when you try and run it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sorry - I am learning Access so I am not familiar with all the
correct
wording. I created a table which includes fields. The [Time]
field
has a
Default Value of time() -- is this a Now Function



:

I'm sorry, I don't understand what you mean by "I created the
field
using
the Date/Time criteria".

My question as to how the field is populated refers to how the
values
are
being put into the field. Are you keying values into the field,
or
are
you
populating the field automatically? If the field is being
populated
automatically, how are you doing that?

Thank you very much for your insite. I created the field
using
the
Date/Time
criteria and thanks for the lesson on "Reserved Words" never
knew
that.

Would my application of the Time field work??

:

Is the field populated using the Time function or the Now
function?
If
it's
the Now function, it includes both date and time, so you
need
to
wrap
the
TimeValue function around the field to extract only the
time.

Incidentally, rename your field. Time is a reserved word,
and
you
should
never use reserved words for your own purposes. For a
comprehensive
list
of
names to avoid (as well as a link to a free utility to check
your
application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

I was asked to create a time sheet where people enter time
into
their
certain
ID # so I can show a trend of their time. I want to use
Access
because
you
can have a standalone form for someone to enter into and
then
the
next
person
comes behind them and enters theirs and so on.

I made the time sheet work but they are asking for a count
of
all
the
IDs
that fall within a certain time frame; E.I, between 2:00pm
and
2:15pm
there
where 5 people to enter their times.

I have been playing with a Field named [Time]. I wrote
out
[Time]
between
#2:00:00pm# and #2:15:00pm# but keep getting an error.
What
am I
doing
wrong
with this??

For the experts out there here is the format they want;
time Total IDs
2pm - 2:15 pm 5
2:16pm - 2:30 pm 2

It would be awesome if I can TEXT the times listed and
write
a
SQL
which
pulls all [Time] between those TEXT times then calculate
how
many
people
entered their IDs

Thanks in advance
John