Query construction help needed! 2

[Smitty020]

OK, I hope that I can explain this correctly.

I have an array called "Ids" that will hold up to four numbers at a time.

Example:
It may have (34,67,32,56)
Or maybe just (12,0,0,0)

If there is only one entry, the rest of the array is filled in with 0's

Now, I want to use this array to query my DB.

This is what I have done so far.

SQL = "SELECT Num FROM Table1 WHERE ID IN("
For a = 0 To UBound(Ids)
If lAccountIds(a) = 0 Then
SQL = SQL & Ids(a) & " )"
Else
SQL = SQL & Ids(a) & ","
End If
Next a


What I am trying to do is slap a ) on instead of a comma, when the last number is read.

Can anyone help me fix this?......or does anyone have a better method?

I appreciate your help!

 

[Elysium]

Why don't you use a "for loop" instead of the IF statement. That way, you can control what position you are on. When you hit the last position, then use a &quot" instead of the comma.

Elysium

 

[zemp]

If the zero is not the last element in the array it looks like you are adding more than one ')' to the SQL statement

Try adding an 'exit for' line

SQL = "SELECT Num FROM Table1 WHERE ID IN("
For a = 0 To UBound(Ids)
If lAccountIds(a) = 0 Then
SQL = SQL & Ids(a) & " )"
exit for
Else
SQL = SQL & Ids(a) & ","
End If
Next a
Thanks and Good Luck!

zemp

 

[vb5prgrmr]

Give this test a try...

[tt]
Dim Ids(3), A, SQL

Ids(0) = 34
Ids(1) = 67
Ids(2) = 32
Ids(3) = 56

SQL = "SELECT Num FROM Table1 WHERE ID IN("
For A = 0 To UBound(Ids)
If Ids(A) <> 0 Then
SQL = SQL & Ids(A) & &quot;,&quot;
End If
Next A

SQL = Left(SQL, Len(SQL) - 1) & &quot&quot;

Debug.Print SQL

Ids(0) = 12
Ids(1) = 0
Ids(2) = 0
Ids(3) = 0

SQL = &quot;SELECT Num FROM Table1 WHERE ID IN(&quot;
For A = 0 To UBound(Ids)
If Ids(A) <> 0 Then
SQL = SQL & Ids(A) & &quot;,&quot;
End If
Next A

SQL = Left(SQL, Len(SQL) - 1) & &quot&quot;

Debug.Print SQL
[/tt]

I hope this helps, Good Luck



Elysium, Look Closer it there you will see it

 

[Smitty020]

I'm not sure I understand what you mean. The thing is, if there is only one number in the array, say it's 15, then I want the SQL to say:

Look for Numb where id in (15)

not

Look for Numb where id in (15,0,0,0)

Could you give me an example maybe?

Thanks!

 

[zemp]

Overlooked one thing.

When you find a zero don't addit just add the ')'.

SQL = &quot;SELECT Num FROM Table1 WHERE ID IN(&quot;
For a = 0 To UBound(Ids)
If lAccountIds(a) = 0 Then
SQL = SQL & &quot; )&quot;
exit for
Else
SQL = SQL & Ids(a) & &quot;,&quot;
End If
Next a
Thanks and Good Luck!

zemp

 

[Smitty020]

vb5prgrmr, THANK YOU! Works like a charm!

Zemp, yours worked nicely too!

 

[kbuc6]

Why don't you just not change it. In an &quot;IN&quot; statement you could have zeros and you should just not get any rows back.

USE Northwind
SELECT * from Employees WHERE EmployeeID IN(1,4,0,0)


this is a valid statement, requires no extra vb processing and allows sql to process it and you don't have to worry about the last comma.