asked on

Access 2007 Can I eliminate a Null Value Error?

I have the following select statement below that works. The problem is if there are NO orders that are less than todays date I get this error message because there is nothing to sum. Is there a way to write the select statement that this error would NOT occur?

Thank you...
Rick

Microsoft VBScript runtime error '800a000d'
Type mismatch: 'FormatCurrency'
/Copy22Index.asp

This Works:
SELECT SUM(ExtPrice)
FROM OpenOrdersFinal
WHERE ShipDate<Date()

mbizup

From what I can tell, the error is not within the query itself, but in the context of how the results (or lack thereof) are being used.

You'll have to include some kind of handling for "no data" in whatever code is using the query.

Can you post the code that makes use of the query?

ES-Components

ASKER

I am not a programmer.
I am using an Access 2007 Table that has two columns, ShipDate and Sales$.

This Works:
SELECT SUM(ExtPrice)
FROM OpenOrdersFinal
WHERE ShipDate<Date()

This select statement works as long as there is a Shipdate in the column less than the current day. If it finds this it then totals the second column(extPrice).

I think I need some sort of IF statement that says IF there is NO shipdate less than today
leave the ExtPrice column Blank or just fill in with Zero, otherwise Sum the (ExtPrice).

Right now because there is No Data (No SHipDate Less than Today) it is giving us that error message.
Need something like (IF ShipDate=" ",ExtPrice="0", Else SUM(ExtPrice).
I don't know how to write the statement.

Any ideas would greatly be appreciated.

Thank you
Rick

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial