Solved

Cdate in query

Posted on 2008-06-25
2
1,946 Views
Last Modified: 2008-06-25
I amusing the function Cdate in my query and continue to get #Error for some records that cannot compute a date even if I use IsError function.  

MatDate: IIf(IsError(CDate(Mid([Maturity_Date],5,2) & "/" & Right([Maturity_Date],2) & "/" & Left([Maturity_Date],4))),0,1)

To make this simpler, I just want it to result in 0 or 1.  0 should display if there is an error, and 1 if not.  However, I only get 1s and a #Error instead of 0.  What can I do?
0
Comment
Question by:mak345
  • 2
2 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 125 total points
ID: 21866433
A better idea would be to write this as a VBA function, which can handle graceful error handling and if-thens in a somewhat readable way, then call that function from your query:

Your query:
MatDate: MyFunction(Maturity_Date)

The function:
Public Function MyFunction(sString as String) as string

On Error Goto eh

If IsNull(Nz(sString, "") = "" then
   'sString is either null or empty.  Bail here.
   MyFunction = 0
   goto ex
End If

If IsDate(Mid([Maturity_Date],5,2) & "/" & Right([Maturity_Date],2) & "/" & Left([Maturity_Date],4)) then
   'Rock and roll.
   MyFunction = 1
   goto ex
Else
   'sString is a value, but can not be formatted as a date correctly.
   MyFunction = 0
   goto ex
end if

ex:
  exit function

er:
  MyFunction = 0
  msgbox "An error occured in MyFunction: " & err.Number & ", " & err.Description
  goto ex

End Function


0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 21866441
btw I recommend against using IsError() in query expressions.  Just too many possibilities, no graceful way of dealing with the error, and bad data can flow downstream.
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question