Query Criteria

[mark curtis]

Dear all,

I have a report run from a query but now I need filter the query using list box selections on a form. The report can be filtered by Freq, Task, Location and Type.

I have a form with each of the above as lists boxes and i want the user to have the choice of selecting:

eg:

I select 4 Freq and 3 Tasks but I do not want to filter by Location or Type? and that is where my problem is!!

I know that I can reference the listbox in the query criteria but if I make no selection then the query will fall over also I need to return criteria greater than 255 and need to do some string manipulation.

If I could code this in SQL it would be much simpler but I do not know how to!

Any help as always much appreciated
Mark

 

[crosmill]

I'd write it in VBA, you can test for Null values and create a SQL statement from that.

Dim strSQL as String

strSQL = "SELECT * FROM Table WHERE "

If me.freq <> Null Then
strSQL = strSQL & "freq LIKE '" & me.freq & "'"
End if

It takes a bit of jiggery pokery when your concatenating a few SQL filters to the string but it's essentially not that difficult.

good luck

 

[mark curtis]

Crosmill,

Thanks for the reply and any examples of how to build up the string for the SQL Filter and then how to return the fields I require in the recordset would be ideal.

I need to learn how to loop through listbox selections, turn them into criteria and then return the recordset and create the report based on the recordset.

Can you point me to any examples?

Thanks
Mark

 

[crosmill]

I don't have any examples to hand, and if you've not done much VBA I suggest you do it in small stages so you can see whats working and whats not, before puting all together.

Create an OnClick event from a button that runs the code to build the SQL statement, then display the result in a memo box on the form.

Me.MemoBox = strSQL

At the end of the code, it might also be useful, if the code is coming out wrong, to input the SQL as your going along

Me.MemoBox = Me.MemoBox & " Part2=" & strSQL.

Opening Recordsets, I find, is always a pain in the arse, basically because you can't see what your retuning if anything. A small tip, I've never been able to get the RecordSetCount to work don't know why, but the most I ever get out of it is -1 or 0, maybe that's how it's supposed to work??
The solution to this I found was to just increment a number as you loop throuhg the set.

Dim count as integer

While NOT RecordSet.EOF
count = count + 1
RecordSet.MoveNext
Wend


As for applying all this to a record, well, I've never done that before, but I'm sure it can be done, you can't do anytihng in Access you can't do in VBA (I think) I don't think it'll be too difficult, it'll just be a case of calling the record through DoCmd or Open or something.

Anyway I suggest you start by building the SQL statements, get that sorted then move on to creating RecordSets, as these are a little more tricky.

Post back if (when) you get stuck, don't forget to post your code, I've been dugging RecordSets for the last six months so I should pick up any glareing errors quite quickly. Incedently the majority of them have been in ASP, which is why I don't have any examples that would be of use to you. But hey, when you've got this down, you'll be able to roll out the ASP as well (if you want)

HTH

 

[Jon K]

"I need to learn how to loop through listbox selections, turn them into criteria and ... Can you point me to any examples?"


Take a look at this thread. There is a demo DB using two multi-select list boxes.
http://www.access-programmers.co.uk/forums/showthread.php?threadid=35240

Hope you can adapt it to suit your needs.