Using a form to specify a query 1

[luccas]

I have a query with a field called Category with text records, one record example: "3 4 SW SRV CCR GR FAF FCO FCR SCR FGR". This example shows 11 categories. I want to use a form to specify up to 10 criteria for this Query. The output will be used to make mailing labels. any help much appreciated.

 

[Trendsetter]

Could you explain in greater detail, I would like to help but do not understand how your data is structured or what you are trying to achieve.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[luccas]

I achieve the desired result if a
put: Like "*1*" or Like "*FGR*" directly into the
Query. But when I refer to the Query from my Form-
[Forms]![frmSelection]![Text0] I can't find anything to give the same
result.

 

[Trendsetter]

Try using:
Like "*" & [Forms]![frmSelection]![Text0] & "*"
This would take the content of Text0 and identify it anywhere in the string being searched.

So if you were looking for CCR then CCR in the Textbox would find it wherever it existed in the string.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[luccas]

Thanks this is what i needed . It works great!

 

[luccas]

I found one problem - if I have null values in my selection form -frmSelection -it shows all recordss. How can I handle null values to give no records?

 

[Trendsetter]

You could try:
Like "*" & [Forms]![frmSelection]![Text0]

I'm not sure that this will do what you want but it's worth a try.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[luccas]

I tried this:
Like "*" & [Forms]![frmSelection]![Text0]

This doesn't handle the nulls and doesn't show the fields with right hand wild cards.

 

[Trendsetter]

Try this:

Like "*" & [Forms]![frmSelection]![Text0] & "*" And Not Is Null

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[luccas]

I tried your suggestion Like "*" & [Forms]![frmSelection]![Text0] & "*" And Not Is Null and if I have a null value in my selection form I get all records. Doesn't seem to effect it.

 

[Trendsetter]

You can eliminate the Nulls before the recordset gets to the form by setting the criteria of the query feeding the form to exclude the Nulls.
Criteria.............Not Is Null
This would ensure that the records you working with will be selectable from the criteria we have already had working.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[luccas]

You are right the nulls in the query are handled with this Not Is Null. I don't think I've been clear on the problem I am having. I am using frmSelection to select the Category field on the Query. I have 2 selection choices on frmSelection , if I make both selections everything works. If I Make one selection and leave one selection blank all records except the nulls show up.(as we are asking for). If I leave the selection blank I don't want to see any records. Or if I leave frmSelection all blank (null) I want no records. Your time greatly appreciated.

 

[Trendsetter]

I honestly think we have exhausted the functionality of selection available in Access. I think you need to bear in mind what are the design criteria of the functions within Access and the possibility that they cannot meet your full wish list.
As a programmer there are many, many things I would an application to do and for me to be able to implement but at the end of the day I have to accept the limitations of the system and the greater limitations of me as a developer.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk