Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Access 2010 - IIF Statement Not Making ANY Sense

Status
Not open for further replies.

nyucknyucknyuck

Technical User
Jan 29, 2008
14
US
IIF Statement Not Making ANY Sense.

This is an old and trusted DB that works JUST FINE in XP, but now, in Access 2010, there are boogers... and THIS one is killing me as I see no reason why it's not working (again, as it's working fine in XP)

I have the following string in a text box; it displays shipping method on a packing slip.

=IIf(Forms!frm_ProducerOrderChooser!ZeroCheck>0 And Forms!frm_ProducerOrderChooser!ShipVia=2,"Ship 2nd Day Air SAVER EOBD Via Fedex: 123456 or UPS#: ABCDEFG",IIf(Forms!frm_ProducerOrderChooser!ZeroCheck>0 And Forms!frm_ProducerOrderChooser!ShipVia=4,"Ship Next Business Day SAVER EOBD Via Fedex: 123456 or UPS#: ABCDEFG",IIf(Forms!frm_ProducerOrderChooser!ZeroCheck>0 And Forms!frm_ProducerOrderChooser!ShipVia=5," Ship RUSH Next Business Day 10:30am Via Fedex: 123456 or UPS#: ABCDEFG","Ship GROUND SAVER Via Fedex: 123456 or UPS#: ABCDEFG")))

If the [ShipVia] field equals anything but 4, I'm getting the correct text displayed, but when ShipVia = 4, it's not giving me what it should, it's displaying Ship GROUND SAVER Via Fedex: 123456 or UPS#: ABCDEFG"

I've tried simplifying the sting just to see if there were issues with such a long string and it still didnt work...the value of 4 is not triggering the right value.

Any help would be appreciated....
 
Put your selection in an input table if it does not already exist. Data belongs in tables not in code. Now you can add/delete/modify as things change. This allows unlimited choices. Here is the general idea.
Code:
tblShipCodes
 shipCode shipText
 1       Ship GROUND SAVER Via Fedex:  123456 or UPS#: ABCDEFG
 2       Ship 2nd Day Air SAVER EOBD Via Fedex:  123456 or UPS#: ABCDEFG
 3
 4       Ship Next Business Day SAVER EOBD Via Fedex:  123456 or UPS#: ABCDEFG
 5       Ship  RUSH Next Business Day 10:30am Via Fedex:  123456 or UPS#: ABCDEFG

then you could put this in the control on the form
=getShipText([ZeroCheck],[shipVia])

Code:
public function getShipText(zerocheck as variant, varvia as variant) as variant
  if not isnull(varVia) and zeroCheck <> 0 then
     getShipText = nz(dlookup("shipText","tblShipCodes","shipCode = " & varVia))
     if isNull(getShipText) then
       getShipText = dlookup("shipText","tblShipCodes","shipCode = 1")
     end if
  end if
end function
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top