Access 2003-filling "null" cells

H

Hal

I've had problems getting the syntax correct in design view when trying to
construct an update query to an existing table with a unique id so that i can
build relationshiqps. the problem seems to be with the IIf(IsNull)....,...,
statement. I get an error message identifying the IIf as an incorrect
"operator" Anyone help with this?
Thanks for any insight.
Hal
 
I

i_takeuti

Hal said:
I've had problems getting the syntax correct in design view when trying to
construct an update query to an existing table with a unique id so that i
can
build relationshiqps. the problem seems to be with the
IIf(IsNull)....,...,
statement. I get an error message identifying the IIf as an incorrect
"operator" Anyone help with this?
Thanks for any insight.
Hal
Click to expand...
 
H

Hal

Ofer, thanks for the reply. I'm know that this syntax is iincorrect, but I'd
appreciate any clarification/corrections. I'm just not familiar with the SQL.
My purpose is to update existing building equipment tables by adding the
building number (BuildingNo) as the unique identifier so that the database
can condtruct proper relationships with my basic "Building Information table"
Each building has a list of equipment that is unique to it, but which
contains item types similar to other buildings.
SQL:
UPDATE [1PK Facilities] INNER JOIN [ME0661BH Houlton Inventory Unedited] ON
[1PK Facilities].BuildingNo = [ME0661BH Houlton Inventory
Unedited].BuildingNo SET [1PK Facilities].BuildingNo = BuildingNo.[ME0661BH
Houlton Inventory Unedited](IsNull("BuildingNo]"),"ME0661BH","BuildingNo]")
WHERE ((([1PK Facilities].BuildingNo)="ME0661BH"));
 
O

Ofer Cohen

There is no need to use an update query, you can use a select query with iif
condition to create a new field that return ME0661BH if the BuildingNo is
empty

Something like:

Select [1PK Facilities].* , IIf([BuildingNo] Is Null
,"ME0661BH",[BuildingNo]) As NewBuilding From [1PK Facilities]

In your report you can use the new field created "NewBuilding"

--
Good Luck
BS"D


Hal said:
Ofer, thanks for the reply. I'm know that this syntax is iincorrect, but I'd
appreciate any clarification/corrections. I'm just not familiar with the SQL.
My purpose is to update existing building equipment tables by adding the
building number (BuildingNo) as the unique identifier so that the database
can condtruct proper relationships with my basic "Building Information table"
Each building has a list of equipment that is unique to it, but which
contains item types similar to other buildings.
SQL:
UPDATE [1PK Facilities] INNER JOIN [ME0661BH Houlton Inventory Unedited] ON
[1PK Facilities].BuildingNo = [ME0661BH Houlton Inventory
Unedited].BuildingNo SET [1PK Facilities].BuildingNo = BuildingNo.[ME0661BH
Houlton Inventory Unedited](IsNull("BuildingNo]"),"ME0661BH","BuildingNo]")
WHERE ((([1PK Facilities].BuildingNo)="ME0661BH"));


Ofer Cohen said:
Can you post the full SQL?
Click to expand...
Click to expand...
 
Top