Decimal places in a calculated Text Box

  • Thread starter Thread starter Hoopster
  • Start date Start date
H

Hoopster

Hey guys,

It's been some time since I have had to come to the Community because
of a problem but I have run into a bit of a puzzler.
I am trying to get the Average Width and Length to print out on an
Access Report desplaying four decimal places. The user Form saves the
width/length to the table just fine, (four decimal places). My Query displays
the Cells as four decimal places as well. The problem occurs on my Report.
For example, I am using the following in the Control Source of the Text Box,
=Min(Nz([MinWidth])). I have the Format set to Fixed and the Decimal Places
set to 4. If the average MinWidth is 48.000 for example, my Report shows 48
Hows come I can't get this to display with four decimal places?
 
Ken,

Didn't work sir. Same result as before.....

Ken Snell (MVP) said:
Try this:

=Min(Nz([MinWidth], "0.0000")).

--

Ken Snell
<MS ACCESS MVP>



Hoopster said:
Hey guys,

It's been some time since I have had to come to the Community because
of a problem but I have run into a bit of a puzzler.
I am trying to get the Average Width and Length to print out on an
Access Report desplaying four decimal places. The user Form saves the
width/length to the table just fine, (four decimal places). My Query
displays
the Cells as four decimal places as well. The problem occurs on my Report.
For example, I am using the following in the Control Source of the Text
Box,
=Min(Nz([MinWidth])). I have the Format set to Fixed and the Decimal
Places
set to 4. If the average MinWidth is 48.000 for example, my Report shows
48
Hows come I can't get this to display with four decimal places?
Click to expand...
Click to expand...
 
Show us the SQL statement of the query that is the report's RecordSource.

--

Ken Snell
<MS ACCESS MVP>


Hoopster said:
Ken,

Didn't work sir. Same result as before.....

Ken Snell (MVP) said:
Try this:

=Min(Nz([MinWidth], "0.0000")).

--

Ken Snell
<MS ACCESS MVP>



Hoopster said:
Hey guys,

It's been some time since I have had to come to the Community
because
of a problem but I have run into a bit of a puzzler.
I am trying to get the Average Width and Length to print out on an
Access Report desplaying four decimal places. The user Form saves the
width/length to the table just fine, (four decimal places). My Query
displays
the Cells as four decimal places as well. The problem occurs on my
Report.
For example, I am using the following in the Control Source of the Text
Box,
=Min(Nz([MinWidth])). I have the Format set to Fixed and the Decimal
Places
set to 4. If the average MinWidth is 48.000 for example, my Report
shows
48
Hows come I can't get this to display with four decimal places?
Click to expand...
Click to expand...
Click to expand...
 
Ken,

As requested, here is the SQL Statement from my Query:

SELECT FlagAudits.AuditNumber, FlagAudits.StyleID, AuditFlagStyle.FlagStyle,
FlagAudits.AuditNum, FlagAudits.AuditDate, [Audit Vendor List].Vendor,
LookupQualityAuditor.AuditorName, FlagAudits.Department, FlagAudits.MinWidth,
FlagAudits.MaxWidth, (([MinWidth]+[MaxWidth])/2) AS WidthAverage,
FlagAudits.MinLength, FlagAudits.MaxLength, (([MinLength]+[MaxLength])/2) AS
LengthAverage, Disposition.Disposition, FlagAudits.DispID,
FlagAudits.[Repair/RejectComments], CauseCodes.CauseDescription,
CauseCodeFamilys.Family
FROM ((((FlagAudits INNER JOIN (CauseCodes INNER JOIN CauseCodeFamilys ON
CauseCodes.FamilyID = CauseCodeFamilys.FamilyID) ON
FlagAudits.[Repair/RejectComments] = CauseCodes.CauseCode) INNER JOIN
Disposition ON FlagAudits.DispID = Disposition.DispID) INNER JOIN
AuditFlagStyle ON FlagAudits.StyleID = AuditFlagStyle.StyleID) INNER JOIN
[Audit Vendor List] ON FlagAudits.VendorID = [Audit Vendor List].VendorID)
INNER JOIN LookupQualityAuditor ON FlagAudits.AuditorID =
LookupQualityAuditor.AuditorID
WHERE (((FlagAudits.AuditDate) Between [forms]![DateRange]![StartDate] And
[forms]![DateRange]![EndDate]) AND (([Audit Vendor
List].Vendor)="Coshocton"));

Thanks for your help.

Steve

Ken Snell (MVP) said:
Show us the SQL statement of the query that is the report's RecordSource.

