nulls in fields

[Guest]

Hi,

I use an update query with values given from a form to update a group of
records in a table. The code is like:
UPDATE myTable SET [Field1]=[Forms]![Form1]![txt1] WHERE
[Field2]=[Forms]![Form1]![txt2] AND [Field3]=[Forms]![Form1]![txt3]
The table may include records that have null value in a field so let’s say
if txt3 is left blank in the form the goal is the query to update records
with null value in Field3 and Field2 values as the txt2 value in Form1, but
this doesn’t work.
Do you know a way to do it?

Thank you
GL

 

[Allen Browne]

Create the query statement dynamically from the values in the form.

This kind of thing:

Dim strSql As String
Dim strWhere As String
Dim lngLen As Long
strSql = "UPDATE myTable SET [Field1] = " & IIf(Me.txt1, "Null", Me.txt1)
If not IsNull(Me.txt2) Then
strWhere = "(Field2 = " & Me.txt2 & ") AND "
End If
If not IsNull(Me.txt3) Then
strWhere = "(Field3 = " & Me.txt3 & ") AND "
End If
'etc
lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen > 0 Then
strSql = strSql & " WHERE " & Left$(strWhere, lngLen)
End If
dbEngine(0)(0).Execute strSql, dbFailOnError

Note that strWhere will need different delimiters depending on the field
types: # around dates, and " for text fields.

You can download an example of building this kind of where string
dynamically here:
http://allenbrowne.com/unlinked/Search2000.zip
The example works with Access 2000 and later. It is actually a search form,
but demonstrates the technique for different data types.

 

[Guest]

Actually I want to do the opposite of what is given in your example.
I like to use as criterion an empty (null) textbox to filter the records
with null in a field.
To be more explanatory if I have a textbox to give the criterion for the
“Profession†field in a table of persons, then typing Doctor in the textbox I
set a filter for the persons who are doctors, but leaving the textbox empty I
set a filter for the persons, hence records, whose the Profession field is
left blank.

Create the query statement dynamically from the values in the form.

This kind of thing:

Dim strSql As String
Dim strWhere As String
Dim lngLen As Long
strSql = "UPDATE myTable SET [Field1] = " & IIf(Me.txt1, "Null", Me.txt1)
If not IsNull(Me.txt2) Then
strWhere = "(Field2 = " & Me.txt2 & ") AND "
End If
If not IsNull(Me.txt3) Then
strWhere = "(Field3 = " & Me.txt3 & ") AND "
End If
'etc
lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen > 0 Then
strSql = strSql & " WHERE " & Left$(strWhere, lngLen)
End If
dbEngine(0)(0).Execute strSql, dbFailOnError

Note that strWhere will need different delimiters depending on the field
types: # around dates, and " for text fields.

You can download an example of building this kind of where string
dynamically here:
http://allenbrowne.com/unlinked/Search2000.zip
The example works with Access 2000 and later. It is actually a search form,
but demonstrates the technique for different data types.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I use an update query with values given from a form to update a group of
records in a table. The code is like:
UPDATE myTable SET [Field1]=[Forms]![Form1]![txt1] WHERE
[Field2]=[Forms]![Form1]![txt2] AND [Field3]=[Forms]![Form1]![txt3]
The table may include records that have null value in a field so let's say
if txt3 is left blank in the form the goal is the query to update records
with null value in Field3 and Field2 values as the txt2 value in Form1,
but
this doesn't work.
Do you know a way to do it?

Thank you
GL

 

[Allen Browne]

If the text box is left null, you want to use Is Null as the criteria of the
field?

If IsNull(Me.txt2) Then
strWhere = strWhere & "(Field2 Is Null) AND "
Else
strWhere = strWhere & "(Field2 = " & Me.txt2 & ") AND "
End If
'etc.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Actually I want to do the opposite of what is given in your example.
I like to use as criterion an empty (null) textbox to filter the records
with null in a field.
To be more explanatory if I have a textbox to give the criterion for the
"Profession" field in a table of persons, then typing Doctor in the
textbox I
set a filter for the persons who are doctors, but leaving the textbox
empty I
set a filter for the persons, hence records, whose the Profession field is
left blank.

