Query Criteria from a Form (1 Viewer)

[taylorhouse]

Hi

I have a Report form where i can select selection criteria for a query. The form name is frmreport and the field is fldmachine. I also have a check box that enables and diasbles this field.

In the query criteria i have

"Like Forms!frmreport.fldname"

when i select the type of machine and run the report i get what i want. But the problem comes when I dont want to filter and just see all entries.

When i disable the fldmachine field i pass
"*" which equates to "Like "*"" in the query criteria and this then displays all the entries with content in the machine field but does not display the records with Null in the machine field.

If i enter in the criteria or field of the query "Is Null" then it works or if i use "Like Forms!frmreport.fldname Or Is Null" this works

But if i try and pass either "Is Null" or "Or Is Null" it seems to be seen as text and is looking for those words and not seeing it as i need

I am sure there is a way of passing this information to the query without it taking the text literally but am stuck and need your help

Thanks
Darren

 

[taylorhouse]

I am still having trouble with this.

What i need to know is can you pass

Is Null

Or Is Null

From a field to criteria of a query

When i am trying to

when the check box is True i have the folowing Forms!frmreport.fldname = "requiredtext"

This is then the statement i have in the query

Like Forms!frmreport.fldname

This works, but when i dont want to check the field in the query and the check box is false i have Forms!frmreport.fldname = "*"

This also works but does not list the records where the field is Null

So I would like to pass Forms!frmreport.fldname = "*" Or Is Null

I have tried a number of combinations but the query interperates what i send as the text to actually look for

I have tried """*"" Or Is Null"

I have also tried passing about field to the criteria or section of the query which contains " Or Null"

But again it is seen in the query as "Or Null" and not Or Null

ANyone any ideas

Cheers

 

[namliam]

This is why you want to tailor build your SQL in VBA to suite your needs. Because basicaly you dont want to search the field at all, instead you are searching for something.

Now you can make a workaround
YourField like YourFormField OR YourFormField = "*"

but this gets very messy the more fields your searching.

Instead use a "Search form", find some samples for that on this forum.
You will be omitting the "Search all" or "Like "*"" options all together not only resolving this problem, but also improving performance.

Good luck !

 

[taylorhouse]

Thanks will take a look. In Access there is always a better way

 

[taylorhouse]

HI I have had a look could someone point me in the direction of a good example which then presents the results in a report

 

[namliam]

http://www.access-programmers.co.uk/forums/showthread.php?t=188663&highlight=Search+form
http://www.access-programmers.co.uk/forums/showthread.php?t=121973&highlight=Search+form
http://www.access-programmers.co.uk/forums/showthread.php?t=99353&highlight=Search+form

One of the above should be able to help you along.

 

[taylorhouse]

Thanks Namliam

I have had a look at those examples and they seem to be doing it the same way i am.

Do you have an example of doing the query in vba as i am still struggling to find one

Thanks