Search code examples
ms-access

Calculated value displays as hash marks (####) in Access query

I need to determine the GST amount from the retail price. I originally had the retail price plus GST (retail price * 1.1) however i the GST is included in the retail price so i need to use (retail price / 1.1) 

SELECT Groups.GroupID, Groups.GroupName, Groups.CountryOfOrigin, CDs.CDName,                   
CDs.YearOfRelease, CDs.CDType, CDs.RetailPrice,
([CDs]![RetailPrice]*1.1) AS GST_Inc
FROM Groups INNER JOIN CDs ON Groups.GroupID = CDs.GroupID
WHERE (((Groups.CountryOfOrigin)="australia") AND ((CDs.CDType)="album"))
ORDER BY Groups.GroupName, CDs.CDName;

When i change it to divide for example,

SELECT Groups.GroupID, Groups.GroupName, Groups.CountryOfOrigin, CDs.CDName, 
CDs.YearOfRelease, CDs.CDType, CDs.RetailPrice, ([CDs]![RetailPrice]/1.1) AS GST
FROM Groups INNER JOIN CDs ON Groups.GroupID = CDs.GroupID
WHERE (((Groups.CountryOfOrigin)="australia") AND ((CDs.CDType)="album"))
ORDER BY Groups.GroupName, CDs.CDName;

It does not return any value just hashtags in the field.

When Using ROUND(GST,1) for example,

SELECT Groups.GroupID, Groups.GroupName, Groups.CountryOfOrigin, CDs.CDName,
CDs.YearOfRelease, CDs.CDType, CDs.RetailPrice, ROUND (GST,1), [CDs]![RetailPrice]-    
[RetailPrice]  /1.1 AS GST
FROM Groups INNER JOIN CDs ON Groups.GroupID = CDs.GroupID
WHERE (((Groups.CountryOfOrigin)="australia") AND ((CDs.CDType)="album"))
ORDER BY Groups.GroupName, CDs.CDName;

I get two returns one as GST which is the one with like 10 decimal points, and a new field EXPR1007 with the rounded GST. How do i make the rounded GST the entry for the GST field? I have tried moving the round statement however it will not accept it.

Solution

  • In Datasheet View, Access will display hash marks (#) if numeric or date values are wider than the column will display.

    example1.png

    Just drag the right side of the column to make it wider and the numbers should appear.

    example2.png