Access 2003 IIF statement

It doesn't seem to be working properly

Page 1 of 1

1 Replies - 1292 Views - Last Post: 15 April 2009 - 01:50 PM

#1 krum110487   User is offline

  • D.I.C Regular

Reputation: 39
  • View blog
  • Posts: 291
  • Joined: 07-February 09

Access 2003 IIF statement

Posted 10 April 2009 - 12:39 PM

The Code:

Update PartInfo " & _
						"SET " & _
							"Nid= IIF(" & rs1.Fields(1).Value & " >= 0, " & rs1.Fields(1).Value & ", Nid) , " & _
							"Bid= IIF(" & rs1.Fields(4).Value & " >= 0, " & rs1.Fields(4).Value & ", Bid) , " & _
							"Sid= IIF(" & rs1.Fields(3).Value & " >= 0, " & rs1.Fields(3).Value & ", Sid) , " & _
							"Did= IIF(" & rs1.Fields(2).Value & " >= 0, " & rs1.Fields(2).Value & ", Did) , " & _
							"PartNum= " & rs1.Fields(0).Value & ", " & _
							"PartDesc= IIF(" & PartDesc & " <> 'N/A', " & PartDesc & ", PartDesc) , " & _
							"Weight= IIF(" & rs1.Fields(6).Value & " >= 0, " & rs1.Fields(6).Value & ", Weight) , " & _
							"Amount= IIF(" & rs1.Fields(7).Value & " >= 0, " & rs1.Fields(7).Value & ", Amount) , " & _
							"PickLoc= IIF(" & PickLoc & " <> 'N/A', " & PickLoc & ", PickLoc) , " & _
							"StoreLoc= IIF(" & StoreLoc & " <> 'N/A', " & StoreLoc & ", StoreLoc) , " & _
							"KanbanNum= " & rs1.Fields(10).Value & ", " & _
							"QuanityPerBox= IIF(" & rs1.Fields(11).Value & " >= 0, " & rs1.Fields(11).Value & ", QuanityPerBox) , " & _
							"DockCode= IIF(" & DockCodes & " <> 'N/A', " & DockCodes & ", DockCode) , " & _
							"Notes= IIF(" & Notes & " <> 'N/A', " & Notes & ", Notes) , " & _
							"Receiving= IIF(" & rs1.Fields(14).Value & " >= 0, " & rs1.Fields(14).Value & ", Receiving) , " & _
							"Sending= IIF(" & rs1.Fields(15).Value & " >= 0, " & rs1.Fields(15).Value & ", Sending) " & _
						"WHERE " & _
							"PartInfo.PartNum = '" & rs1.Fields(0).Value & "' AND PartInfo.Nid = " & rs1.Fields(1).Value & " AND PartInfo.Did = " & rs1.Fields(2).Value _
)



The Idea:

I am working on an update call to where it will only update the field if the value is not the default (null Value), numbers being -1 and strings being "N/A"

This was my idea...
	Update PartInfo
						SET
							Nid= IIF(NidValue >= 0, NidValue, Nid) , 
							Did= IIF(DidValue >= 0, DidValue, Did) , 
							PartDesc= IIF(PartDescVal <> 'N/A', PartDescVal, PartDesc)
							WHERE...



this code worked before I added the if statements, but I didn't want the current data to be updated with something that doesn't have a value. (they can update a part of the file instead of the whole thing).

And I am doing it like so, because I am using a module that is updating via batching a table.

The Problem:
It doesn't seem to work, it wont update any of the values on the list, and as I said before it worked before I added the IFF, and I am NOT getting any errors.
Thanks!
-Krum

This post has been edited by krum110487: 10 April 2009 - 12:41 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Access 2003 IIF statement

#2 krum110487   User is offline

  • D.I.C Regular

Reputation: 39
  • View blog
  • Posts: 291
  • Joined: 07-February 09

Re: Access 2003 IIF statement

Posted 15 April 2009 - 01:50 PM

At first I was wondering why no-one was replying, but I think I figured it out!

my code is fine, the problem was...dumb to say the least...

on my batch table when I change things, I fogot to get out of "edit" mode so that it can apply the changes, so it still had that little pencil next to it, once I did that it worked flawlessly... a week of confusion over a tiny technicality!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1