Build Dynamic If Statement

[thad]

Hi all,

I am trying to build an if statement that is built by
checking equality. Can you tell me why the following code
below has a data type error when I run it. What can I do
to fix the loop.

rsProdFam.MoveFirst
Count = 0
'Build if statement
Do While Not rsProdFam.EOF
If rsProdFam!Description = "Sep Sleeve" And Count
= 0 Then
SepSleeveIfStatement = Left(rsMasterSepSleeve!
Prod_No, 6) & " <> " & rsProdFam!AddH_Prod_Fam
Count = Count + 1
ElseIf rsProdFam!Description = "Sep Sleeve" And
Count > 0 Then
SepSleeveIfStatement = SepSleeveIfStatement
+ " And " & Left(rsMasterSepSleeve!Prod_No, 6) & " <> " &
rsProdFam!AddH_Prod_Fam
Count = Count + 1
End If
rsProdFam.MoveNext
Loop

If SepSleeveIfStatement Then
rsMasterSepSleeve.Delete
End If

 

[SteveS]

Thad,

Since you didn't post the whole Sub, it's difficult to be
100% sure. I would guess that it is because of the plus
(+) sign in the ELSEIF statement. Try changing it to an
ampersand (&).

Was there an error when you compiled the code? Did you
step thru the code? Which line had the error?

I modified your code, maybe you will like it. It's a
little shorter...... (watch for line wrap)


rsProdFam.MoveFirst
SepSleeveIfStatement = ""
'Build if statement
Do While Not rsProdFam.EOF
If rsProdFam!Description = "Sep Sleeve" Then
' this is one line
SepSleeveIfStatement = SepSleeveIfStatement &
Left(rsMasterSepSleeve!Prod_No, 6) & " <> " & rsProdFam!
AddH_Prod_Fam & " And "

End If
rsProdFam.MoveNext
Loop


If Len(SepSleeveIfStatement) >0 Then
' get rid of the last 5 chars ->( " AND " )
'this is one line
SepSleeveIfStatement = left(SepSleeveIfStatement,
Len(SepSleeveIfStatement) - 5)
rsMasterSepSleeve.Delete
End If


HTH

Steve

 

[thad]

Hi Steve,

Thanks for your input. Is there a way to better post code
in this forum? The wordwrap is killing me.

I finally got it to work. See below. I took out some ""
and removed the (+).

rsProdFam.MoveFirst
Count = 0
'Build if statement
Do While Not rsProdFam.EOF
If rsProdFam!Description = "Sep Sleeve" And
Count = 0 Then
SepSleeveIfStatement = Left
(rsMasterSepSleeve!Prod_No, 6) <> rsProdFam!AddH_Prod_Fam
Count = Count + 1
ElseIf rsProdFam!Description = "Sep Sleeve"
And Count > 0 Then
SepSleeveIfStatement =
SepSleeveIfStatement And Left(rsMasterSepSleeve!Prod_No,
6) <> rsProdFam!AddH_Prod_Fam
Count = Count + 1
End If
rsProdFam.MoveNext
Loop

 

[SteveS]

-----Original Message-----

Hi Steve,

Thanks for your input. Is there a way to better post code
in this forum? The wordwrap is killing me.

No, I think this is it. I copy the code and paste it into
Notepad (with line wrap in Notepad turned off), then merge
the lines by going to the end of the line and pressing
Delete.

I finally got it to work. See below. I took out some ""
and removed the (+).

You might want to re-think this....
By removing the ampersands (&) and the quotes, you Totally
changed what SepSleeveIfStatement result will be. When
Count = 0, then SepSleeveIfStatement will be TRUE OR
FALSE. When Count > 0, then SepSleeveIfStatement will
always be FALSE. In any case, it will be TRUE/FALSE and
not a string.

Unless that is what you wanted in the first place???

rsProdFam.MoveFirst
Count = 0
'Build if statement
Do While Not rsProdFam.EOF
If rsProdFam!Description = "Sep Sleeve" And
Count = 0 Then
SepSleeveIfStatement = Left
(rsMasterSepSleeve!Prod_No, 6) <> rsProdFam!AddH_Prod_Fam
Count = Count + 1
ElseIf rsProdFam!Description = "Sep Sleeve"
And Count > 0 Then
SepSleeveIfStatement =
SepSleeveIfStatement And Left(rsMasterSepSleeve!Prod_No,
6) <> rsProdFam!AddH_Prod_Fam
Count = Count + 1
End If
rsProdFam.MoveNext
Loop

HTH

Steve