#Error Message

[Guest]

I have been working on a database that calculates the cost/usage of the
peridocials by dviding the cost by the usage. If the value of the usage is
0, then the cost per usage is displayed as #Error. How can I have this
#Error replaced by a zero.

See below the SQL Statements I have used to calculate this value:


SELECT Listings.Title, Listings.Cost, Listings.Use, [cost]/[use] AS [Cost
per Use], Listings.Code, Listings.Supplier
FROM Listings
ORDER BY Listings.Title;

Any help with this matter will be appreciated.

Sincerely,

Tiffany Miller

 

[Allen Browne]

Use IIf() to test if [use] is zero:
SELECT Listings.Title, Listings.Cost, Listings.Use,
IIf([use]=0, 0, [cost]/[use]) AS [Cost per Use],
Listings.Code, Listings.Supplier
FROM Listings
ORDER BY Listings.Title;

Better still:
SELECT Listings.Title, Listings.Cost, Listings.Use,
CCur(Nz(IIf([use]=0, 0, [cost]/[use]),0)) AS [Cost per Use],
Listings.Code, Listings.Supplier
FROM Listings
ORDER BY Listings.Title;
Explanation of why:
http://allenbrowne.com/ser-45.html

 

[Guest]

Thank you, you were more than helpful. I was wondering, I'm new to
Microsoft Access and only slightly familiar with SQL, do you know of any good
online resources where I can learn more. I'm very interested in becoming a
better coders. As of now the only sites I use are w3schools.com and this
site.

Thank you for your Assistance again.

Tiffany Miller

Use IIf() to test if [use] is zero:
SELECT Listings.Title, Listings.Cost, Listings.Use,
IIf([use]=0, 0, [cost]/[use]) AS [Cost per Use],
Listings.Code, Listings.Supplier
FROM Listings
ORDER BY Listings.Title;

Better still:
SELECT Listings.Title, Listings.Cost, Listings.Use,
CCur(Nz(IIf([use]=0, 0, [cost]/[use]),0)) AS [Cost per Use],
Listings.Code, Listings.Supplier
FROM Listings
ORDER BY Listings.Title;
Explanation of why:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have been working on a database that calculates the cost/usage of the
peridocials by dviding the cost by the usage. If the value of the usage
is
0, then the cost per usage is displayed as #Error. How can I have this
#Error replaced by a zero.

See below the SQL Statements I have used to calculate this value:


SELECT Listings.Title, Listings.Cost, Listings.Use, [cost]/[use] AS [Cost
per Use], Listings.Code, Listings.Supplier
FROM Listings
ORDER BY Listings.Title;

Any help with this matter will be appreciated.

Sincerely,

Tiffany Miller

 

[Allen Browne]

The links I recommend for Access are listed here:
http://allenbrowne.com/tips.html#links

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you, you were more than helpful. I was wondering, I'm new to
Microsoft Access and only slightly familiar with SQL, do you know of any
good
online resources where I can learn more. I'm very interested in becoming
a
better coders. As of now the only sites I use are w3schools.com and this
site.

Thank you for your Assistance again.

Tiffany Miller

Use IIf() to test if [use] is zero:
SELECT Listings.Title, Listings.Cost, Listings.Use,
IIf([use]=0, 0, [cost]/[use]) AS [Cost per Use],
Listings.Code, Listings.Supplier
FROM Listings
ORDER BY Listings.Title;

Better still:
SELECT Listings.Title, Listings.Cost, Listings.Use,
CCur(Nz(IIf([use]=0, 0, [cost]/[use]),0)) AS [Cost per Use],
Listings.Code, Listings.Supplier
FROM Listings
ORDER BY Listings.Title;
Explanation of why:
http://allenbrowne.com/ser-45.html

I have been working on a database that calculates the cost/usage of the
peridocials by dviding the cost by the usage. If the value of the
usage
is
0, then the cost per usage is displayed as #Error. How can I have this
#Error replaced by a zero.

See below the SQL Statements I have used to calculate this value:


SELECT Listings.Title, Listings.Cost, Listings.Use, [cost]/[use] AS
[Cost
per Use], Listings.Code, Listings.Supplier
FROM Listings
ORDER BY Listings.Title;

Any help with this matter will be appreciated.

Sincerely,

Tiffany Miller