Values not equal but not returning in Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following in my database

SELECT tblWO.wono, tblWO.newequipno, tblCosts.strEquipNo FROM tblCosts INNER
JOIN tblWO ON tblCosts.strWONO = tblWO.wono WHERE (((tblWO.newequipno) <>
[tblCosts.strEquipNo])) ORDER BY tblWO.wono


It works great except that when a value is blank or Null in tblWO.newequipno
and there is a value in tblCosts.strEquipNo it is not pulling these records.

After I retrieve this recordset, I put the value tblWO.newequipno into the
tblCosts.strEquipNo field. That code works too.

The only part that is not working is the <> part. How can I fix that
without fixing the data by doing an update query to change "" to null or are
they the same thing???

Your assistance is greatly appreciated.

Lynn
 
Throw the Nz function in there to give null a value. Access can't compare
null to anything.

something like...

Nz(tblWO.newequino,0) <> Nz(...
 
A Null represents an unknown value, and generally speaking, any expression
involving a Null will evaluate to neither True nor False, but Null. Is one
unknown value equal to another unknown value? The answer is unknown, i.e.,
Null.

Access provides a useful NZ() function that can solve problems like these.
The first argument to the function is the value to be tested for Null, the
second, optional argument, which defaults to 0 for numbers or an empty
string for text, is the value to be substituted for Null values.

WHERE NZ(tblWO.newequipno, 0) <> NZ(tblCosts.strEquipNo, 0)

Alternatively, you could use a more complex WHERE clause to test for Null
values in the two fields ...

WHERE (tblWO.newequipno) <> tblCosts.strEquipNo) OR (tblWO.newequipno IS
NULL OR tblCosts.strEquipNo IS NULL)

While the second example is a little longer and more complex, it uses only
SQL keywords and isn't dependent on any Access functions. It's more portable
should you ever need to use it outside of Microsoft Access.

BTW, an empty string ("") is not the same as Null. A Null is an unknown
value. An empty string is a definite, known value, it is a string containing
no characters. They may look the same, but they behave differently, for
example a test of whether two empty strings are equal will return True,
where a test of two Null values would return Null.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Lynn Arlington said:
I have the following in my database

SELECT tblWO.wono, tblWO.newequipno, tblCosts.strEquipNo FROM tblCosts
INNER
JOIN tblWO ON tblCosts.strWONO = tblWO.wono WHERE (((tblWO.newequipno) <>
[tblCosts.strEquipNo])) ORDER BY tblWO.wono


It works great except that when a value is blank or Null in
tblWO.newequipno
and there is a value in tblCosts.strEquipNo it is not pulling these
records.

After I retrieve this recordset, I put the value tblWO.newequipno into the
tblCosts.strEquipNo field. That code works too.

The only part that is not working is the <> part. How can I fix that
without fixing the data by doing an update query to change "" to null or
are
they the same thing???

Your assistance is greatly appreciated.

Lynn
Click to expand...
 
Thank you, the NZ() function was exactly what I was looking for.

This is a full process, I thought that solving one part might do it but now
I have an additional issue.

The recordset gets pulled including those records with null value, now I
want to update the tblCosts with the Null value. Below is the entire code
behind this step. I will be repeating this step with several different
fields and have found its easier to work one field at a time. (Processing
time and speed are not an issue)

sub updatetable1()

strSQL = "SELECT tblWO.wono, tblWO.newequipno, tblCosts.strEquipNo FROM
tblCosts INNER JOIN tblWO ON tblCosts.strWONo = tblWO.wono WHERE
(((NZ(tblWO.newequipno)) <> NZ([tblCosts.strEquipNo]))) ORDER BY tblWO.wono"


Set objRS = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
While Not objRS.EOF
'Make the changes
strSQL = "UPDATE tblCosts SET strEquipNo='" & objRS("newequipno") &
"' WHERE strWONo ='" & objRS("wono") & "';"

CurrentDb.Execute strSQL

objRS.MoveNext
Wend
objRS.Close
Set objRS = Nothing
end sub

