By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,968 Members | 1,610 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,968 IT Pros & Developers. It's quick & easy.

SQL IIf() Function Error

P: 26
I'm back again. I have a table called tblVendorItem, pretty much exactly the same as twinnyfo's, except I added another field called "VendorOrder".

But the problem I'm having is when I put this table in a query and run this code in SQL:

Expand|Select|Wrap|Line Numbers
  1. UPDATE tblVendorItem
  2.  
  3. SET VendorOrder = IIf(VendorPriority=1, Vendor, IIf(VendorPriority=2, Vendor,  IIf(VendorPriority=3, Vendor,  IIf(VendorPriority=4, Vendor,  IIf(VendorPriority=5, Vendor, " " )))))
  4.  
  5. WHERE CanShipSameDay = "Yes";
I get an error message saying:

"Microsoft Access can't update all the records in the update query

Microsoft Access didn't update 70 field(s) due to a type conversion failure, 0 record(s) due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to calidation rule violations.
Do you want to continue running this query anyway?
To ignore the error(s) and run the query, click Yes."



When I click "Yes" to ignore the errors, the VendorOrder field remains blank



What I'm trying to do with this query is check the vendorPriority for the lowest number and if the CanShipSameDay says "Yes", then the VendorOrder will show whatever is in the vendor field.

If the CanShipSameDay says "No", then the query will look in VendorPriority2 and so on.

Any idea on how to fix this?
Jun 26 '14 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,769
Please try to explain this better.

