The last ever Sql problem

Jak

Ok. All of the last problem i posted has now been solved ... actually through a combination of the various bits of advice i got here.

Except of course the first one. So I'm back to ask for even ideas about how to get around it.

As before I need another Criteria Filter question for the Query. Which will ask for all the machine numbers.

As before they will be non-sequential and there could be up to 25 in a group.

So I'm lookin for a little VB box to pop up and say

---

Enter Machine Numbers

---

And the I punch in ...

2,14,28,29,30

And they are the only one's selected.

I have searched the various tech help and of course MS Access help things without luck. All i want now is something that will work even.

I mean, could i just have a load of boxes pop up for each number? But then how would it know when to stop asking?

Any ideas appreciated,

Cheers,

JAK.

Slosh

Use an Access macro and a loop asking for numbers (using the inputbox function), convert the string entered it to a number, (val function) and end the loop on a negative number or zero... this help ??

Jak

I'll let ye know tomorrow

Ta Slosh.

koloth

would you not be better writing a form or something to better handle input? Otherwise your using the sql statement to collect the input.

Jak

Someone suggested this;

Are you using VB to generate your SQL statement? If you are, you could input the entire list of numbers as a string, say strResponse = "12,247,300,499", then place that string directly in the SQL statement. For example
strSQLCmd = "SELECT * FROM TableWhatever WHERE FieldWhatever IN (" & strResponse & ")"

I'm gonna give it a try as well.

I'll let ye know if it works,

JAK.

Jak

Right here's what I'm doing now so ...

I open a new query design and slap in the follwing SQL as directed.

SELECT * FROM TBL_transaction WHERE horo IN
( funcResponse() )

Of course the program doesn't know what the function is .. so i define it in a Module as below ...

Public Function funcResponse() As String
funcResponse = InputBox("Enter Numbers for Query _
criteria", "Input Box")
End Function

....

Grand .. except my defining of the function yields a syntax error when run. Can anyone see why?

Cheers,

JAK.

koloth

I took out the _ and newline and the function ran fine.

Jak

Cheers Koloth,

That fixed it.

Unfortunately its still only accepting 1 number. For an input string should the numbers be separated by something?

I've tried commas and semicolons.


JAK.

OctaviaN

cant you loop the the input box to ask for a single number until the input box doesnt contain a number or defined char??

OctaviaN

soz but use an array for the above loop of 26 and increment the array number on each loop and then when you just hit enter or put in a char (if you use a group 0) thenit will stop the loop and you can access or pass the array fields whatever way you net to