The problem is with the Update statement, it doesn't seem to want to take
the null value and put it in the tblCosts field.

Any ideas?

Thanks again

Lynn


Brendan Reynolds said:
A Null represents an unknown value, and generally speaking, any expression
involving a Null will evaluate to neither True nor False, but Null. Is one
unknown value equal to another unknown value? The answer is unknown, i.e.,
Null.

Access provides a useful NZ() function that can solve problems like these.
The first argument to the function is the value to be tested for Null, the
second, optional argument, which defaults to 0 for numbers or an empty
string for text, is the value to be substituted for Null values.

WHERE NZ(tblWO.newequipno, 0) <> NZ(tblCosts.strEquipNo, 0)

Alternatively, you could use a more complex WHERE clause to test for Null
values in the two fields ...

WHERE (tblWO.newequipno) <> tblCosts.strEquipNo) OR (tblWO.newequipno IS
NULL OR tblCosts.strEquipNo IS NULL)

While the second example is a little longer and more complex, it uses only
SQL keywords and isn't dependent on any Access functions. It's more portable
should you ever need to use it outside of Microsoft Access.

BTW, an empty string ("") is not the same as Null. A Null is an unknown
value. An empty string is a definite, known value, it is a string containing
no characters. They may look the same, but they behave differently, for
example a test of whether two empty strings are equal will return True,
where a test of two Null values would return Null.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Lynn Arlington said:
I have the following in my database

SELECT tblWO.wono, tblWO.newequipno, tblCosts.strEquipNo FROM tblCosts
INNER
JOIN tblWO ON tblCosts.strWONO = tblWO.wono WHERE (((tblWO.newequipno) <>
[tblCosts.strEquipNo])) ORDER BY tblWO.wono


It works great except that when a value is blank or Null in
tblWO.newequipno
and there is a value in tblCosts.strEquipNo it is not pulling these
records.

After I retrieve this recordset, I put the value tblWO.newequipno into the
tblCosts.strEquipNo field. That code works too.

The only part that is not working is the <> part. How can I fix that
without fixing the data by doing an update query to change "" to null or
are
they the same thing???

Your assistance is greatly appreciated.

Lynn
Click to expand...
Click to expand...
 
I'm sorry, Lynn. Perhaps it's me - it has been quite a long, tiring week -
but I just don't seem to be able to make sense of that code. Perhaps some
one else would like to try?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Lynn Arlington said:
Thank you, the NZ() function was exactly what I was looking for.

This is a full process, I thought that solving one part might do it but
now
I have an additional issue.

The recordset gets pulled including those records with null value, now I
want to update the tblCosts with the Null value. Below is the entire code
behind this step. I will be repeating this step with several different
fields and have found its easier to work one field at a time. (Processing
time and speed are not an issue)

sub updatetable1()

strSQL = "SELECT tblWO.wono, tblWO.newequipno, tblCosts.strEquipNo FROM
tblCosts INNER JOIN tblWO ON tblCosts.strWONo = tblWO.wono WHERE
(((NZ(tblWO.newequipno)) <> NZ([tblCosts.strEquipNo]))) ORDER BY
tblWO.wono"


Set objRS = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
While Not objRS.EOF
'Make the changes
strSQL = "UPDATE tblCosts SET strEquipNo='" & objRS("newequipno") &
"' WHERE strWONo ='" & objRS("wono") & "';"

CurrentDb.Execute strSQL

objRS.MoveNext
Wend
objRS.Close
Set objRS = Nothing
end sub

The problem is with the Update statement, it doesn't seem to want to take
the null value and put it in the tblCosts field.

Any ideas?

Thanks again

Lynn


Brendan Reynolds said:
A Null represents an unknown value, and generally speaking, any
expression
involving a Null will evaluate to neither True nor False, but Null. Is
one
unknown value equal to another unknown value? The answer is unknown,
i.e.,
Null.

Access provides a useful NZ() function that can solve problems like
these.
The first argument to the function is the value to be tested for Null,
the
second, optional argument, which defaults to 0 for numbers or an empty
string for text, is the value to be substituted for Null values.

