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:
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