Hello,
Run-time error ...
No value given one or more required paramete
Code:
UPDATE GuitarOptionDetails SET GuitarOptionDetails.OptionCombo = Concatenate("SELECT Option_Item FROM GuitarHeader WHERE GuitarItem & Option_Item =""" & [GuitarOptionDetails].[GuitarItem] & [GuitarOptionDetails].[Option_Item] & """");
basically i need to create an update query to update the OptionCombo field where:
1. OptionCategory=BODY
2. Combine all the BODY OptionItems as one into the OptionCombo field (the Concatenate() function should accomplish this portion)
3. For each InvoiceNumber AND GuitarItem.
NOTE: some invoices will not have any BODY Option_Item, this can place "N" in the OptionCombo BUT only if the invoice does not have a single BODY Option_Item, if it does, it needs to put the combined BODY Option_Item values into the OptionCombo field.
How should I rewrite the SQL to achieve this without getting this error?
basically i need to create an update query to update the OptionCombo field where:
1. OptionCategory=BODY
2. Combine all the BODY OptionItems as one into the OptionCombo field (the Concatenate() function should accomplish this portion)
3. For each InvoiceNumber AND GuitarItem.
How should I rewrite the SQL to achieve this without getting this error?
UPDATE GuitarOptionDetails SET
GuitarOptionDetails.OptionCombo = [color=red]"I don't understand this part (as it relates to Number 2)"[/color]
WHERE OptionCategory = "BODY"
As far as #2...combine Option Items?? So where are they?? Structuring?? In other fields?? How many other fields?? Does it vary?? Is there another condition involved here?? It sounds like some of those fields might be empty. If they are, does that come into play?? It won't if they are NULL, but are they??
Ok, so based on your table, and what you said in number two, I guess this is what you want...
Code:
UPDATE GuitarOptionDetails SET GuitarOptionDetails.OptionCombo =
[color=red]([Guitar Item] & ", " & [Option Item]) AS [New Field Name][/color]
WHERE OptionCategory = "BODY"
This creates a new field that "Concats" the two above field values together for every record (spaced by the "," delimiter). I'm not sure if your asking how to concat different fields in a record, or weather you are wanting to Concat all the values of a field into one record (You can't do this with CONCAT).
IIf([OptionCategory]="Body",Concatenate("SELECT Option_Item FROM GuitarOptionDetails WHERE GuitarItem & Option_Item =""" & GuitarOptionDetails.GuitarItem & GuitarOptionDetails.Option_Item & """"),"N")
and the result is this:
Code:
InvoiceDate InvoiceNumber GuitarItem Option_Item OptionCategory OptionCombo
10/18/07 2372490 CT3M B Finishes N
10/18/07 2372490 CT3M BC Options N
10/18/07 2372559 AE185 38 Body N
10/18/07 2372559 AE185 BC Options N
10/18/07 2372559 AE185 DTS Finishes N
10/18/07 2372559 AE185 GL Options N
10/18/07 2372559 AE185 IN Headstock N
10/18/07 2372559 AE185 NIN Neck N
10/18/07 2372559 AE185 QPH Headstock N
10/18/07 2372559 AE185 QSKA Finishes N
10/18/07 2372559 AE185 R14 Neck N
10/18/07 2372559 AE185 SL Options N
10/18/07 2372559 AE185 STJF Neck N
10/18/07 2372559 AE185 TN Neck N
10/15/07 2371872 LB76P 5M Neck SP2P
10/15/07 2371872 LB76P BC Options SP2P
10/15/07 2371872 LB76P BMF Neck SP2P
10/15/07 2371872 LB76P KOA Woods SP2P
10/15/07 2371872 LB76P SP2P Body SP2P
10/15/07 2371884 B4 BST Finishes HB2 HB2
10/15/07 2371884 B4 HB2 Body HB2 HB2
10/11/07 2371330 SC90M 5W Neck RB RB RB
10/11/07 2371330 SC90M 5W Body RB RB RB
10/11/07 2371330 SC90M B Finishes RB RB RB
10/11/07 2371330 SC90M BC Options RB RB RB
10/11/07 2371330 SC90M CG Finishes RB RB RB
10/11/07 2371330 SC90M NIN Neck RB RB RB
10/11/07 2371330 SC90M RB Body RB RB RB
so in these 2 invoices the 1st one is correct, "N" because there's no Body optioncategory.
But for the 2nd invoice "2372559" there is one Body option_item "38". It should have put "38" in the OptionCombo field instead put "N".
And still the 3rd invoice has "SP2P" in OptionCombo, which is correct since that's the ONLY Body option_item for this invoice.
And still the 4th invoice why it has "HB2 HB2" instead of just "HB2"?
And the last one shown here, why does it only have "RB RB RB" instead of "5W RB"?
Help_Angel, I have marked up your pasted table to better read it...
Code:
InvoiceDate[color=red](1)[/color] InvoiceNumber[color=red](2)[/color] GuitarItem[color=red](3)[/color] Option_Item[color=red](4)[/color] OptionCategory[color=red](5)[/color] OptionCombo[color=red](6)[/color]
10/18/07[color=red](1)[/color] 2372490[color=red](2)[/color] CT3M[color=red](3)[/color] B[color=red](4)[/color] Finishes[color=red](5)[/color] N[color=red](6)[/color]
10/18/07 2372490 CT3M BC Options N
10/18/07 2372559 AE185 38 Body N
10/18/07 2372559 AE185 BC Options N
10/18/07 2372559 AE185 DTS Finishes N
10/18/07 2372559 AE185 GL Options N
10/18/07 2372559 AE185 IN Headstock N
10/18/07 2372559 AE185 NIN Neck N
10/18/07 2372559 AE185 QPH Headstock N
10/18/07 2372559 AE185 QSKA Finishes N
10/18/07 2372559 AE185 R14 Neck N
10/18/07 2372559 AE185 SL Options N
10/18/07 2372559 AE185 STJF Neck N
10/18/07 2372559 AE185 TN Neck N
10/15/07[color=red](1)[/color] 2371872[color=red](2)[/color] LB76P[color=red](3)[/color] 5M[color=red](4)[/color] Neck[color=red](5)[/color] SP2P[color=red](6)[/color]
10/15/07 2371872 LB76P BC Options SP2P
10/15/07 2371872 LB76P BMF Neck SP2P
10/15/07 2371872 LB76P KOA Woods SP2P
10/15/07 2371872 LB76P SP2P Body SP2P
10/15/07 2371884 B4 BST Finishes HB2 HB2
10/15/07 2371884 B4 HB2 Body HB2 HB2
10/11/07 2371330 SC90M 5W Neck RB RB RB
10/11/07 2371330 SC90M 5W Body RB RB RB
10/11/07 2371330 SC90M B Finishes RB RB RB
10/11/07 2371330 SC90M BC Options RB RB RB
10/11/07 2371330 SC90M CG Finishes RB RB RB
10/11/07 2371330 SC90M NIN Neck RB RB RB
10/11/07 2371330 SC90M RB Body RB RB RB
As you can see from the table, your CONCAT statement in the IIF function is not being evaluated. I don't think you are writing the correct code for what you want really. This statement of yours...
basically i need to create an update query to update the OptionCombo field where:
1. OptionCategory=BODY 2. Combine all the BODY OptionItems as one into the OptionCombo field (the Concatenate() function should accomplish this portion)
suggests that there is (or should be) more than one value in certain RECORDS under the "OptionItem" field. However, in your table, I only see ONE value per record in this field. Also, this statement from you in red...
But for the 2nd invoice "2372559" there is one Body option_item "38". It should have put "38" in the OptionCombo field instead put "N".
And still the 3rd invoice has "SP2P" in OptionCombo, which is correct since that's the ONLY Body option_item for this invoice. And still the 4th invoice why it has "HB2 HB2" instead of just "HB2"?
suggests that for this particular record, the value of the cell in the "OptionCombo" is "HB2 HB2" (even before the IIF function is performed). I don't think it is, but that's what it looks like.
I'm not sure about the irratic nature of the data output; it certainly does not appear to make much sense. I can tell you these facts though...
** The CONCAT function IS valid, because if it wasn't, you wouldn't be able to view your query. ** If you are simply trying to CONCAT the values from the "OptionItem" field and the "OptionCombo" field, you don't need to write an essay.... [OptionItem] & " " & [OptionCombo] works just fine.
** When you write a function (DSum, IIF, etc...) or expression (field + field), Access produces a temporary field to store this data, and also assignes it a temporary name if you haven't. This is NOT the same as simply selecting the prepopulated fields from a table.
Another possibility for you to consider...
If you have selected all your tables in the field, PLUS included your IIF function as another temporary field in the query, I am guessing that some sort of duplication is happening, and may be jumbling up your data (but this is just a guess). "IIF([field] = "whatever", CONCAT(field1 & field2), "N")" Say you write that IIF function, and you also include "field1" & "field2" as separate columns in your SELECT statement....
Makes me wonder if this will duplicate actions on "field1" from the IIF statement....??
I have just looked at your database from the last thread you posted about it. Craig was helping you on it, and although I did not read through the million or so posts that are in the thread, I did look through the file. Apparently, there is more going on here than what you originally had given information for.
There are obviously going to be JOINS involved with this, so I'm not really convinced that it is as simple as you think. From reading the last post in the other thread, it doesn't seem as though that problem was ever solved. Was it??
Hi ajetrumpet,
yes, but for a different approach. it's been a difficult task and i've come up with another direction and needed help with the SQL statement using a new field OptionCombo.
basically trying to now get ONLY the Body Option_items concatenated into the new field OptionCombo and if there are no BODY Option_item then to put the value N.
basically trying to now get ONLY the Body Option_items concatenated into the new field OptionCombo and if there are no BODY Option_item then to put the value N.
there's nothing in OptionCombo field. i need it to run the update query to update the OptionCombo field with just the data from Option_Items ONLY when the Option_Items are Body from OptionCategory, otherwise place a N into the OptionCombo.
i need it to run the update query to update the OptionCombo field with just the data from Option_Items ONLY when the Option_Items are Body from OptionCategory, otherwise place a N into the OptionCombo.
Now, THAT is a clearly stated question!! If you're just updating, I think you'll have to do it twice...one for updating the NULLS and one for updating the other condition. For the "value" condition...
Code:
UPDATE table SET Table.OptionCombo = Table.OptionCategory
WHERE OptionCategory = "Body";
If you're looking to use the IIF statment, just remember that it is only a temporary field in a query. To make it permanent in the table, you will have to make another table and join the columns from both.
