correct SQL statement? ...

[vbahelp07]

Run-time error ...
no value given one or more required paramete

from:

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.
3. For each InvoiceNumber AND GuitarItem.

NOTE: some invoices (guitaritems) may not have a 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 with the error.

 

[vbahelp07]

Hi,

SELECT GuitarHeader.InvoiceDate, GuitarHeader.InvoiceNumber, GuitarHeader.GuitarItem, GuitarDetails.Option_Item, GuitarHeader.OptionCombo, FinishOptions.OptionCategory
FROM (GuitarHeader INNER JOIN GuitarDetails ON GuitarHeader.InvoiceNumber = GuitarDetails.InvoiceNumber) INNER JOIN FinishOptions ON GuitarDetails.Option_Item = FinishOptions.OptionItems;

 

[lameid]

I really don't see why that query I posted does not take care of the list. It will not fix the 'N' problem.

Without the concatenate code and the data I'm at a loss.

The IIF approach simply won't work correctly the way you are going about it if at all.

 

[vbahelp07]

It is odd ...

i can't remember the post ID. Here is the concatenate() function. It is from Dhookom

Option Compare Database

Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = " ") _
        As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
'   this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
    Dim rs As New ADODB.Recordset
        rs.Open pstrSQL, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
'====== uncomment next line for DAO ========
    'Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
        Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
End Function

so what can I change? should it be a two step update query?
how do I utilize the "N" for the invoices without any Body (OptionCategory) Option_items?

 

[lameid]

The second query should be a modification of my second step in figuring out how to make the list...

Update GuitarHeader Set GuitarHeader.optionCombo = [i][b]"N"[/b][/i]
Where GuitarHeader.InvoiceNumber [i][b]NOT[/b][/i] IN 
     (Select GuitarDeatils.InvoiceNumber 
      From GuitarDeatils inner Join FinsihOption ON GuitarDeatils.Option_Item = FinsihOption.OptionItem
      Where GuitarDeatils.FinishOption.OptionCategory = "Body")

But if the final query I made doesn't work, I don't know how that will help.

 

[vbahelp07]

Hi lameid!

this one works great! had to change the last line to

Where FinishOption.OptionCategory = "Body"

from

Where GuitarDetails.FinishOption.OptionCategory = "Body"

it didn't like the extra GuitarDetails ...


anyway THANK YOU!


The other query though, i get a

Run-time error ...
No value for one or more ...

UPDATE GuitarHeader SET GuitarHeader.OptionCombo = Concatenate("Select GuitarDetails.Option_Item From GuitarDetails inner Join FinishOptions ON GuitarDetails.Option_Item = FinishOptions.OptionItem Where GuitarDetails.FinishOptions.OptionCategory = ""Body"" AND GuitarDetails.InvoiceNumber = " & GuitarHeader.InvoiceNumber)
WHERE (((GuitarHeader.InvoiceNumber) In (Select GuitarDetails.InvoiceNumber       
From GuitarDetails inner Join FinishOptions ON GuitarDetails.Option_Item = FinishOptions.OptionItems       Where FinishOptions.OptionCategory = "Body")));

 

[lameid]

UPDATE GuitarHeader SET GuitarHeader.OptionCombo = Concatenate("Select GuitarDetails.Option_Item From GuitarDetails inner Join FinishOptions ON GuitarDetails.Option_Item = FinishOptions.OptionItem Where FinishOptions.OptionCategory = ""Body"" AND GuitarDetails.InvoiceNumber = " & GuitarHeader.InvoiceNumber)
WHERE (((GuitarHeader.InvoiceNumber) In (Select GuitarDetails.InvoiceNumber       
From GuitarDetails inner Join FinishOptions ON GuitarDetails.Option_Item = FinishOptions.OptionItems       Where FinishOptions.OptionCategory = "Body")));

I see, I copied the same wrong criteria from the where clause to the concatenate (the extra GuitarDetails.).

I must have missed deleting it from a join or something I copied.

 

[vbahelp07]

Thanks.

weird, i am still receiving the

run-time error
no value given for one or more required parameters.

when i click on debug it goes to this portion of the Concatenate() function (beginning)

    rs.Open pstrSQL, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic

could it be Null value handling issue?

 

[lameid]

Does the code compile? Do the constants have values?

Just looking for a reason for it not to work.

You could comment the ADO version and uncomment the DAO version. Then that line is not even involved.

 

[vbahelp07]

compiles fine.

strangely enough the Concatenate() function is working in another query.

so, yes, very odd that it's erroring with that one.

 

[lameid]

Hmmm...

If you debug and in the immediate window type

? pstrSQL

and hit Enter you should get the SQL it is trying to use.

If the problem is not obvious, you can copy and paste the SQL into a query and then get its datasheet view. That should show if there are any problems with the SQL being executed to obtain the list.

 

[vbahelp07]

this is what returns:

Select GuitarDetails.Option_Item From GuitarDetails inner Join FinishOptions ON GuitarDetails.Option_Item = FinishOptions.OptionItem Where FinishOptions.OptionCategory = "Body" AND GuitarDetails.InvoiceNumber = 2373275

and a random invoice number of

InvoiceDate	InvoiceNumber	GuitarItem	Option_Item	OptionCategory	OptionCombo
10/24/07	2373275	SC90C	36	Options	
10/24/07	2373275	SC90C	AB	Neck	
10/24/07	2373275	SC90C	FPH	Headstock	
10/24/07	2373275	SC90C	FTS	Finishes	
10/24/07	2373275	SC90C	G	Options	
10/24/07	2373275	SC90C	LN	Neck	
10/24/07	2373275	SC90C	RB	Body

which is in the middle of the list.

weird!

 

[lameid]

I trust when you execute the SQL that it returns one field and row with a value of RB?

If so I have nothing left but database corruption...

Backup the database

Then try these and keep going till it works:
Try compact repair.
Execute access with the decomplie switch with the MDB.
Import everything inot a new database file.
Cry. And try backups and putting code and SQL in that is missing by copying text.