Cdate Error Handling
Contents |
Forums Excel Questions Cdate error when operating on blank string Results 1 to 7 of 7 Cdate error when operating on blank stringThis is a discussion on Cdate error when operating on blank string within the Excel
Cdate Type Mismatch Vba
Questions forums, part of the Question Forums category; Hi, this is the first time I type mismatch cdate vbscript have posted on this wonderful source of info, so be gentle with me. ... LinkBack LinkBack URL About LinkBacks Bookmark & cdate format Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Dec 1st, 2005,10:42 AM #1 bcfaigg Board Regular Join Date
Cdate Function
Dec 2005 Posts 71 Cdate error when operating on blank string Hi, this is the first time I have posted on this wonderful source of info, so be gentle with me. I am designing a form based tool which involves entering quite a lot of data on to a workbook, using vba forms. I am using lots of expressions like worksheets("Milestones").cells(SelectionRow, 21).value=cdate(txtDeadline.text) I am also using an error handling procedure to ensure that
Cdate Vba
only dates are entered, like: on error goto DateError ... unload me goto finish: DateError: msgbox("One of the values entered is not a valid date. Please check and try again.") finish: This works perfectly except for the fact that if a text box is left blank on the form, you get the same error. I.e. Cdate operating on a blank string gives the same error. The obvious (in my eyes) solution is wherever I use Cdate to create a function e.g. ConvertDate as follows: function ConvertDate(date_to_convert) if date_to_convert="" then goto continue convertdate=cdate(date_to_convert) continue: end function Can anyone think of a more elegant/better solution to my dilemma. I need some sort of validation on the dates entered via the form, but need the ability for text boxes to be left blank at times too. Thanks Gopes P.S. Apologies for poor formatting - cannot download programs such as VB HTML Maker at work Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Dec 1st, 2005,10:48 AM #2 Norie Board Regular Join Date Apr 2004 Location Stirling Posts 69,882 You can use the IsDate function to test for dates. Is that what you're after? Share Share this post on Digg Del.icio.us Technorati Twitter If posting code please use code tags. Reply With Quote Dec 1st, 2005,10:58 AM #3 bcfai
Community Links Social Groups Pictures & Albums Members List Calendar Search Forums Show Threads Show Posts Tag Search Advanced Search Find All Thanked Posts Go to Page... Thread Tools Rating: Display Modes 04-23-2010, 08:12 AM #1 BrokenBiker ManicMechanic Join Date: on error resume next Mar 2006 Location: Not where I should... Posts: 128 Thanks: 0 Thanked 0 Times in 0 Posts
Cdate Access
Null Date Value and String vs Date and #Error I have a query w/a calculated filed (TngDue). Some of those records have no date. When dateserial I use CDate to convert the TngDue field to a date format, the records w/o date entries give me an #Error. I've tried to use IIf and IsNull to get rid of the #Errors and still maintain a date format, but am http://www.mrexcel.com/forum/excel-questions/175775-cdate-error-when-operating-blank-string.html so far unsuccessful. Code: TngDueDate: IIf(IsNull([TngDue]),"",CDate([TngDue])) It seems that if I try to handle the null values to get rid of the #Errors, I have to live w/a text string. If I want a date value, I'll have to live w/the #Errors. Is there any way to handle null values w/a date field and still have the result as a date as opposed to a string? BrokenBiker View Public Profile Find More Posts by BrokenBiker 04-23-2010, 03:36 PM #2 http://www.access-programmers.co.uk/forums/showthread.php?t=191989 Galaxiom Super Moderator Join Date: Jan 2009 Location: NSW Australia Posts: 10,238 Thanks: 51 Thanked 1,170 Times in 1,107 Posts Re: Null Date Value and String vs Date and #Error The Null string is not valid in a date field. TngDueDate: IIf(IsNull([TngDue]),Null,CDate([TngDue])) Galaxiom View Public Profile Find More Posts by Galaxiom 04-23-2010, 06:47 PM #3 BrokenBiker ManicMechanic Join Date: Mar 2006 Location: Not where I should... Posts: 128 Thanks: 0 Thanked 0 Times in 0 Posts Re: Null Date Value and String vs Date and #Error Thanks for the reply, but that gave me the same #Error. Is there a way to use the IsDate fx? Possibly If Not (IsDate(TngDue)...etc....? I'll give it a shot and see what it does. BrokenBiker View Public Profile Find More Posts by BrokenBiker 04-24-2010, 05:18 AM #4 BrokenBiker ManicMechanic Join Date: Mar 2006 Location: Not where I should... Posts: 128 Thanks: 0 Thanked 0 Times in 0 Posts Re: Null Date Value and String vs Date and #Error Yep, that did it! TngDueDate: IIf(Not IsDate([TngDue]),Null,CDate([TngDue])) BrokenBiker View Public Profile Find More Posts by BrokenBiker 04-25-2010, 02:31 AM #5 Galaxiom Super Moderator Join Date: Jan 2009 Location: NSW Australia Posts: 10,238 Thanks: 51 Thanked 1,170 Times in 1,107 Posts Re: Null Date Value and String vs Date and #Error Would seem that the apparently Null values were actually the Null String.
with cDate() The SitePoint Forums have moved. You can now find them here. This forum is http://www.sitepoint.com/forums/showthread.php?79398-Try-Catch-with-cDate() now closed to new posts, but you can browse existing content. You can find out more information about the move and how to open a new account (if necessary) here. If you get stuck you can get support by emailing forums@sitepoint.com If this is your first visit, be sure to check out the FAQ type mismatch by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. SitePoint Sponsor User Tag List Results 1 to 16 of 16 Thread: Try Catch with cDate() Thread Tools cdate error handling Show Printable Version Subscribe to this Thread… Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Oct 7, 2002,11:33 #1 Brandon Luhring View Profile View Forum Posts SitePoint Evangelist Join Date Apr 2002 Location IN, USA Posts 455 Mentioned 0 Post(s) Tagged 0 Thread(s) Try Catch with cDate() I've got a Try...Catch statement used to make sure that user input is actually a date. Unfortunately, it is not catching the error, instead it gives me the error Cast from string "111" to type 'Date' is not valid. Here's my code with the error line in red: Code: Imports System Imports System.Web Imports System.Web.UI Imports System.Web.UI.WebControls Imports System.Web.UI.HtmlControls Imports System.Drawing.Color Imports System.Collections Imports System.Collections.Specialized Public Class DefaultCode Inherits Page '********************************************' 'Allow communication with front-end Page Controls' '********************************************' Protected ErrorMessage As Label Protected WithEvents exactDate As TextBox Protected WithEvents startDate As TextBox Protected WithEvents endDate As TextBox Protected WithEvents Submit As Button '********************************************' ' Check for Errors and Report ' '***********