Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Cdate in query

Posted on 2008-06-25
2
Medium Priority
?
1,955 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 66

Accepted Solution

by:
Jim Horn earned 500 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 66

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

580 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