Adding quotes to this filter 1

[Epsilon101]

Me!TxtUserID = Me!TxtUserID & ", " & Me!CboxUserName

TxtUserId is the unique identifier for each member of a team, and looks like this example = J3L.

CboxUserName is a combo box with 2 columns, first is the ID J3L and the second is the users full name.

The code above is used in an after update command. Where you look through the CboxUserName combo box for the right user, select them and the code above adds that to a text box.

I end up with the text box showing something like this:

J3L, L2H, T1S

It can then be used in an SQL filter on a report, except it will not work because i think i need quotes around each ID.

Does anyone know how to add quotes around these IDs without giving an error and asking for the parameter??

---------------------------------------
When im awake, i dont want to sleep, and when im asleep i dont want to wake.

 

[PHV]

Try something like this:
strFilter="[User ID] IN ('" & Replace(Me!TxtUserID, ",", "','") & "')"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Zukkster]

Try

Me!TxtUserID = "'" &Me!TxtUserID & "', '" & Me!CboxUserName & "'"

 

[Epsilon101]

Sorry the combobox can be selected as many times as someone wants so the formatting needs to happen when its added to the text box.

What would be the right sql statement for filter by

A Team ID : Say 1 or 2, then by a User ID: Say T1R or J3L, then by the date range example [Application Date] Between #02/05/2004# And #03/05/2004# all in the one statement?

Oh and i started the date range with an AND and use Mid(strwhere, 6) to remove the first AND from the statement.

Any help would be great cos i keep getting an operator error

---------------------------------------
When im awake, i dont want to sleep, and when im asleep i dont want to wake.

 

[PHV]

Something like this ?
If Len(Me!TxtUserID & "") > 0 Then
strwhere = strwhere & " AND [User ID] IN ('" & Replace(Me!TxtUserID, ",", "','") & "')"
End If
If Len(Me!TxtTeamID & "") > 0 Then
strwhere = strwhere & " AND [Team ID] IN (" & Me!TxtTeamID & ")"
End If

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Epsilon101]

Hi, I have tried your suggestion and its a lot better now but the report doesnt work properly as yet.

I have added in:

If Len(Me!TxtTeamID & "") > 0 Then
strwhere = strwhere & " AND [TeamID] IN ('" & Replace(Me!TxtUserID, ",", "','") & "')"
End If
If Len(Me!TxtUserID & "") > 0 Then
strwhere = strwhere & " AND [UserID] IN (" & Replace(Me!TxtTeamID, ",", ",") & ")"

End If

And in total the filter comes out as :

([Application Date] Between #05/01/2004# AND #05/01/2004# AND [TeamID] IN (2, 1) AND [UserID] IN ('L2H','J3L'))

I removed the quotes on the Team ID because they are just numbers to search on ID 1 and 2.

So it will look for any in those 2 teams and any with that user id and any made during that date range.

Except instead of working the report asks for a Team ID Parameter, and then the report loads with the correct filter in it if i dont enter anything in the Parameter box, but the report doesnt show any data in it, when it should.
So obviously messed up somewhere.

The help was great, any more would be useful almost got it done.


---------------------------------------
When im awake, i dont want to sleep, and when im asleep i dont want to wake.

 

[Epsilon101]

No need to reply to this, i have figured out why it was wrong, the code was complete. Thanks for the replace code didnt know about that.

---------------------------------------
When im awake, i dont want to sleep, and when im asleep i dont want to wake.