Solved

Cdate in query

Posted on 2008-06-25
2
1,948 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem with MySQL query - graph 3 27
C# DataTable (in memory) vs SQL Lookups 4 37
Options for Linking SQL tables to Access 2013 9 43
vb.net dbnull syntax 1 9
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …

730 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