You are here:

Using MS Access/Access 2007 Number Format

Advertisement


Question
I am using access 2007 and have created a query that compares hourly rate on one table against the hourly rate on another table. The "Hourly Rate" field is formatted the same on each table (datatype:Number, FieldSize:double, Format:fixed, Decimals:auto) and the individual tables holding the Hourly Rate you can see the information is formatted as 32.50 or 25.00 all values have 2 decimal places even if the number has 2 zero's after the decimal. When I run the query all that shows is 32.5 or 25. My query is currently using the code  (((Nz([tblTOS].[HourlyRate],""))<>Nz([tblMASTER]![Hourly Rate],""))) to compare the hourly rate..Any Suggestions on how I would be able to format this information would be greatly appreciated.

Answer
Try it this way:

(((Nz(Round([tblTOS].[HourlyRate],2),0))<>Nz(Round([tblMASTER]![Hourly Rate],2),0)))

First, By using "" you are converting to a string so that might confuse things. Second, because you are using a Double sub type, it may have values in the 14th or 15th decimal place. Using the Round function will compare the values only to 2 decimal places.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.