WHERE NZ(tblWO.newequipno, 0) <> NZ(tblCosts.strEquipNo, 0)

Alternatively, you could use a more complex WHERE clause to test for Null
values in the two fields ...

WHERE (tblWO.newequipno) <> tblCosts.strEquipNo) OR (tblWO.newequipno IS
NULL OR tblCosts.strEquipNo IS NULL)

While the second example is a little longer and more complex, it uses
only
SQL keywords and isn't dependent on any Access functions. It's more
portable
should you ever need to use it outside of Microsoft Access.

BTW, an empty string ("") is not the same as Null. A Null is an unknown
value. An empty string is a definite, known value, it is a string
containing
no characters. They may look the same, but they behave differently, for
example a test of whether two empty strings are equal will return True,
where a test of two Null values would return Null.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


message
I have the following in my database

SELECT tblWO.wono, tblWO.newequipno, tblCosts.strEquipNo FROM tblCosts
INNER
JOIN tblWO ON tblCosts.strWONO = tblWO.wono WHERE (((tblWO.newequipno)
<>
[tblCosts.strEquipNo])) ORDER BY tblWO.wono


It works great except that when a value is blank or Null in
tblWO.newequipno
and there is a value in tblCosts.strEquipNo it is not pulling these
records.

After I retrieve this recordset, I put the value tblWO.newequipno into
the
tblCosts.strEquipNo field. That code works too.

The only part that is not working is the <> part. How can I fix that
without fixing the data by doing an update query to change "" to null
or
are
they the same thing???

Your assistance is greatly appreciated.

Lynn
Click to expand...
Click to expand...
Click to expand...
 
when using the nz() function in the update query, how do I get the value
(null) from the old table into the new table

nz([strid],"") this doesn't work but nz([strid], " ") does but it puts a
space in the cell, can I use the word Null instead of the ""???


FYI, the second snippet of code I am using loops through each record that is
pulled in the earlier sql so it updates one record at a time instead of a
global update.

your help is greatly appreciated.
thanks, Lynn



Brendan Reynolds said:
I'm sorry, Lynn. Perhaps it's me - it has been quite a long, tiring week -
but I just don't seem to be able to make sense of that code. Perhaps some
one else would like to try?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Lynn Arlington said:
Thank you, the NZ() function was exactly what I was looking for.

This is a full process, I thought that solving one part might do it but
now
I have an additional issue.

The recordset gets pulled including those records with null value, now I
want to update the tblCosts with the Null value. Below is the entire code
behind this step. I will be repeating this step with several different
fields and have found its easier to work one field at a time. (Processing
time and speed are not an issue)

sub updatetable1()

strSQL = "SELECT tblWO.wono, tblWO.newequipno, tblCosts.strEquipNo FROM
tblCosts INNER JOIN tblWO ON tblCosts.strWONo = tblWO.wono WHERE
(((NZ(tblWO.newequipno)) <> NZ([tblCosts.strEquipNo]))) ORDER BY
tblWO.wono"


Set objRS = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
While Not objRS.EOF
'Make the changes
strSQL = "UPDATE tblCosts SET strEquipNo='" & objRS("newequipno") &
"' WHERE strWONo ='" & objRS("wono") & "';"

CurrentDb.Execute strSQL

objRS.MoveNext
Wend
objRS.Close
Set objRS = Nothing
end sub

The problem is with the Update statement, it doesn't seem to want to take
the null value and put it in the tblCosts field.

Any ideas?

Thanks again

Lynn


Brendan Reynolds said:
A Null represents an unknown value, and generally speaking, any
expression
involving a Null will evaluate to neither True nor False, but Null. Is
one
unknown value equal to another unknown value? The answer is unknown,
i.e.,
Null.

Access provides a useful NZ() function that can solve problems like
these.
The first argument to the function is the value to be tested for Null,
the
second, optional argument, which defaults to 0 for numbers or an empty
string for text, is the value to be substituted for Null values.

WHERE NZ(tblWO.newequipno, 0) <> NZ(tblCosts.strEquipNo, 0)

