using <= and >= in an expression

  • Thread starter Thread starter Cheryl
  • Start date Start date
C

Cheryl

Hi,
I'm trying to set up a query that will return how many
meals allowed per shift hours worked.

I created a query that returns the hours worked and am now
using the "HrMinWorked" field from that query to try and
get this new query working.

So far all my efforts have returned #Error and -1???

I need to store these values, as I will need to run a
monthly and year end report showing the amount of meals
allowed respectively.

This is my expression:

MealsAllowed:(([HrMin]<="24")/"8") + ([HrMin]
="24:01")/"4")
Click to expand...

So....they are allowed 1 meal for every 8 hours worked for
the first 24 hours on shift, and after 24 hours worked,
they are allowed 1 meal for every 4 hours worked or a
portion thereof.

I will also need this to work is they work less than 24
hours on a shift.

Thanks in advance for your help.
Cheryl
 
Cheryl,

I don't fully follow what you're up to, but let's see if we can clean it up
a little. Also, it is not recommended, in most cases, to store values that
you can calculate.

Let's start with your current equation:
MealsAllowed:(([HrMin]<="24")/"8") + ([HrMin]>="24:01")/"4")

1) You have all the numbers in quotes as if they are text, is there a reason
for this?

2) (([HrMin]<="24")/"8"), The part inside the inner parenthesis will be done
first. [HrMin]<=24 will return True (-1) or False(0) then divide that value
by 8. This will give a result of -1/8 or 0.

3) ([HrMin]>="24:01"), Again, this will return True (-1) or False (0) then
divide by 4. Also, what data type is [HrMin] and what do its values look
like? I assume you mean 24 hours and 1 minute.

--
Wayne Morgan
Microsoft Access MVP


Cheryl said:
Hi,
I'm trying to set up a query that will return how many
meals allowed per shift hours worked.

I created a query that returns the hours worked and am now
using the "HrMinWorked" field from that query to try and
get this new query working.

So far all my efforts have returned #Error and -1???

I need to store these values, as I will need to run a
monthly and year end report showing the amount of meals
allowed respectively.

This is my expression:

MealsAllowed:(([HrMin]<="24")/"8") + ([HrMin]
="24:01")/"4")
Click to expand...

So....they are allowed 1 meal for every 8 hours worked for
the first 24 hours on shift, and after 24 hours worked,
they are allowed 1 meal for every 4 hours worked or a
portion thereof.

I will also need this to work is they work less than 24
hours on a shift.

Thanks in advance for your help.
Cheryl
Click to expand...
 
Wayne,

The equation comes from my old algebra days when you
enclosed what you wanted done together in brackets - and
some studying of online help manuals.

I orginally started out as wanting to use a calculated
control on a form to achieve the meal allowance, but was
advised that if I wanted to store the values, I needed to
do something other than using a form. Perhaps I'm
misunderstanding the meaning of "stored values".
Ultimately, I need to be able to print a report indicating
how many meals have been allowed, monthly and annually.

No reason for the numbers in quotes - just experimenting
to see what will work. Looking again at my equation
though, I'm thinking that I need to show the 24 and 8 as
hours and minutes ? yes?

The HrMin in the brackets is a field in my first query
that returns the total number of hours worked. The
expression for that field follows:

HrMin: Format(DateDiff("n",[DateOrdered]+[StartTime1],
[DateHome]+[EndTime2])\60,"0\:") & Format(DateDiff("n",
[DateOrdered]+[StartTime1],[DateHome]+[EndTime2]) Mod
60,"00")

I don't have any format set for the data type of the HrMin
field. I'm thinking it is set in the expression above.
Anyway, what it returns if this: 27:50 (27 hours and 50
minutes worked in a shift.)

I then want to take the value from the HrMin field and
calculate how many meals are allowed.

For example:

The person works 27 hours 50 minutes. The formula above
stores it as "27:50" (without the quotes). I want to then
take the 27:50 and calculate that the person would be
allowed 3 meals for the first 24 hours, and 1 meal for the
3 hours and 50 minutes portion of their shift, beyond the
24 hours.

