Hi

I'm trying to update a table via an SQL statement. The SQL statement consists of sevral variables from a form. When I fill all the text boxes in and therefore populate all the variables the table update works fine. However when I leave one or more fields blank, the table does not update. Anyone any ideas?

Cheers
Tony

__________________
Cheers
Tony

Do something like this since your query is not passing nulls.

You need to check EACH text box or combo box etc that is passing data to your update query for Null. If it is null, put in some value (I put "Null"), and if it is not null, then pass the value.

IOW, you need to check each value before you pass it to a variable which you pass to the query string, and not pass the value directly to the query string without checking or without a variable.

'=====
Dim stText1 As String
'...

If IsNull(Me.txtMyTextBox) Then
   stText1 = "Null"
   Else
   stText1 = Me.txtMyTextBox
End If
'...
'=====

HTH


mmcdonal

Ooops, then pass stText1 to the UPDATE query string as a field value.

mmcdonal

Actually I wouldn't do what mmcdonal suggests.

"Null" is NOT the same as Null. Why put values in your table if you don't have data for them?

I would suggest that you check your table constraints to see if the fields that you are not entering require a value. If so, then you should put edits on the form to force entry.

I do see where mmcdonal's solution would work.

If you are building the SQL string based on the field values using something like:

sql = sql & "Field1 = " & me.field1

This will make a "valid" SQL statement where you didn't have one without change the value of the field to "Null".

However, if you're using

sql = sql & "Field1 = """ & me.field1 & """"

as you should be doing to make sure you build a valid SQL string if the value of me.field1 has a space in it, your table will store the word "Null" if the field is not entered on the form.

In short, since you're building the SQL statement yourself, make sure it has valid SQL syntax.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

P.S. Actually, on further review I notice that I would do what mmcdonal suggests. But there is a way that doesn't require using an extra variable which I describe in a later post. Also, mmcdonal's way doesn't cover building a valid SQL if your field has a space or special character in it. Sorry mmcdonal.

Each field you add to your SQL statement should have something like this:

sql = sql & iif(isnull(me.field1),"Null","""" & me.field1 & """") ' for text fields

sql = sql & iif(isnull(me.field1),"Null","#" & me.field1 & "#") ' for date fields

sql = sql & iif(isnull(me.field1),"Null",me.field1) ' for numeric fields

or you could do it the long way...

If isnull(me.field1) then ' a text field
    sql = sql & "Null"
else
    sql = sql & """" & me.field1 & """"
endif

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

I said do something LIKE this, and "I put "Null" just to demonstrate a place holder. The upshot is that the UPDATE is invalid if table validation is not adhered to.

mmcdonal

Thanks a lot chaps.....that should do the trick

Cheers
Tony