Alternatively, you could use a more complex WHERE clause to test for Null
values in the two fields ...

WHERE (tblWO.newequipno) <> tblCosts.strEquipNo) OR (tblWO.newequipno IS
NULL OR tblCosts.strEquipNo IS NULL)

While the second example is a little longer and more complex, it uses
only
SQL keywords and isn't dependent on any Access functions. It's more
portable
should you ever need to use it outside of Microsoft Access.

BTW, an empty string ("") is not the same as Null. A Null is an unknown
value. An empty string is a definite, known value, it is a string
containing
no characters. They may look the same, but they behave differently, for
example a test of whether two empty strings are equal will return True,
where a test of two Null values would return Null.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


message
I have the following in my database

SELECT tblWO.wono, tblWO.newequipno, tblCosts.strEquipNo FROM tblCosts
INNER
JOIN tblWO ON tblCosts.strWONO = tblWO.wono WHERE (((tblWO.newequipno)
<>
[tblCosts.strEquipNo])) ORDER BY tblWO.wono


It works great except that when a value is blank or Null in
tblWO.newequipno
and there is a value in tblCosts.strEquipNo it is not pulling these
records.

After I retrieve this recordset, I put the value tblWO.newequipno into
the
tblCosts.strEquipNo field. That code works too.

The only part that is not working is the <> part. How can I fix that
without fixing the data by doing an update query to change "" to null
or
are
they the same thing???

Your assistance is greatly appreciated.

Lynn
Click to expand...
Click to expand...
Click to expand...
 
The purpose of the NZ() function is to convert a Null value into something
else. If you want to put the Null value from the source field into the
target field, do not use NZ() for that, just copy it as is from the source
to the target.

You could put the word "Null" into the field using the NZ() function by
enclosing it in quotes (NZ([FieldName], "Null")) but you need to be aware
that the word "Null" is not the same thing as a Null value, and will be
treated differently by functions and statements that check for Null values.
Similarly, an empty string ("", a string containing no characters) is not
the same as a Null value, and will not be treated as such by those
functions.

If you want to store an empty string in the field, make sure that the Allow
Zero Length property of the field is set to "Yes" in table design view.
Generally, though, my advice is to stick to using Null values to represent
unknown values. An empty string, or the text "Null", can only be used in
text fields. If you use either of those to represent an unknown value in a
text field, you'll be forced to use something else to represent unknown
values in number and date fields.

A fellow-MVP, Allen Browne, has some good articles on Null values at the
following URL ...

http://allenbrowne.com/tips.html

Look in particular for the following articles ...

The Query Lost My Records! (Nulls)
Nulls: Do I need them?
Common errors with Null

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Lynn Arlington said:
when using the nz() function in the update query, how do I get the value
(null) from the old table into the new table

nz([strid],"") this doesn't work but nz([strid], " ") does but it puts a
space in the cell, can I use the word Null instead of the ""???


FYI, the second snippet of code I am using loops through each record that
is
pulled in the earlier sql so it updates one record at a time instead of a
global update.

your help is greatly appreciated.
thanks, Lynn



Brendan Reynolds said:
I'm sorry, Lynn. Perhaps it's me - it has been quite a long, tiring
week -
but I just don't seem to be able to make sense of that code. Perhaps some
one else would like to try?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


message
Thank you, the NZ() function was exactly what I was looking for.

This is a full process, I thought that solving one part might do it but
now
I have an additional issue.

The recordset gets pulled including those records with null value, now
I
want to update the tblCosts with the Null value. Below is the entire
code
behind this step. I will be repeating this step with several different
fields and have found its easier to work one field at a time.
(Processing
time and speed are not an issue)

sub updatetable1()

strSQL = "SELECT tblWO.wono, tblWO.newequipno, tblCosts.strEquipNo FROM
tblCosts INNER JOIN tblWO ON tblCosts.strWONo = tblWO.wono WHERE
(((NZ(tblWO.newequipno)) <> NZ([tblCosts.strEquipNo]))) ORDER BY
tblWO.wono"


