Dynamic Queries! 1

[Trevahaha]

Okay, here's some background on my database.
I have distribution lists: lists of UserID's (more than one ID in list)
I have reports that take any of 150 queries.

I would like to dynamically take the existing queries and by the user choice limit the reports to contain only the UserID's that are in the list they choose.

I know how to do this the long way and write out SQL for each query -- but is there a simple way that I can write one code that says something like:

"Report's existing query" Where UserID = "UserID's from list"?

Please let me know if you need more clarification!

 

[digiduck]

you should be able to use a parameter, like the following example from yours

WHERE UserID=[@UserID]

then you just fill the parameter with the value when you submit the query.

 

[Trevahaha]

Well, what I ended up doing was:

Do Until dist.EOF
UserIDSQL = UserIDSQL & dist!UserID
dist.MoveNext
If Not (dist.EOF) Then
UserIDqry = UserIDSQL & " OR "
End If
Loop
UserIDSQL = UserIDSQL & &quot"
dist.Close

If Not IsNull(SQLqry) Then
SQLqry = SQLqry & " AND " & UserIDSQL
Else
SQLqry = UserIDqry
End If
End If
DoCmd.OpenReport rptName, acViewPreview, , SQLqry, , strArguments


This way it sends the report the criteria of all users.. just think it may be slower than another way.

 

[PGTurner]

Assuming users pick the IDs they want from a pick list, the following technique will build an IN(X,Y,Z...) list from the picked selections.

I have built it with a picklist ( in my case called PhyPickList). And a command button that builds the query and presents it once the list is picked.

Onclick event for button
...
    InList = CreateInListFromPick(Me.PhyPickList)
    If InList = "IN )" Then
      MsgBox "No physicians selected"
      Exit Sub
    End If

Then build the query using the selections picked from a listbox.

            stDocName = "SELECT DISTINCT PerfPhysician, AccountNumber "
stDocName = stDocName & " FROM ChargeTransactions WHERE PerfPhysician " & InList 
stDocName = stDocName & " ORDER BY TxnSerDate;"

The function that builds the IN() clause from the picks.

Private Function CreateInListFromPick(PickList As ListBox)
On Error GoTo Err_CreateInListFromPick
Dim i As Long
Dim InList As String
' Called by
'MsgBox CreateInListFromPick(Me.PickList)
InList = "IN ("
For i = 0 To PickList.ItemsSelected.Count - 1
  InList = InList & "'" & PickList.ItemData(PickList.ItemsSelected.Item(i)) & "',"
Next i
InList = Mid(InList, 1, Len(InList) - 1) & ")"

CreateInListFromPick = InList

Exit_CreateInListFromPick:
    Exit Function

Err_CreateInListFromPick:
    MsgBox Err.Number & ":" & Err.Description
    Resume Exit_CreateInListFromPick
    
End Function

 

[Trevahaha]

Ahh.. well.. no.. they choose a listID, the listID refers to a table:
tblDistributionUsers
DistID UserID
1 205
1 1026
2 744
2 205

etc...
but the query run has to take the distribution members (UserID's) and apply them to the query.... so let's say I'm running a report that I want to print name tags for certain users. I choose a distribution list that contains 2 members (DISTID #1, for example).. I want to apply that to the report so that it will print where USERID = 205 or 1026.

I'll give you a star anyways cause you have a good example!

Thanks

Trevor

 

[Trevahaha]

PGTurner -
oh.. i see more of what you meant. I've never used the IN( ) function.. I'll have to check that out. Thanks again! Maybe it will be more efficient than what I did above.. what do you think?