If I'm totally off track with how I'm headed to solve
this, please don't hesitate to let me know! I've been
working at this for about a month already, and quite
frankly have taken out shares in 'Miss Clairol' to cover
the resulting gray hair!!!

Many Thanks
Cheryl
-----Original Message-----
Cheryl,

I don't fully follow what you're up to, but let's see if we can clean it up
a little. Also, it is not recommended, in most cases, to store values that
you can calculate.

Let's start with your current equation:
MealsAllowed:(([HrMin]<="24")/"8") + ([HrMin]
="24:01")/"4")

1) You have all the numbers in quotes as if they are text, is there a reason
for this?

2) (([HrMin]<="24")/"8"), The part inside the inner parenthesis will be done
first. [HrMin]<=24 will return True (-1) or False(0) then divide that value
by 8. This will give a result of -1/8 or 0.

3) ([HrMin]>="24:01"), Again, this will return True (-1) or False (0) then
divide by 4. Also, what data type is [HrMin] and what do its values look
like? I assume you mean 24 hours and 1 minute.

--
Wayne Morgan
Microsoft Access MVP


Hi,
I'm trying to set up a query that will return how many
meals allowed per shift hours worked.

I created a query that returns the hours worked and am now
using the "HrMinWorked" field from that query to try and
get this new query working.

So far all my efforts have returned #Error and -1???

I need to store these values, as I will need to run a
monthly and year end report showing the amount of meals
allowed respectively.

This is my expression:

MealsAllowed:(([HrMin]<="24")/"8") + ([HrMin]
="24:01")/"4")
Click to expand...

So....they are allowed 1 meal for every 8 hours worked for
the first 24 hours on shift, and after 24 hours worked,
they are allowed 1 meal for every 4 hours worked or a
portion thereof.

I will also need this to work is they work less than 24
hours on a shift.

Thanks in advance for your help.
Cheryl
Click to expand...


.
Click to expand...
 
Cheryl,

When you have the data in hh:mm format, while it is easy for the human to
read, it can be a real pain in your calculations. A date/time value is a
point in time, not an elapsed time (i.e. 2 May 2004 3:00:00 PM, not the
difference between this and 5PM which would be 2 hours or 120 minutes). I
believe you will find your calculations to be much simpler if you use your
smallest unit (in this case minutes) to do all of the calculations. This
would change 24 hours to 1440 minutes. You can now add/subtract as needed
because everything is in the same units. To get to this point, you would
start with 2 date/time values and get the difference in minutes (possibly
using DateDiff) then do your calculations. Once you are done with the
calculations, you can format it for human consumption to make it easier to
read.

"Storing" the values would be writing them to a table for later retrieval.
However, if you have the necessary data already in your tables to calculate
the meal breaks, you could use this calculation in a calculated field in a
query then sum that field if you need to get a total. The values don't have
to come from a table to be used in the query. If you find you have to store
the values, this can be done, but the calculation needs to be correct first
or what you store won't be worth anything.

--
Wayne Morgan
Microsoft Access MVP


Wayne,

The equation comes from my old algebra days when you
enclosed what you wanted done together in brackets - and
some studying of online help manuals.

I orginally started out as wanting to use a calculated
control on a form to achieve the meal allowance, but was
advised that if I wanted to store the values, I needed to
do something other than using a form. Perhaps I'm
misunderstanding the meaning of "stored values".
Ultimately, I need to be able to print a report indicating
how many meals have been allowed, monthly and annually.

No reason for the numbers in quotes - just experimenting
to see what will work. Looking again at my equation
though, I'm thinking that I need to show the 24 and 8 as
hours and minutes ? yes?

The HrMin in the brackets is a field in my first query
that returns the total number of hours worked. The
expression for that field follows:

HrMin: Format(DateDiff("n",[DateOrdered]+[StartTime1],
[DateHome]+[EndTime2])\60,"0\:") & Format(DateDiff("n",
[DateOrdered]+[StartTime1],[DateHome]+[EndTime2]) Mod
60,"00")

I don't have any format set for the data type of the HrMin
field. I'm thinking it is set in the expression above.
Anyway, what it returns if this: 27:50 (27 hours and 50
minutes worked in a shift.)

I then want to take the value from the HrMin field and
calculate how many meals are allowed.

For example:

The person works 27 hours 50 minutes. The formula above
stores it as "27:50" (without the quotes). I want to then
take the 27:50 and calculate that the person would be
allowed 3 meals for the first 24 hours, and 1 meal for the
3 hours and 50 minutes portion of their shift, beyond the
24 hours.

If I'm totally off track with how I'm headed to solve
this, please don't hesitate to let me know! I've been
working at this for about a month already, and quite
frankly have taken out shares in 'Miss Clairol' to cover
the resulting gray hair!!!
Click to expand...
 
Thanks Wayne. I'll work at fixing this up for myself.
Cheryl
-----Original Message-----
Cheryl,

When you have the data in hh:mm format, while it is easy for the human to
read, it can be a real pain in your calculations. A date/time value is a
point in time, not an elapsed time (i.e. 2 May 2004 3:00:00 PM, not the
difference between this and 5PM which would be 2 hours or 120 minutes). I
believe you will find your calculations to be much simpler if you use your
smallest unit (in this case minutes) to do all of the calculations. This
would change 24 hours to 1440 minutes. You can now add/subtract as needed
because everything is in the same units. To get to this point, you would
start with 2 date/time values and get the difference in minutes (possibly
using DateDiff) then do your calculations. Once you are done with the
calculations, you can format it for human consumption to make it easier to
read.

"Storing" the values would be writing them to a table for later retrieval.
However, if you have the necessary data already in your tables to calculate
the meal breaks, you could use this calculation in a calculated field in a
query then sum that field if you need to get a total. The values don't have
to come from a table to be used in the query. If you find you have to store
the values, this can be done, but the calculation needs to be correct first
or what you store won't be worth anything.

--
Wayne Morgan
Microsoft Access MVP


Wayne,

The equation comes from my old algebra days when you
enclosed what you wanted done together in brackets - and
some studying of online help manuals.

I orginally started out as wanting to use a calculated
control on a form to achieve the meal allowance, but was
advised that if I wanted to store the values, I needed to
do something other than using a form. Perhaps I'm
misunderstanding the meaning of "stored values".
Ultimately, I need to be able to print a report indicating
how many meals have been allowed, monthly and annually.

No reason for the numbers in quotes - just experimenting
to see what will work. Looking again at my equation
though, I'm thinking that I need to show the 24 and 8 as
hours and minutes ? yes?

The HrMin in the brackets is a field in my first query
that returns the total number of hours worked. The
expression for that field follows:

HrMin: Format(DateDiff("n",[DateOrdered]+[StartTime1],
[DateHome]+[EndTime2])\60,"0\:") & Format(DateDiff("n",
[DateOrdered]+[StartTime1],[DateHome]+[EndTime2]) Mod
60,"00")

I don't have any format set for the data type of the HrMin
field. I'm thinking it is set in the expression above.
Anyway, what it returns if this: 27:50 (27 hours and 50
minutes worked in a shift.)

I then want to take the value from the HrMin field and
calculate how many meals are allowed.

For example:

The person works 27 hours 50 minutes. The formula above
stores it as "27:50" (without the quotes). I want to then
take the 27:50 and calculate that the person would be
allowed 3 meals for the first 24 hours, and 1 meal for the
3 hours and 50 minutes portion of their shift, beyond the
24 hours.

If I'm totally off track with how I'm headed to solve
this, please don't hesitate to let me know! I've been
working at this for about a month already, and quite
frankly have taken out shares in 'Miss Clairol' to cover
the resulting gray hair!!!
Click to expand...


.
Click to expand...
 
Back
Top