Start by posting details of the fields in the table (A link to another post which is not even principally about the table layout is not helpful. It shouldn't be this complicated just getting what the question is).

Then revisit the wording of your explanation of the requirement. I can see clearly that it in no way matches the code, but what you're actually after makes little sense and seems contradictory.

I would guess that [VendorOrder] is a numeric field so setting it to a space is never going to work, but what you're actually after is just guesswork after that.

I don't mean to put you off. I expect you'll come to understand how important the questions are after a couple of them, but if it doesn't make sense then sympathy won't help you much (Most people will simply ignore it if they can't understand it).
Jun 26 '14 #2

twinnyfo
Expert Mod 2.5K+
P: 3,488
A couple things. It looks like no matter what the Priority, you are merely setting the Vendor to the Current vendor of the Record. Is this what you are trying to do? If so, then just set the value to the Vendor Field.

Second, my thought is that your VendorOrder field is not the same data type as the Vendor Field. This could cause problems. Remember, in your Table tblVendorItems, the Vendor is identified by the PK of the Table tblVendors (or at least it should be). What you are telling your query to do may be confusing it.

Oddly enough, when I added your code to my SQL editor, and then went to design view, it did not recognize "VendorPriority" but thought it was a text string, so I put brackets around the field.

Also, if your CanShipSameDay field is a Yes/No field, then the proper syntax is:

Expand|Select|Wrap|Line Numbers
  1. WHERE CanShipSameDay = True
Or, even simpler:

Expand|Select|Wrap|Line Numbers
  1. WHERE CanShipSameDay
On my sample table, the following code executed properly:

Expand|Select|Wrap|Line Numbers
  1. UPDATE tblVendorItems SET tblVendorItems.VendorOrder = 
  2. IIf([VendorPriority]=1,[Vendor], 
  3. IIf([VendorPriority]=2,[Vendor], 
  4. IIf([VendorPriority]=3,[Vendor], 
  5. IIf([VendorPriority]=4,[Vendor], 
  6. IIf([VendorPriority]=5,[Vendor],"")))))
  7. WHERE (((tblVendorItems.CanShipSameDay)=True));
Note: Line breaks added for easier viewing.

Let me know if this helps.
Jun 26 '14 #3

NeoPa
Expert Mod 15k+
P: 31,769
@Twinny.

The SQL posted is equivalent to :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [tblVendorItems]
  2. SET    [VendorOrder]=IIf([VendorPriority] Between 1 And 5,[Vendor],'') 
  3. WHERE  ([CanShipSameDay])
You and I can see that this is very unlikely to be what the OP intended - particularly in view of their initial explanation. It never touches any record that doesn't have [CanShipSameDay] set to True (or Yes - but not 'Yes'). I believe they need to be encouraged to take a step back and work out what they actually want before we try to help at any detailed level. Otherwise, experience tells me we'll all be wasting each other's time.

Your test assumes that their table design matches what you have, but I suspect even there, that the problem is that they don't. It seems likely they have a design such that either strings, or whatever type of data the [Vendor] field is, is not valid for the field [VendorOrder]. At the end of the day that's the only field the SQL will atempt to update.
Jun 26 '14 #4

P: 26
I'm back,

sorry for the late response but here's the table i have:
Expand|Select|Wrap|Line Numbers
  1. ID   SKU   Vend  Pri  VItem   SameDay  VendorOrder
  2. 1    123   MDTV   2   4987    Yes     
  3. 2    123   RYUU   1   464     Yes     
  4. 3    124   MDTV   2   5001    Yes     
  5. 4    124   RYUU   1   463     Yes     
  6. 5    254   MDTV   2   3998    Yes     
  7. 6    254   RYUU   1   085     Yes     
  8. 7    542   MDTV   2   5000    Yes     
  9. 8    542   RYUU   1   357     Yes     
  10. 9    324   MDTV   2   4304    Yes     
  11. 10   324   RYUU   1   874     Yes     
  12. 11   786   MDTV   2   4387    Yes     
  13. 12   786   RYUU   1   919     Yes     
  14. 13   475   MDTV   2   7653    Yes     
  15. 14   475   RYUU   1   898     Yes     
  16. 15   521   MDTV   2   4389    Yes     
  17. 16   521   RYUU   1   078     Yes     
  18. 17   354   MDTV   2   4020    Yes     
  19. 18   354   RYUU   1   489     Yes     
  20. 19   254   MDTV   2   4300    Yes     
  21. 20   254   RYUU   1   167     Yes     
  22. 21   128   MDTV   2   4760    Yes     
  23. 22   128   RYUU   1   168     Yes     
  24. 23   009   MDTV   2   7542    Yes     
  25. 24   009   RYUU   1   597     No      
  26. 25   010   MDTV   2   9677    Yes     
  27. 26   010   RYUU   1   598     Yes     
  28. 27   704   MDTV   0   4555    Yes     
  29. 28   704   LOWE   2   23-OH   No      
  30. 29   704   RYUU   3   101     Yes     
  31. 30   704   GVHH   1   804521  Yes     
  32. 31   157   MDTV   0   1700    Yes     
  33. 32   157   RYUU   1   416     Yes     
  34. 33   157   GVHH   2   830921  Yes     
  35. 34   858   MDTV   0   4044    Yes     
  36. 35   858   LOWE   0   23-HF   Yes     
  37. 36   858   RYUU   1   413     Yes     
  38. 37   858   GVHH   2   207871  Yes     
  39. 38   821   MDTV   0   4817    Yes     
  40. 39   821   LOWE   0   23-HJ   Yes     
  41. 40   821   RYUU   1   132     Yes     
  42. 41   821   GVHH   2   507702  Yes     
  43. 42   724   MDTV   0   8978    Yes     
  44. 43   724   LOWE   0   23-IU   Yes     
  45. 44   724   RYUU   1   533     Yes     
  46. 45   724   GVHH   2   434501  Yes     
  47. 46   428   MDTV   0   4567    Yes     
  48. 47   428   LOWE   3   23-YU   Yes     
  49. 48   428   RYUU   1   471     No      
  50. 49   428   GVHH   2   459601  Yes     
  51. 50   129   MDTV   0   5287    Yes     
  52. 51   129   LOWE   0   23-FF   Yes     
  53. 52   129   RYUU   1   503     Yes     
  54. 53   129   GVHH   2   784701  Yes     
  55. 54   219   MDTV   0   5647    Yes     
  56. 55   219   LOWE   0   42-SD   Yes     
  57. 56   219   RYUU   1   219     Yes     
  58. 57   481   MDTV   0   4456    Yes     
  59. 58   481   LOWE   0   42-BG   Yes     
  60. 59   481   RYUU   1   925     Yes     
  61. 60   484   MDTV   0   6547    Yes     
  62. 61   484   LOWE   0   42-BB   Yes     
  63. 62   484   RYUU   1   914     Yes     
  64. 63   411   MDTV   0   6457    Yes     
  65. 64   411   LOWE   0   46-EH   Yes     
  66. 65   411   RYUU   1   470     Yes     
  67. 66   442   MDTV   0   4564    Yes     
  68. 67   442   LOWE   0   31-TF   Yes     
  69. 68   442   RYUU   1   488     Yes     
  70. 69   754   MDTV   0   4898    Yes     
  71. 70   754   LOWE   0   31-AS   Yes     
  72. 71   754   RYUU   1   987     Yes     
  73. 72   321   MDTV   0   4567    Yes     
  74. 73   321   LOWE   0   31-MN   Yes     
  75. 74   321   RYUU   1   949     Yes     
  76.  

Vend = Vendor

Pri = VendorPriority

VItem = VendorItem

SameDay = CanShipSameDay




What I need this query to do is find the lowest number (but not 0) in the priority field, then look at that same row to see if the CanShipSameDay is "Yes".

If it does say yes, go to the tblOrders table (Which has two fields: SKU ITEM # and VendorOrder) and input whatever the vendor is.

If CanShipSameDay is "No" then the query would look at priority number "2" for that SKU item and check the CanShipSameDay for that item.

There can only be one VendorOrder for each SKU item.

So for each SKU ITEM, I need this vendor to check for the lowest priority number (not 0), check if it can ship that day and put the vendor's name on the vendorOrder field in another table





I tried using the code Twinnyfo gave and I changed the [CanShipSameDay] data type from "Short Text" to "Yes/No", but it still gave me the same error message when I tried to run it.

Also, VendorOrder is not a numeric value, it's supposed to display who I order from. So under VendorOrder, it should say the name of the vendor I want to order from, like "MDTV"

And even if it did run properly, I still wouldn't be done because I just realized that there can only be one "vendorOrder" value for each "SKU item".

I'm pretty lost at the moment, should I use VBA instead of SQL for this problem?
Jun 27 '14 #5

Rabbit
Expert Mod 10K+
P: 12,430
First, you shouldn't have a VendorOrder field in the VendorItem table. That field should go on an Order table. You modified the table that twinnyfo originally laid out by adding an extra field that shouldn't be there

Second, did you design all 3 of the tables that you need to for this piece of your project? In one of your other threads, it was mentioned that you would need at least 3 tables to accomplish this part of the project.

Third, it you're lost at the SQL stage of development, adding in VBA will only further complicate things. SQL is much simpler than VBA and the problem you are having is nothing that can't be handled by SQL.
Jun 27 '14 #6

P: 26
I've deleted the Vendororder field in thetblVendorItems table and I put that field in an orders table named tblOrders.

And yes I did create the other two tables for the Vendors and the SKU items, they are called tblVendors and tblSKUItems.
Jun 27 '14 #7

Rabbit
Expert Mod 10K+
P: 12,430
So where does this leave you? Now that you've moved the field to the other table, is the issue resolved?
Jun 27 '14 #8

NeoPa
Expert Mod 15k+
P: 31,769
Please revisit post #2 where you were asked to explain again (clearly and consistently) what it is you're actually trying to do with this code. Your original explanation doesn't actually make sense so it's hard to help you at this point.
Jun 28 '14 #9

Post your reply

Sign in to post your reply or Sign up for a free account.