2 problems with my query

  • Thread starter Thread starter LMB
  • Start date Start date
L

LMB

Hello,

I created an expression in a query in access 2000.

RoadBlockHours: [SumOfNumMinsPerRdBlk]/60

I have 2 problems when I run the query.

First problem is I get a pop up window with this in it.

Enter Parameter Value
SumOfNumMinsPerRdBlk

If I hit the enter key, the query runs

Second is....

If the SumOfNumMinsPerRdBlk is 35, I get this number in return
0.583333333333333. How do I limit it to just 2 decimal places?

Here is the sql. I don't work in the sql view so if you could post the
answer so I can use the query grid that would be great.........Thanks, Linda


SELECT tblEmpPostShiftRoadBlocks.WkAreaID, qryWorkAreaList.WorkAreaName,
Sum(tblEmpPostShiftRoadBlocks.NumMinsPerRdBlk) AS SumOfNumMinsPerRdBlk,
[SumOfNumMinsPerRdBlk]/60 AS RoadBlockHours
FROM tblEmpPostShiftRoadBlocks INNER JOIN qryWorkAreaList ON
tblEmpPostShiftRoadBlocks.WkAreaID = qryWorkAreaList.WorkAreaID
GROUP BY tblEmpPostShiftRoadBlocks.WkAreaID, qryWorkAreaList.WorkAreaName,
[SumOfNumMinsPerRdBlk]/60
ORDER BY tblEmpPostShiftRoadBlocks.WkAreaID;
 
Linda,

I suggest you replace this in your query design grid...
RoadBlockHours: [SumOfNumMinsPerRdBlk]/60
.... with this...
RoadBlockHours: Sum([NumMinsPerRdBlk])/60
.... and in the Totals row of the query design grid for this column,
change from Group By to Expression.

As regards the number of decimal places, a query datasheet is not
normally for human consumption anyway, so it doesn't really matter what
it looks like. Presumably this data is going to eventually end up on a
form or report somewhere, and this is the place to worry about
appearance. In this case, you can set the properties of the textbox to...
Format: Fixed
Decimal Places: 2
 
Thanks, Steve, that worked well. Since I am not real familiar with code,
expressions or calculations, I tried to use the expressions builder and I
think I apparently used a query instead of the table that I based my query
on and the SumOf was apparently added in the builder because that's not the
name of my row. When I went back and used the table to get my field name,
the expression worked (see Expr1: below) and gave me the same results.
Which one is better and why the same results with 2 different expressions?

Expr1: [tblEmpPostShiftRoadBlocks]![NumMinsPerRdBlk]/60

Sum: ([NumMinsPerRdBlk])/60

I do intend on using this for a report so when I get there I'll change my
properties there.

Thanks a million!

Linda



Steve Schapel said:
Linda,

I suggest you replace this in your query design grid...
RoadBlockHours: [SumOfNumMinsPerRdBlk]/60
... with this...
RoadBlockHours: Sum([NumMinsPerRdBlk])/60
... and in the Totals row of the query design grid for this column, change
from Group By to Expression.

As regards the number of decimal places, a query datasheet is not normally
for human consumption anyway, so it doesn't really matter what it looks
like. Presumably this data is going to eventually end up on a form or
report somewhere, and this is the place to worry about appearance. In
this case, you can set the properties of the textbox to...
Format: Fixed
Decimal Places: 2

--
Steve Schapel, Microsoft Access MVP

Hello,

I created an expression in a query in access 2000.

RoadBlockHours: [SumOfNumMinsPerRdBlk]/60

I have 2 problems when I run the query.

First problem is I get a pop up window with this in it.

Enter Parameter Value
SumOfNumMinsPerRdBlk

If I hit the enter key, the query runs

Second is....

If the SumOfNumMinsPerRdBlk is 35, I get this number in return
0.583333333333333. How do I limit it to just 2 decimal places?

Here is the sql. I don't work in the sql view so if you could post the
answer so I can use the query grid that would be great.........Thanks,
Linda


SELECT tblEmpPostShiftRoadBlocks.WkAreaID, qryWorkAreaList.WorkAreaName,
Sum(tblEmpPostShiftRoadBlocks.NumMinsPerRdBlk) AS SumOfNumMinsPerRdBlk,
[SumOfNumMinsPerRdBlk]/60 AS RoadBlockHours
FROM tblEmpPostShiftRoadBlocks INNER JOIN qryWorkAreaList ON
tblEmpPostShiftRoadBlocks.WkAreaID = qryWorkAreaList.WorkAreaID
GROUP BY tblEmpPostShiftRoadBlocks.WkAreaID,
qryWorkAreaList.WorkAreaName, [SumOfNumMinsPerRdBlk]/60
ORDER BY tblEmpPostShiftRoadBlocks.WkAreaID;
Click to expand...
Click to expand...
 
Linda,

[tblEmpPostShiftRoadBlocks]![NumMinsPerRdBlk]/60 and
[NumMinsPerRdBlk]/60 are identical in your case. In the first
expression, it simply identifies which table you are using the
[NumMinsPerRdBlk] field from. If only one of the tables in the query
has a [NumMinsPerRdBlk] field, then identifying the table does no harm,
but is unnecessary.
 
Back
Top