Set objRS = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
While Not objRS.EOF
'Make the changes
strSQL = "UPDATE tblCosts SET strEquipNo='" &
objRS("newequipno") &
"' WHERE strWONo ='" & objRS("wono") & "';"

CurrentDb.Execute strSQL

objRS.MoveNext
Wend
objRS.Close
Set objRS = Nothing
end sub

The problem is with the Update statement, it doesn't seem to want to
take
the null value and put it in the tblCosts field.

Any ideas?

Thanks again

Lynn


:

A Null represents an unknown value, and generally speaking, any
expression
involving a Null will evaluate to neither True nor False, but Null. Is
one
unknown value equal to another unknown value? The answer is unknown,
i.e.,
Null.

Access provides a useful NZ() function that can solve problems like
these.
The first argument to the function is the value to be tested for Null,
the
second, optional argument, which defaults to 0 for numbers or an empty
string for text, is the value to be substituted for Null values.

WHERE NZ(tblWO.newequipno, 0) <> NZ(tblCosts.strEquipNo, 0)

Alternatively, you could use a more complex WHERE clause to test for
Null
values in the two fields ...

WHERE (tblWO.newequipno) <> tblCosts.strEquipNo) OR (tblWO.newequipno
IS
NULL OR tblCosts.strEquipNo IS NULL)

While the second example is a little longer and more complex, it uses
only
SQL keywords and isn't dependent on any Access functions. It's more
portable
should you ever need to use it outside of Microsoft Access.

BTW, an empty string ("") is not the same as Null. A Null is an
unknown
value. An empty string is a definite, known value, it is a string
containing
no characters. They may look the same, but they behave differently,
for
example a test of whether two empty strings are equal will return
True,
where a test of two Null values would return Null.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies
to
this post will be deleted without being read. Any e-mail claiming to
be
from brenreyn at indigo dot ie that is not digitally signed by me with
a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll
find
a useable e-mail address at the URL above.


message
I have the following in my database

SELECT tblWO.wono, tblWO.newequipno, tblCosts.strEquipNo FROM
tblCosts
INNER
JOIN tblWO ON tblCosts.strWONO = tblWO.wono WHERE
(((tblWO.newequipno)
<>
[tblCosts.strEquipNo])) ORDER BY tblWO.wono


It works great except that when a value is blank or Null in
tblWO.newequipno
and there is a value in tblCosts.strEquipNo it is not pulling these
records.

After I retrieve this recordset, I put the value tblWO.newequipno
into
the
tblCosts.strEquipNo field. That code works too.

The only part that is not working is the <> part. How can I fix
that
without fixing the data by doing an update query to change "" to
null
or
are
they the same thing???

Your assistance is greatly appreciated.

Lynn
Click to expand...
Click to expand...
Click to expand...
 
Thanks Brendan,
Lynn

Brendan Reynolds said:
The purpose of the NZ() function is to convert a Null value into something
else. If you want to put the Null value from the source field into the
target field, do not use NZ() for that, just copy it as is from the source
to the target.

You could put the word "Null" into the field using the NZ() function by
enclosing it in quotes (NZ([FieldName], "Null")) but you need to be aware
that the word "Null" is not the same thing as a Null value, and will be
treated differently by functions and statements that check for Null values.
Similarly, an empty string ("", a string containing no characters) is not
the same as a Null value, and will not be treated as such by those
functions.

If you want to store an empty string in the field, make sure that the Allow
Zero Length property of the field is set to "Yes" in table design view.
Generally, though, my advice is to stick to using Null values to represent
unknown values. An empty string, or the text "Null", can only be used in
text fields. If you use either of those to represent an unknown value in a
text field, you'll be forced to use something else to represent unknown
values in number and date fields.

A fellow-MVP, Allen Browne, has some good articles on Null values at the
following URL ...

http://allenbrowne.com/tips.html

Look in particular for the following articles ...

The Query Lost My Records! (Nulls)
Nulls: Do I need them?
Common errors with Null

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Lynn Arlington said:
when using the nz() function in the update query, how do I get the value
(null) from the old table into the new table

