SQL IIf() Function Error

[Jrod2541]

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

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

UPDATE tblVendorItem

SET VendorOrder = IIf(VendorPriority=1, Vendor, IIf(VendorPriority=2, Vendor,  IIf(VendorPriority=3, Vendor,  IIf(VendorPriority=4, Vendor,  IIf(VendorPriority=5, Vendor, " " )))))

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?

[NeoPa]

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).

[twinnyfo]

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:

WHERE CanShipSameDay = True

Or, even simpler:

WHERE CanShipSameDay

On my sample table, the following code executed properly:

UPDATE tblVendorItems SET tblVendorItems.VendorOrder = 
IIf([VendorPriority]=1,[Vendor], 
IIf([VendorPriority]=2,[Vendor], 
IIf([VendorPriority]=3,[Vendor], 
IIf([VendorPriority]=4,[Vendor], 
IIf([VendorPriority]=5,[Vendor],"")))))
WHERE (((tblVendorItems.CanShipSameDay)=True));

Note: Line breaks added for easier viewing.

Let me know if this helps.

[NeoPa]

@Twinny.

The SQL posted is equivalent to :

UPDATE [tblVendorItems]
SET    [VendorOrder]=IIf([VendorPriority] Between 1 And 5,[Vendor],'') 
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.

[Jrod2541]

I'm back,

sorry for the late response but here's the table i have:

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

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 "vendorOrde r" value for each "SKU item".

I'm pretty lost at the moment, should I use VBA instead of SQL for this problem?

[Rabbit]

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.

[Jrod2541]

I've deleted the Vendororder field in thetblVendorIte ms 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.

[Rabbit]

So where does this leave you? Now that you've moved the field to the other table, is the issue resolved?

[NeoPa]

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.