Data Type mismatch in criteria expression

[gwboolean]

I am using 5 textbox controls for use with 5 corresponding date fields from my table. My issue is that if any of the 5 text boxes are left empty (i.e. Null) and I update the table, I receive the following message:



It was my thought that I could go to the database and change the properties for these fields to accept/deal with null data. While that might be the correct place to mitigate the issue, I was unable to do that.



The table is updated using AddParams(), followed by UpdateRecord() and the code:
(I don't have my glasses at the moment and am unable to see the Insert Code button)

Private Sub AddParams()
'ChangeMaster parameters
MasterBase.AddParam("@recno", lblChangeID.Text)
MasterBase.AddParam("@name", txtName.Text)
MasterBase.AddParam("@managerid", EmployeeID.ToString)
MasterBase.AddParam("@manager", cboManager.Text)
MasterBase.AddParam("@type", cboType.Text)
MasterBase.AddParam("@owner", cboOwner.Text)
MasterBase.AddParam("@where", cboWhere.Text)
MasterBase.AddParam("@made", txtMade.Text)
MasterBase.AddParam("@reason", txtReason.Text)
MasterBase.AddParam("@result", txtResult.Text)
MasterBase.AddParam("@path", lblLink.Text)
MasterBase.AddParam("@open", txtOpen.Text)
MasterBase.AddParam("@submit", txtSubmit.Text)
MasterBase.AddParam("@approve", txtApprove.Text)


MasterBase.AddParam("@train", txtTrain.Text)
MasterBase.AddParam("@effective", txtEffective.Text)
MasterBase.AddParam("@active", chkActive.Checked)
MasterBase.AddParam("@obsolete", chkObsolete.Checked)
End Sub

The following code updates the table.

Private Sub UpdateRecord()
#Region "Establish Connection and execute query"
MasterBase.ChangeMasterQuery("UPDATE sitChangeMaster " &
"SET ChangeID=@recno,ChangeName=@name,ChangeManagerID=@ managerid, " &
"ChangeManager=@manager,ChangeType=@type,ChangeOwn er=@owner, " &
"WhereUsed=@where,ChangeMade=@made,ChangeReason=@r eason,ChangeResult=@result, " &
"filePath=@path,DateOpen=@open,DateSubmit=@submit, DateApprove=@approve, " &
"DateTrain=@train,DateEffective=@effective,Active= @active,Obsolete=@obsolete " &
"WHERE ChangeID=@recno")
#End Region
If NoErrors(True) = False OrElse RecordCount < 1 Then Exit Sub
RefreshForm()
End Sub




So, My question is exactly how, and how many ways, are there to be able to make my date fields deal with null values?

[June7]

What are you coding this in? Doesn't look like VBA.

[ranman256]

look for a number getting assigned a string.

and you really shouldn't need to do all this coding. There are simple ways.

[gwboolean]

I am using parameters. I have found them to have some beneficial use. The parameters are all Objects, so I am pretty sure they should be handle strings/etc. It is Visual Basic and not VBA.

Anyway, I have gone through this enough times (several hours of line by line checking) to be pretty confident that the issue has nothing to do with the parameters and code and everything to do with the date fields not having the ability to handle an incoming Null Value.

I had thought that one could just go back to the database and change the properties for the date fields to be able to accept Null values. However, I either did that wrong or it is not doable there. I am aware that this can be coded, but I have no idea what form that would take.

Any ideas how that can be done in the field properties at the database? I would prefer to work there than to have to code this out.

[Micron]

Can you not replace date variables with a valid date to see if the code will run? In any event I would remove the validation text property seeing as how you seem to want to be able to allow null in your date fields. I have to agree with Ranman; looks overly complicated for table updating but maybe that's because I'm not familiar with using VB to update Access tables.

[ssanfu]

So, My question is exactly how, and how many ways, are there to be able to make my date fields deal with null values?

Since you are building a query in code, maybe you could check for NULL before adding the parameter to the string?
Allen Browne has a function to convert Intl dates to Americans format:
International Dates in Access

Or maybe, since you are building the query in code, do something like this:

Private Sub AddParams()
    'ChangeMaster parameters
    MasterBase.AddParam("@recno", lblChangeID.Text)
    MasterBase.AddParam("@name", txtName.Text)
    MasterBase.AddParam("@managerid", EmployeeID.ToString)
    MasterBase.AddParam("@manager", cboManager.Text)
    MasterBase.AddParam("@type", cboType.Text)
    MasterBase.AddParam("@owner", cboOwner.Text)
    MasterBase.AddParam("@where", cboWhere.Text)
    MasterBase.AddParam("@made", txtMade.Text)
    MasterBase.AddParam("@reason", txtReason.Text)
    MasterBase.AddParam("@result", txtResult.Text)
    MasterBase.AddParam("@path", lblLink.Text)
    '---Date params---
    MasterBase.AddParam("@open", txtOpen.Text)
    MasterBase.AddParam("@submit", txtSubmit.Text)
    MasterBase.AddParam("@approve", txtApprove.Text)
    MasterBase.AddParam("@train", txtTrain.Text)
    MasterBase.AddParam("@effective", txtEffective.Text)
    '---Date params---
    MasterBase.AddParam("@active", chkActive.Checked)
    MasterBase.AddParam("@obsolete", chkObsolete.Checked)
End Sub

    'The following code updates the table.

Private Sub UpdateRecord()
    Dim stQryUPDATE As String

    stQryUPDATE = "UPDATE sitChangeMaster SET"

    If Not IsNull(@recno) Then
        stQryUPDATE = stQryUPDATE & " ChangeID=@recno,"
    End If
    If Not IsNull(@name) Then
        stQryUPDATE = stQryUPDATE & " ChangeName=@name,"
    End If
    If Not IsNull(@managerid) Then
        tQryUPDATE = stQryUPDATE & " ChangeManagerID=@managerid,"
    End If
    If Not IsNull(@manager) Then
        tQryUPDATE = stQryUPDATE & " ChangeManager=@manager,"
    End If
    If Not IsNull(@type) Then
        tQryUPDATE = stQryUPDATE & " ChangeType=@type,"
    End If
    If Not IsNull(@owner) Then
        tQryUPDATE = stQryUPDATE & " ChangeOwn er=@owner,"
    End If
    If Not IsNull(@where) Then
        tQryUPDATE = stQryUPDATE & " WhereUsed=@where,"
    End If
    If Not IsNull(@made) Then
        tQryUPDATE = stQryUPDATE & " ChangeMade=@made,"
    End If
    If Not IsNull(@reason) Then
        tQryUPDATE = stQryUPDATE & " ChangeReason=@reason,"
    End If
    If Not IsNull(@result) Then
        tQryUPDATE = stQryUPDATE & " ChangeResult=@result,"
    End If
    If Not IsNull(@path) Then
        tQryUPDATE = stQryUPDATE & " filePath=@path,"
    end If
    If Not IsNull(@open) Then  '<<--Date
        tQryUPDATE = stQryUPDATE & " DateOpen=@open,"
    end If
    If Not IsNull(@submit) Then  '<<--Date
        tQryUPDATE = stQryUPDATE & " DateSubmit=@submit,"
    end If
    If Not IsNull(@approve) Then  '<<--Date
        tQryUPDATE = stQryUPDATE & " DateApprove=@approve,"
    end If
    If Not IsNull(@train) Then  '<<--Date
        tQryUPDATE = stQryUPDATE & " DateTrain=@train,"
    end If
    If Not IsNull(@effective) Then  '<<--Date
        tQryUPDATE = stQryUPDATE & " DateEffective=@effective,"
    end If
    If Not IsNull(@active) Then
        tQryUPDATE = stQryUPDATE & " Active= @active,"
    end If
    If Not IsNull(@obsolete) Then
        tQryUPDATE = stQryUPDATE & " Obsolete=@obsolete," 
    end If
    If Not IsNull(@recno) Then
        tQryUPDATE = stQryUPDATE & " WHERE ChangeID=@recno,")
    end If
    
    REM remove trailing comma
    If len(tQryUPDATE) > 3 Then
        tQryUPDATE = Left(Len(tQryUPDATE),Len(tQryUPDATE)-1)
    #Region "Establish Connection and execute query"
        MasterBase.ChangeMasterQuery(stQryUPDATE)
    #End Region
    If NoErrors(True) = False OrElse RecordCount < 1 Then Exit Sub
    RefreshForm()
End Sub

This is just an example in the hopes of getting you moving forward.


Good luck with your project............

[gwboolean]

ssnafu. I will give that a try. Interestingly, it looks almost identical to the code I use to auto write an SQL query. I have also considered just having a function that is run for the value of the AddParam(name, value).

What I am really getting a little irked about is that I am unable to just open the database, go to the designer and set a property for a date field that will allow it to accept Nulls. But NOooooooo. I have to come up with some kind of Rube Goldberg code method to get the job done.

[June7]

The problem isn't with date/time field accepting Null. Date/time fields can be left Null via manual data entry. It's constructing SQL statement to pass either a value or Null that is the issue. This SQL works just fine: UPDATE table SET datefield = Null. This one does not: UPDATE table SET datefield = #Null#. If SQL is constructed to expect a string or date value and gets Null instead, it fails or if explicitly declared variable tries to receive Null, code fails. In VBA, only Variant type variable can hold Null. Is that the case with your VB variables - are they specifically text or date type?

So, "UPDATE sitChangeMaster SET DateEffective = " & IIf(IsNull(textbox), "Null", "@effective"). Or for longer statements, build per ssanfu code.

An alternative may be to open a recordset object and edit each field - no SQL injection risk and no parameters needed and no IIf(), simply: rs!fieldname = controlname. If editing only one record, this should be just as efficient as an action SQL.

[John_G]

Look at the error message - it says there is an error in in a criteria expression. To me that indicates it doesn't like a where clause somewhere. What are the data types of ChangeID and @recno? If ChangeID is a string (even though it may LOOK like a number), and @Recno is a number, then that might be the error. Nulls in dates won't (or shouldn't) give you that error - in my experience a Null where there should not be one results in a syntax error message.

Some additional thoughts... Are you sure it is the code you posted that is causing the error? After entering the data on your form, how do you initiate the database update? If it is with a button click, is there any code that runs before it reaches the code you show us? Is there, for example, code which validates the dates, and which might have trouble with a null value in any of them?

[gwboolean]

June7

I see what you are talking about. Your way of passing it looks a lot better than what I was thinking about doing. Sometimes I can get very, every convoluted. But I am still irritated that the database design does not have a property that deals with that. But on the other hand, the database is free. The databases I can use that does have that property ain't free.

John

That is correct. The error occurs in the Update query when the parameter is passed to the field. The field has be configured to accept/ignore the null value. There is no property that does this in the database. So it appears that I am going to have to not pass a value if it is null, for all of the five date fields.

[June7]

Any text/memo/date/number field will naturally accept Null by default when it is created unless you set it as Required. It's the SQL statement that can't handle Null if it is expecting an actual value. The IIf() expression I suggested allows Null to be passed. If a field is set as Required, then Null will not be accepted no matter how the SQL is built.

[John_G]

I still think we and you are missing something. If you attempt to enter a Null into a table field that does not accept Null's, that is not the error message you will get. You will get an error stating "You must enter a value in xxxx field" (or similar). The error of data type mismatch (IMO) has to be due to something else.

I see references to masterbase.AddParams and Masterbase.ChangeMasterQuery - what are those - procedures in the database? If they are coded procedures (i.e. not built-in) could something in that code be cauing the problem?