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()
ES-ComponentsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
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?
0
ES-ComponentsAuthor Commented:
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
0
Jeffrey CoachmanMIS LiasonCommented:
The query, (if executed alone), will not return any records, so it seems your vb app is triggering the "error"...
...
IF there is NO shipdate less than today
leave the ExtPrice column Blank or just fill in with Zero, otherwise Sum the (ExtPrice)
To me, if there were no dates returned, I would not want to see a "Blank" record, I would just rather get a "No data found" message. (and the query would not even run if no data was found)
(for example: "No Ship dates are before the current date", ...or something similar)

It is not clear to me how your vb app is interacting with your data, (how you are triggering this query to run, error handling, other Access objects available, ...ect)
...but from a far away perspective, you could just check to see if any dates exist before running your query.

Code might be something like this (on a button on a form):

If DCount("ShipDate", "OpenOrdersFinal", "ShipDate<" & "#" & Date & "#") = 0 Then
    MsgBox "No Orders before today"
Else
    'Code to run your query here. ex.: Docmd.OpenQuery "YourQueryNameHere")
End If

If you have Access Reports available, ...they contain a "No Data" event that you can catch this with.
1. Make the query the source for the report.
2. On the "No Data" event put code like this:
    msgbox "No Orders before today, ...cancelling report"
    cancel=true

Here no changes are needed to the query
You simply add a button to a form and open the report:
Docmd.OpenReport "YourReportNameHere", acViewPreview


Let's see what mbizup or other experts post...

JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.