[RESOLVED] Pass Empty Date value

[aikidokid]

How do I pass an empty value, that is a Date type variant, to a SQL statement?

The user adds dates to textboxes, which are then validated, and if then ok, added to an update statement (strSQL)

The dates don't have to be filled in and I am getting the error Data Type Mismatch because I am passing "" for the date.

How would I pass an empty date to the SQL?

Thanks

[si_the_geek]

To put null values into an SQL statement, simply use the keyword Null (without any kind of quotes around it).

[GaryMazzone]

Try looking here:

http://www.vbforums.com/showthread.p...79#post2881879

[aikidokid]

Thanks Guys,

Adding the date to a string I have seen before, maybe from you Gary
How would this be handled as the field type is Date/Time?

Is this going to cause me an error there?

[GaryMazzone]

No you check if the data in the textfield is a valid date then change the var from NULL to the textbox value sourrounded by single qoutes.

[si_the_geek]

My explanation, and Gary's example (which only sets the appropriate quotes when there is a value), are suitable for all data types.

[aikidokid]

Not quite there yet!

I have changed the dates to be added as follows:

    'firstly give dates Null Value
    For i = 0 To 3
        dtDate(i) = "Null"
    Next i
    
    If txtMembership(11).Text <> "" Then dtDate(0) = "#" & CDate(txtMembership(11).Text) & "#"
    If txtMembership(12).Text <> "" Then dtDate(1) = "#" & CDate(txtMembership(12).Text) & "#"
    If txtMembership(13).Text <> "" Then dtDate(2) = "#" & CDate(txtMembership(13).Text) & "#"
    If txtMembership(15).Text <> "" Then dtDate(3) = "#" & CDate(txtMembership(15).Text) & "#"

And in the strSQL this part is as follows:

        "DOB = '" & dtDate(0) & "', " & _
        "Date_Joined = '" & dtDate(1) & "', " & _
        "Insurance_Due = '" & dtDate(2) & "', " & _
        "Grade = '" & Replace(txtMembership(14).Text & "", "'", "''") & "', " & _
        "Last_Graded = '" & dtDate(3) & "', " & _

In this particular instance the var dtDate(3) is Null.

But I am still getting the error;
Data type mismatch in criteria expression

If I print out the strSQL I get the following:

debug.Print strsql
UPDATE tbl_Membership SET First_Name = 'Brian', Middle_Name = 'Aurthur', Surname = 'Smith', Street = '22 Anystreet', Town = 'Anytown', City = 'Anycity', County = 'Anycounty', Post_Code = 'BG68UY', Home_Tel = '01779999999', Mobile_Tel = '07968888999', Email = '', DOB = '#22/05/1984#', Date_Joined = '#07/06/2007#', Insurance_Due = '#07/06/2008#', Grade = 'White', Last_Graded = 'Null', Membership_Number = 'M5678', Occupation = 'Painter', Notes = '' Where Memb_ID = 47

I think all of the ' and # are correct, but maybe not as I am getting the error!

[aikidokid]

Got it.

I didn't remove the Apostrophe around the dtDate(0) variables.

I think this is resolved now.

Thanks