Results 1 to 5 of 5

Thread: Data type mismatch in criteria expression error when date/time field is empty...

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    274

    Data type mismatch in criteria expression error when date/time field is empty...

    How to avoid this error even if the user doesn't enter anything in date/time field "txtTRDT.Text" (here" TRDT" is Transaction Date) ?

    Code:
    CmdTxt = "Insert into tblEMPLOYEES([EID],[ENM],[EML],[TRDT])" & _
                   "Values('" & txtEID.Text & "', '" & txtENM.Text & "','" & txtEML.Text & "','" & txttTRDT.Text & "') "

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,676

    Re: Data type mismatch in criteria expression error when date/time field is empty...

    If the field is a DateTime field, then use a DateTimePicker rather than a TextBox and return it's value while calling it's ToString method. This will ensure that a value will always be returned.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    274

    Re: Data type mismatch in criteria expression error when date/time field is empty...

    I have datetimepicker as well and by clicking on it getting the date in the text field. Actually, I have several date/time fields on my form to save all ID Card numbers and its Expiry date as well. In case a user doesn't have one of card which is on the form so it is to be left blank. In that case, I need to resolve the error.

  4. #4
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,676

    Re: Data type mismatch in criteria expression error when date/time field is empty...

    Either way you should not be loading in an empty string. If the user is not loading the date/time field into the TextBox(which seems redundant to me), then you'll receive this error. Another option that you have is to use the If Operator to return a default date:
    Code:
    CmdTxt = "Insert into tblEMPLOYEES([EID],[ENM],[EML],[TRDT])" & _
                   "Values('" & txtEID.Text & "', '" & txtENM.Text & "','" & txtEML.Text & "','" & If(String.IsNullOrWhiteSpace(txttTRDT.Text), DateTime.Now.ToString, txttTRDT.Text) & "') "

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,951

    Re: Data type mismatch in criteria expression error when date/time field is empty...

    1) Use a parameterized query
    2) USe .Parameters.Add to add each parameter, be sure to include the type
    3) Then set the .Value of each parameter
    4) when setting hte date, if there is one, use it... if there isn't set it to DBNull
    5) .ExecuteNonQuery the command
    6) Pass GO and collect $200

    Of course this will only work if the field is set to allow nulls in the first place.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width