SQL Statement with Nz

[rdemyan via AccessMonster.com]

I have to make use of data in another system. The data is imported into my
app and stored in a table. Unfortunately there is no primary key. Only two
fields out of about 15 are required and these two do not constitute a unique
key.

I need to be able to select records from the table based on almost all of the
fields. In 99% of the cases this will yield a unique row but there will be
exceptions.

I'm having trouble with how to use Nz in an SQL statement to be used in code.
I have the following (this is abbreviated, but shows the issues). It seems
to work, but I want to confirm that I am doing it the correct way.

strSQL = "SELECT * FROM [TABLEA] WHERE BUILDING_ID = '" & rsOLD("BUILDING_ID")
& "'" & _
" AND Nz(USER_ID,'') = '" & Nz(rsOLD("USER_ID")) & "' AND Nz(AREA,0)
= " & Nz(rsOLD("AREA"), 0) & _
" AND TIME_STAMP = #" & Me.Download_Date & "# ORDER BY BUILDING_ID
ASC;"

I severely shortened this but it contains the elements of what I need to
confirm (hopefully I didn't type in any syntax errors).

The issue I have is if USER_ID or AREA are null in the table. I still need
to get that record and I want to make sure that my syntax is correct. rsOLD
is a recordset that is created prior to the code reaching this SQL statement.

Thanks.

 

[Marshall Barton]

I have to make use of data in another system. The data is imported into my
app and stored in a table. Unfortunately there is no primary key. Only two
fields out of about 15 are required and these two do not constitute a unique
key.

I need to be able to select records from the table based on almost all of the
fields. In 99% of the cases this will yield a unique row but there will be
exceptions.

I'm having trouble with how to use Nz in an SQL statement to be used in code.
I have the following (this is abbreviated, but shows the issues). It seems
to work, but I want to confirm that I am doing it the correct way.

strSQL = "SELECT * FROM [TABLEA] WHERE BUILDING_ID = '" & rsOLD("BUILDING_ID")
& "'" & _
" AND Nz(USER_ID,'') = '" & Nz(rsOLD("USER_ID")) & "' AND Nz(AREA,0)
= " & Nz(rsOLD("AREA"), 0) & _
" AND TIME_STAMP = #" & Me.Download_Date & "# ORDER BY BUILDING_ID
ASC;"

I severely shortened this but it contains the elements of what I need to
confirm (hopefully I didn't type in any syntax errors).

The issue I have is if USER_ID or AREA are null in the table. I still need
to get that record and I want to make sure that my syntax is correct. rsOLD
is a recordset that is created prior to the code reaching this SQL statement.

I think that should do what you want, as long as there are
no records with an zero length string in the userid field
(unlikely) and no records with a 0 in the area field
(possible?). You may want to use some totally off the wall
values instead. For example,
" AND Nz(User_ID, 'Q~X') = '" & Nz(rsOLD("USER_ID"), "Q~X")
& "' AND Nz(AREA,-99) = " & Nz(rsOLD("AREA"), -99) & _

 

[rdemyan via AccessMonster.com]

Thanks, Marshall:

I'll probably take your advice and use "off-the-wall" values, but I'm not
sure what the
problem is if the Area does equal zero and not null. The NZ function only
does it's magic if the value is null, right. So, I don't see the issue. Can
you explain that further.

Thanks.

I have to make use of data in another system. The data is imported into my
app and stored in a table. Unfortunately there is no primary key. Only two

[quoted text clipped - 22 lines]

to get that record and I want to make sure that my syntax is correct. rsOLD
is a recordset that is created prior to the code reaching this SQL statement.

I think that should do what you want, as long as there are
no records with an zero length string in the userid field
(unlikely) and no records with a 0 in the area field
(possible?). You may want to use some totally off the wall
values instead. For example,
" AND Nz(User_ID, 'Q~X') = '" & Nz(rsOLD("USER_ID"), "Q~X")
& "' AND Nz(AREA,-99) = " & Nz(rsOLD("AREA"), -99) & _

 

[Michel Walsh]

Hi,


If you want the records where a field value is either null either equal to
some constant, you can try:

Nz(fieldName, constant) = constant


but that invalidates the possible use of indexes. Maybe better:


fieldName = constant OR fieldName IS NULL



Hoping it may help,
Vanderghast, Access MVP

Thanks, Marshall:

I'll probably take your advice and use "off-the-wall" values, but I'm not
sure what the
problem is if the Area does equal zero and not null. The NZ function only
does it's magic if the value is null, right. So, I don't see the issue.
Can
you explain that further.

Thanks.

I have to make use of data in another system. The data is imported into
my
app and stored in a table. Unfortunately there is no primary key. Only
two

[quoted text clipped - 22 lines]

to get that record and I want to make sure that my syntax is correct.
rsOLD
is a recordset that is created prior to the code reaching this SQL
statement.

I think that should do what you want, as long as there are
no records with an zero length string in the userid field
(unlikely) and no records with a 0 in the area field
(possible?). You may want to use some totally off the wall
values instead. For example,
" AND Nz(User_ID, 'Q~X') = '" & Nz(rsOLD("USER_ID"), "Q~X")
& "' AND Nz(AREA,-99) = " & Nz(rsOLD("AREA"), -99) & _

 

[Marshall Barton]

Michel makes a very good point, best to listen to him.

To answer your followup question. The way you had it, a
Null value in a table record would match a recordset record
that has a 0 in the field. I think you only wanted to match
Null to Null, and 0 to 0 without mixing them.
--
Marsh
MVP [MS Access]

I'll probably take your advice and use "off-the-wall" values, but I'm not
sure what the
problem is if the Area does equal zero and not null. The NZ function only
does it's magic if the value is null, right. So, I don't see the issue. Can
you explain that further.

I have to make use of data in another system. The data is imported into my
app and stored in a table. Unfortunately there is no primary key. Only two

[quoted text clipped - 22 lines]

to get that record and I want to make sure that my syntax is correct. rsOLD
is a recordset that is created prior to the code reaching this SQL statement.

I think that should do what you want, as long as there are
no records with an zero length string in the userid field
(unlikely) and no records with a 0 in the area field
(possible?). You may want to use some totally off the wall
values instead. For example,
" AND Nz(User_ID, 'Q~X') = '" & Nz(rsOLD("USER_ID"), "Q~X")
& "' AND Nz(AREA,-99) = " & Nz(rsOLD("AREA"), -99) & _