nz([strid],"") this doesn't work but nz([strid], " ") does but it puts a
space in the cell, can I use the word Null instead of the ""???


FYI, the second snippet of code I am using loops through each record that
is
pulled in the earlier sql so it updates one record at a time instead of a
global update.

your help is greatly appreciated.
thanks, Lynn



Brendan Reynolds said:
I'm sorry, Lynn. Perhaps it's me - it has been quite a long, tiring
week -
but I just don't seem to be able to make sense of that code. Perhaps some
one else would like to try?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


message
Thank you, the NZ() function was exactly what I was looking for.

This is a full process, I thought that solving one part might do it but
now
I have an additional issue.

The recordset gets pulled including those records with null value, now
I
want to update the tblCosts with the Null value. Below is the entire
code
behind this step. I will be repeating this step with several different
fields and have found its easier to work one field at a time.
(Processing
time and speed are not an issue)

sub updatetable1()

strSQL = "SELECT tblWO.wono, tblWO.newequipno, tblCosts.strEquipNo FROM
tblCosts INNER JOIN tblWO ON tblCosts.strWONo = tblWO.wono WHERE
(((NZ(tblWO.newequipno)) <> NZ([tblCosts.strEquipNo]))) ORDER BY
tblWO.wono"


Set objRS = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
While Not objRS.EOF
'Make the changes
strSQL = "UPDATE tblCosts SET strEquipNo='" &
objRS("newequipno") &
"' WHERE strWONo ='" & objRS("wono") & "';"

CurrentDb.Execute strSQL

objRS.MoveNext
Wend
objRS.Close
Set objRS = Nothing
end sub

The problem is with the Update statement, it doesn't seem to want to
take
the null value and put it in the tblCosts field.

Any ideas?

Thanks again

Lynn


:

A Null represents an unknown value, and generally speaking, any
expression
involving a Null will evaluate to neither True nor False, but Null. Is
one
unknown value equal to another unknown value? The answer is unknown,
i.e.,
Null.

Access provides a useful NZ() function that can solve problems like
these.
The first argument to the function is the value to be tested for Null,
the
second, optional argument, which defaults to 0 for numbers or an empty
string for text, is the value to be substituted for Null values.

WHERE NZ(tblWO.newequipno, 0) <> NZ(tblCosts.strEquipNo, 0)

Alternatively, you could use a more complex WHERE clause to test for
Null
values in the two fields ...

WHERE (tblWO.newequipno) <> tblCosts.strEquipNo) OR (tblWO.newequipno
IS
NULL OR tblCosts.strEquipNo IS NULL)

While the second example is a little longer and more complex, it uses
only
SQL keywords and isn't dependent on any Access functions. It's more
portable
should you ever need to use it outside of Microsoft Access.

BTW, an empty string ("") is not the same as Null. A Null is an
unknown
value. An empty string is a definite, known value, it is a string
containing
no characters. They may look the same, but they behave differently,
for
example a test of whether two empty strings are equal will return
True,
where a test of two Null values would return Null.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies
to
this post will be deleted without being read. Any e-mail claiming to
be
from brenreyn at indigo dot ie that is not digitally signed by me with
a
GlobalSign digital certificate is a forgery and should be deleted
without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll
find
a useable e-mail address at the URL above.


message
I have the following in my database

SELECT tblWO.wono, tblWO.newequipno, tblCosts.strEquipNo FROM
tblCosts
INNER
JOIN tblWO ON tblCosts.strWONO = tblWO.wono WHERE
(((tblWO.newequipno)
<>
[tblCosts.strEquipNo])) ORDER BY tblWO.wono


It works great except that when a value is blank or Null in
tblWO.newequipno
and there is a value in tblCosts.strEquipNo it is not pulling these
records.

After I retrieve this recordset, I put the value tblWO.newequipno
into
the
tblCosts.strEquipNo field. That code works too.

The only part that is not working is the <> part. How can I fix
that
without fixing the data by doing an update query to change "" to
null
or
are
they the same thing???

Your assistance is greatly appreciated.

Lynn
Click to expand...
Click to expand...
Click to expand...
 
Back
Top