Create the query statement dynamically from the values in the form.

This kind of thing:

Dim strSql As String
Dim strWhere As String
Dim lngLen As Long
strSql = "UPDATE myTable SET [Field1] = " & IIf(Me.txt1, "Null", Me.txt1)
If not IsNull(Me.txt2) Then
strWhere = "(Field2 = " & Me.txt2 & ") AND "
End If
If not IsNull(Me.txt3) Then
strWhere = "(Field3 = " & Me.txt3 & ") AND "
End If
'etc
lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen > 0 Then
strSql = strSql & " WHERE " & Left$(strWhere, lngLen)
End If
dbEngine(0)(0).Execute strSql, dbFailOnError

Note that strWhere will need different delimiters depending on the field
types: # around dates, and " for text fields.

You can download an example of building this kind of where string
dynamically here:
http://allenbrowne.com/unlinked/Search2000.zip
The example works with Access 2000 and later. It is actually a search
form,
but demonstrates the technique for different data types.

Hi,

I use an update query with values given from a form to update a group
of
records in a table. The code is like:
UPDATE myTable SET [Field1]=[Forms]![Form1]![txt1] WHERE
[Field2]=[Forms]![Form1]![txt2] AND [Field3]=[Forms]![Form1]![txt3]
The table may include records that have null value in a field so let's
say
if txt3 is left blank in the form the goal is the query to update
records
with null value in Field3 and Field2 values as the txt2 value in Form1,
but this doesn't work.
Do you know a way to do it?

 

[Guest]

Thanks a lot
GL

If the text box is left null, you want to use Is Null as the criteria of the
field?

If IsNull(Me.txt2) Then
strWhere = strWhere & "(Field2 Is Null) AND "
Else
strWhere = strWhere & "(Field2 = " & Me.txt2 & ") AND "
End If
'etc.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Actually I want to do the opposite of what is given in your example.
I like to use as criterion an empty (null) textbox to filter the records
with null in a field.
To be more explanatory if I have a textbox to give the criterion for the
"Profession" field in a table of persons, then typing Doctor in the
textbox I
set a filter for the persons who are doctors, but leaving the textbox
empty I
set a filter for the persons, hence records, whose the Profession field is
left blank.

Create the query statement dynamically from the values in the form.

This kind of thing:

Dim strSql As String
Dim strWhere As String
Dim lngLen As Long
strSql = "UPDATE myTable SET [Field1] = " & IIf(Me.txt1, "Null", Me.txt1)
If not IsNull(Me.txt2) Then
strWhere = "(Field2 = " & Me.txt2 & ") AND "
End If
If not IsNull(Me.txt3) Then
strWhere = "(Field3 = " & Me.txt3 & ") AND "
End If
'etc
lngLen = Len(strWhere) - 5 'Without trailing " AND "
If lngLen > 0 Then
strSql = strSql & " WHERE " & Left$(strWhere, lngLen)
End If
dbEngine(0)(0).Execute strSql, dbFailOnError

Note that strWhere will need different delimiters depending on the field
types: # around dates, and " for text fields.

You can download an example of building this kind of where string
dynamically here:
http://allenbrowne.com/unlinked/Search2000.zip
The example works with Access 2000 and later. It is actually a search
form,
but demonstrates the technique for different data types.

Hi,

I use an update query with values given from a form to update a group
of
records in a table. The code is like:
UPDATE myTable SET [Field1]=[Forms]![Form1]![txt1] WHERE
[Field2]=[Forms]![Form1]![txt2] AND [Field3]=[Forms]![Form1]![txt3]
The table may include records that have null value in a field so let's
say
if txt3 is left blank in the form the goal is the query to update
records
with null value in Field3 and Field2 values as the txt2 value in Form1,
but this doesn't work.
Do you know a way to do it?