--

Ken Snell
<MS ACCESS MVP>


Hoopster said:
Ken,

Didn't work sir. Same result as before.....

Ken Snell (MVP) said:
Try this:

=Min(Nz([MinWidth], "0.0000")).

--

Ken Snell
<MS ACCESS MVP>



Hey guys,

It's been some time since I have had to come to the Community
because
of a problem but I have run into a bit of a puzzler.
I am trying to get the Average Width and Length to print out on an
Access Report desplaying four decimal places. The user Form saves the
width/length to the table just fine, (four decimal places). My Query
displays
the Cells as four decimal places as well. The problem occurs on my
Report.
For example, I am using the following in the Control Source of the Text
Box,
=Min(Nz([MinWidth])). I have the Format set to Fixed and the Decimal
Places
set to 4. If the average MinWidth is 48.000 for example, my Report
shows
48
Hows come I can't get this to display with four decimal places?
Click to expand...
Click to expand...
Click to expand...
 
If you want trailing zeroes for a whole number, you have to set the format
property of the control (if the control is bound to the field and nothing
else) or you have to use a format function to turn the number into a string.

=Min(Nz([MinWidth],0)

You can either do
=Format(Min(Nz([MinWidth],0),"#,##0.0000")

I suspect the problem is that Min(Nz([MindWidth])) is returning a STRING since
you failed to specify that you wanted zero returned.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Ken,

As requested, here is the SQL Statement from my Query:

SELECT FlagAudits.AuditNumber, FlagAudits.StyleID, AuditFlagStyle.FlagStyle,
FlagAudits.AuditNum, FlagAudits.AuditDate, [Audit Vendor List].Vendor,
LookupQualityAuditor.AuditorName, FlagAudits.Department, FlagAudits.MinWidth,
FlagAudits.MaxWidth, (([MinWidth]+[MaxWidth])/2) AS WidthAverage,
FlagAudits.MinLength, FlagAudits.MaxLength, (([MinLength]+[MaxLength])/2) AS
LengthAverage, Disposition.Disposition, FlagAudits.DispID,
FlagAudits.[Repair/RejectComments], CauseCodes.CauseDescription,
CauseCodeFamilys.Family
FROM ((((FlagAudits INNER JOIN (CauseCodes INNER JOIN CauseCodeFamilys ON
CauseCodes.FamilyID = CauseCodeFamilys.FamilyID) ON
FlagAudits.[Repair/RejectComments] = CauseCodes.CauseCode) INNER JOIN
Disposition ON FlagAudits.DispID = Disposition.DispID) INNER JOIN
AuditFlagStyle ON FlagAudits.StyleID = AuditFlagStyle.StyleID) INNER JOIN
[Audit Vendor List] ON FlagAudits.VendorID = [Audit Vendor List].VendorID)
INNER JOIN LookupQualityAuditor ON FlagAudits.AuditorID =
LookupQualityAuditor.AuditorID
WHERE (((FlagAudits.AuditDate) Between [forms]![DateRange]![StartDate] And
[forms]![DateRange]![EndDate]) AND (([Audit Vendor
List].Vendor)="Coshocton"));

Thanks for your help.

Steve

Ken Snell (MVP) said:
Show us the SQL statement of the query that is the report's RecordSource.

--

Ken Snell
<MS ACCESS MVP>


Hoopster said:
Ken,

Didn't work sir. Same result as before.....

:

Try this:

=Min(Nz([MinWidth], "0.0000")).

--

Ken Snell
<MS ACCESS MVP>



Hey guys,

It's been some time since I have had to come to the Community
because
of a problem but I have run into a bit of a puzzler.
I am trying to get the Average Width and Length to print out on an
Access Report desplaying four decimal places. The user Form saves the
width/length to the table just fine, (four decimal places). My Query
displays
the Cells as four decimal places as well. The problem occurs on my
Report.
For example, I am using the following in the Control Source of the Text
Box,
=Min(Nz([MinWidth])). I have the Format set to Fixed and the Decimal
Places
set to 4. If the average MinWidth is 48.000 for example, my Report
shows
48
Hows come I can't get this to display with four decimal places?
Click to expand...
Click to expand...
Click to expand...
 
Sorry John,

Struck out again. When I tried =Min(Nz([MinWidth],0), I recieved the
Error telling me I was missing a closing parenthesis. When I inserted the
parenthesis I recived the Error Message "The expression you entered has a
funtion containing the wrong number of arguments". I recieved the same error
when I used =Format(Min(Nz([MinWidth],0),"#,##0.0000").
The controls in my Table are set up as Fixed using 4 decimal places.
The same is true of the columns in my Query as well as the controls in my
Report.

John Spencer said:
If you want trailing zeroes for a whole number, you have to set the format
property of the control (if the control is bound to the field and nothing
else) or you have to use a format function to turn the number into a string.

=Min(Nz([MinWidth],0)

You can either do
=Format(Min(Nz([MinWidth],0),"#,##0.0000")

I suspect the problem is that Min(Nz([MindWidth])) is returning a STRING since
you failed to specify that you wanted zero returned.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Ken,

As requested, here is the SQL Statement from my Query:

SELECT FlagAudits.AuditNumber, FlagAudits.StyleID, AuditFlagStyle.FlagStyle,
FlagAudits.AuditNum, FlagAudits.AuditDate, [Audit Vendor List].Vendor,
LookupQualityAuditor.AuditorName, FlagAudits.Department, FlagAudits.MinWidth,
FlagAudits.MaxWidth, (([MinWidth]+[MaxWidth])/2) AS WidthAverage,
FlagAudits.MinLength, FlagAudits.MaxLength, (([MinLength]+[MaxLength])/2) AS
LengthAverage, Disposition.Disposition, FlagAudits.DispID,
FlagAudits.[Repair/RejectComments], CauseCodes.CauseDescription,
CauseCodeFamilys.Family
FROM ((((FlagAudits INNER JOIN (CauseCodes INNER JOIN CauseCodeFamilys ON
CauseCodes.FamilyID = CauseCodeFamilys.FamilyID) ON
FlagAudits.[Repair/RejectComments] = CauseCodes.CauseCode) INNER JOIN
Disposition ON FlagAudits.DispID = Disposition.DispID) INNER JOIN
AuditFlagStyle ON FlagAudits.StyleID = AuditFlagStyle.StyleID) INNER JOIN
[Audit Vendor List] ON FlagAudits.VendorID = [Audit Vendor List].VendorID)
INNER JOIN LookupQualityAuditor ON FlagAudits.AuditorID =
LookupQualityAuditor.AuditorID
WHERE (((FlagAudits.AuditDate) Between [forms]![DateRange]![StartDate] And
[forms]![DateRange]![EndDate]) AND (([Audit Vendor
List].Vendor)="Coshocton"));

Thanks for your help.

Steve

Ken Snell (MVP) said:
Show us the SQL statement of the query that is the report's RecordSource.

--

Ken Snell
<MS ACCESS MVP>


Ken,

Didn't work sir. Same result as before.....

:

Try this:

=Min(Nz([MinWidth], "0.0000")).

--

Ken Snell
<MS ACCESS MVP>



Hey guys,

It's been some time since I have had to come to the Community
because
of a problem but I have run into a bit of a puzzler.
I am trying to get the Average Width and Length to print out on an
Access Report desplaying four decimal places. The user Form saves the
width/length to the table just fine, (four decimal places). My Query
displays
the Cells as four decimal places as well. The problem occurs on my
Report.
For example, I am using the following in the Control Source of the Text
Box,
=Min(Nz([MinWidth])). I have the Format set to Fixed and the Decimal
Places
set to 4. If the average MinWidth is 48.000 for example, my Report
shows
48
Hows come I can't get this to display with four decimal places?
Click to expand...
Click to expand...
Click to expand...
 
Well, we did miss closing parens.

=Min(Nz([MinWidth],0))

You can either do
=Format(Min(Nz([MinWidth],0)),"#,##0.0000")

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
John,

I had tried both of these before but though I would try
=Format(Min(Nz([MaxWidth],0)),'#,##0.0000') again only changing the " around
the Format to '. This works fine. Thanks for all your help.

Steve

John Spencer said:
Well, we did miss closing parens.

=Min(Nz([MinWidth],0))

You can either do
=Format(Min(Nz([MaxWidth],0)),'#,##0.0000')

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Sorry John,

Struck out again. When I tried =Min(Nz([MinWidth],0), I recieved the
Error telling me I was missing a closing parenthesis. When I inserted the
parenthesis I recived the Error Message "The expression you entered has a
funtion containing the wrong number of arguments". I recieved the same error
when I used =Format(Min(Nz([MinWidth],0),"#,##0.0000").
The controls in my Table are set up as Fixed using 4 decimal places.
The same is true of the columns in my Query as well as the controls in my
Report.

"John Spencer" wrote:
Click to expand...
Click to expand...
 
Back
Top