• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

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

Microsoft VBScript runtime error '800a000d'
Type mismatch: 'FormatCurrency'

This Works:
FROM OpenOrdersFinal
WHERE ShipDate<Date()
1 Solution
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-ComponentsAuthor Commented:
I am not a programmer.
I am using an Access 2007 Table that has two columns, ShipDate and Sales$.

This Works:
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
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"
    '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"

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now