You are here:

Using MS Access/Access 2007 Number Format


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.

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,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




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


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

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

Brooklyn College BA

©2017 All rights reserved.