Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Cdate in query

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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

885 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