Recordset within another 3

[lanelouna]

hello
can ayone help me on this small problem
i have this code which is not working: it tells me that there are too few parameters, (the line where the error is, is in bold)
Set RS = db.OpenRecordset("CCA3TSurpIVDR", dbOpenDynaset)
Do Until RS.EOF
RS.Edit
lIVDR = RS!IVDR
strSQL = "SELECT CCA3TSurDefIfFerme.IVDR, CCA3TSurDefIfFerme.SCOD_CLR, CCA3TSurDefIfFerme.IF_CLASSE, "
For i = 0 To nbr
Classe = classesOuvertes(i)
strSQL = strSQL & "CCA3TSurDefIfFerme.[IF_OUVERT_" & Classe & "], "
Next i
strSQL = Left(strSQL, Len(strSQL) - 2)
strSQL = strSQL & " FROM CCA3TSurDefIfFerme"
strSQL = strSQL & " WHERE (((CCA3TSurDefIfFerme.IVDR)= lIVDR));"
Set RS2 = db.OpenRecordset(strSQL, dbOpenDynaset)
Do Until RS2.EOF
etc

in fact this is not woking because i am using WHERE (((CCA3TSurDefIfFerme.IVDR)= lIVDR));"

had i wrote
WHERE (((CCA3TSurDefIfFerme.IVDR)= 6000));"
it works
is there way to resolve this error
thanks in advance

Lina Chebli

 

[pmessana]

It appears your "lIVDR" is a variable though I can't see that for sure, if that is the case, your where clause has to be written like this:

"WHERE (((CCA3TSurDefIfFerme.IVDR)= " & lIVDR

If that is not the case, well, someone else can surely answer your question.

 

[LittleSmudge]

For a start

strSQL = Left(strSQL, Len(strSQL) - 2) _
     & " FROM CCA3TSurDefIfFerme " _
     & " WHERE CCA3TSurDefIfFerme.IVDR= " & lIVDR

However, if lIDVR is not numeric but TEXT then you'll need

strSQL = Left(strSQL, Len(strSQL) - 2) _
     & " FROM CCA3TSurDefIfFerme " _
     & " WHERE CCA3TSurDefIfFerme.IVDR = '" & lIVDR & "'"

'ope-that-'elps

G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[Maquis]

you need to take your variable outside the quotes. The way you have the statement written the variable name is being literally inserted into the strSQL instead of the contents of the variable.

Use this instead:


strSQL = strSQL & " WHERE CCA3TSurDefIfFerme.IVDR = " & lIVDR Maq
<insert witty signature here>

 

[lanelouna]

thanks alot all of You
it is working now
i thought iwas becoming better with this!
still a long way to go
thanks again for the explanation and the answer and for being quick!

Lina Chebli