filter text on report from list box selections

[Rachael]

Hi All,

I have a diary/job planner section in my database that has a field for date, blocks, diary entry and completed by. In the blocks field (text) you can list A, B, C or A, C, or A, depending on what blocks the job was completed on. (Hope I'm making sense). Anyway, what I want to be able to do is preview my report listing the diary entries that correspond to selections in a list box.

So the user can come along and choose form a list box blocks A and C, click a command button that will open the report and filter out records that have A and C or just A or just C in the Blocks text field.

I think I have to use

In (Like "*"& something or other &"*") but don't quite know how to put it all together and I don't think the In likes the Like in the In?????

Any ideas would be much appreciated, thankyou.

Rachael

 

[WayneRyan]

Rachael,

See this example in the Sample DB Forum:

http://www.access-programmers.co.uk/forums/showthread.php?t=72319

Wayne

 

[Rachael]

Hi Wayne,

Hope your feeling better since last time we spoke. Thanks for the reply, unfortunately I know how to do the example you posted. What my problem is that I have only one field called blocks that lists the block names for each diary entry

ie

Record 1 Block field is A,B,C
Record 2 Block field is A,
Record 3 Block field is A,C,
Record 4 Block field is B, and so on

I want to be able to use a multi-select listbox to choose which blocks we want to report on ie if in the listboc blocks A and C are chosen then records 1, 2 and 3 would be on the report. If just block B was chosen in the listbox then records 1 and 4 would be on the report. If blocks B and C were chosen then Records 1, 3 and 4 would be on the report. Get my drift.

As you have realised from the last time you helped (which I really appreciated) was that I'm a bit hopeless at writing code.

This is my very, very feeble attempt, it doesn't work but you might see what I was trying to do.

Dim stDocName As String
stDocName = "Planner"


Dim vItm As Variant
Dim stWhat As String
Dim BlkData As String
Dim stSQL As String
Dim loqd As QueryDef
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb

If LstData.ListIndex = -1 Then
MsgBox "Please choose 1 or more blocks from the list.", vbInformation, "Vineyard ARKive"
Exit Sub
End If
Set rst = dbs.OpenRecordset("Select * From Planner Where Completed = Fasle")

stWhat = "": BlkData = ","
For Each vItm In Me.LstData.ItemsSelected
If rst!Blocks = "Like "*"&Me.LstData.ItemData(2)&"*"" Then
stWhat = stWhat & rst!PlannerID
stWhat = stWhat & BlkData
End If
Next vItm
Me!BlkData = CStr(Left$(stWhat, Len(stWhat) - Len(BlkData)))
rst.Close
Set rst = Nothing

Set loqd = CurrentDb.QueryDefs("PlannerRptQry")
stSQL = "SELECT [PlannerQuery].[PlannerID], [PlannerQuery].Date, [PlannerQuery].Entry, [PlannerQuery].Blocks, [PlannerQuery].Completed"
stSQL = stSQL & "FROM [PlannerQuery] WHERE ((([PlannerQuery].[PlannerID]) In (" & Me!BlkData & ")) ORDER BY [PlannerQuery].Date;"
loqd.sql = stSQL
loqd.Close

DoCmd.OpenReport stDocName, acPreview

I had this idea that if I could get the id number of the records that have the Block listed in its block field (each one that matched) then I could use the id numbers in the 'In' part of the sql bit.......but as you can see (and don't laugh) i'm pretty hopeless at code.

Thankyou once again and kind regards,
Rachael

 

[WayneRyan]

Rachael,

Wow! It looks like the data structures have changed a lot!

If you're faced with multiple data elements "crunched" into a field like that,
then your only choice is to parse it with a VBA function that you could
include in your query(s).

Need more info here ...

Wayne