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?
 
Hi is it true that if you put true or false the access 2000 system gets
confused
 
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...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access stripping decimal places in query 1
Decimal places doesn't seem to work 7
Number Format - Decimal places. 2
Decimal places.. 11
Getting decimal places to display in a report in text boxes 1
Text Box formatting Access 2003 2
How can I set decimal places in a calculated control on a rpt? 2
Formatting Decimal Places 4

Back
Top