AccessReports

[NeedHelp]

I want to pass some parameters to Access reports. I am setting a string = to an sql string. I then set the recordsource = to this string. What if I dont know how many parameters I want to pass from my VB form? ie)They can choose what Employee #'s they want the report based on by typing them into a text box. I have no idea how many they will choose?

[BrewGuru99]

It's unclear what you mean, but I think you intend on building your sql string based upon what the user inputs.

You might need to use a loop to cycle through all the options selected, generating the string as it goes.

Example (NOTE- If you want to have them type the Employee#'s in, they would have to use some structure so that you could grab each number from the one string):


DIM POSTo as Long, POSFrom as Long
DIM strTempSQL as string, strSQL as string

Text1 = Text1 & " "
POSFrom = 0
POSTo = InStr(Text1, " ")
Do While POS <> 0
If strTempSQL <> "" then
strTempSQL = strTempSQL & " OR ([Employee#] = " & mid(Text1, POSFrom + 1, POSTo - 1) & ")"
else
strTempSQL = "([Employee#] = " & mid(Text1, POSFrom + 1, POSTo - 1) & ")"
End If
POSFrom = POSTo
POSTo = InStr(POSTo, Text1, " ")
Loop
strSQL = "SELECT * FROM Employees WHERE " & strTempSQL




From here you would open your report and pass the sql to the recordsource. You'll have to be a little creative and make the report capable of accepting and correctly displaying the variety of possibilities.

BrewGuru99