Bypass Query Parameters

Hi,

I wonder if someone might be able to help me please.

I'm fairly new to MS Access, and everything I know is self taught so please
bear with me.

I have put together a query which consists of a few fields. Some of these
have parameters which I've added to them. This in turn feeds a report.

The problem I have is that I don't always want to key in the parameters i.e.
I want to see all the data instead of it being filtered.

I have seen a very similar post on this site, with a possible solution, but
for the life of me can't get it to work i.e. where I can just click 'Go' and
it gives me all the data, but by typing in the parameters it give me the
filtered data. I just wondered whether someone could provide me with a bit of
guidance please.

The code is:

Like IIf([Year] Is Null,"*",[Year])

Where 'Year' is the field name. I have then changed this to 'Surname',
'Forename' and so on and so forth for my other fields and I have typed this
into each criteria line for the different fields.

I get all the data when I don't key the parameters in, which is obviously
what I want, but when I do I can't get the filtered data.

Any ideas please?

Regards

Chris

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200801/1

Re: Bypass Query Parameters

In your original query, go to the sql view. It will say something like

Select * from tblx where somefield=[yourparam]

All you need to do is change the = sign to the word Like (and have
spaces between -- fieldname -- Like -- [Parameter])

Select * from tblx where somefield Like [yourparam]

Now when the parameter box comes up you can type in part of a param like

bill*

to get just everyone named Bill or you can type just

*

to get everything

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Bypass Query Parameters

Rich,

That's great.

I've been working on this for a while now so I really appreciate your help.
I'm not that good at writing in SQL, but using the design mode I've got it to
work:

It works a treat.

Thanks very much for your time and help.

Regards

Chris

Rich P wrote:
>In your original query, go to the sql view. It will say something like
>
>Select * from tblx where somefield=[yourparam]
>
>All you need to do is change the = sign to the word Like (and have
>spaces between -- fieldname -- Like -- [Parameter])
>
>Select * from tblx where somefield Like [yourparam]
>
>Now when the parameter box comes up you can type in part of a param like
>
>bill*
>
>to get just everyone named Bill or you can type just
>
>*
>
>to get everything
>
>Rich

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200801/1

Re: Bypass Query Parameters

The above method works OK so long as you have values in those fields.
If the field is null, the * will not return that record in the search
results. You can get around that by making sure none of the fields are
null. If the field must be blank, you can set the field to accept zero
length strings and then make sure that all blank fields are set to "".

That being said, it is still a rather crude way of doing it. If you
have a lot of records, it can also take longer for the query to run as
you have unnecessary criteria in there. If you have a lot of records,
or if you have users who are not computer savvy, I find it best to
create a form that has the search fields in it along with a 'Search'
button. The user fills in the form and then clicks search. In that
search button, you 'assemble' the SQL statement into a string based on
which fields the user indicated they want to search by, then set that
string as the reports record source. If you don't know SQL, you can
copy and paste it from the query builder in SQL view to help you.

Re: Bypass Query Parameters

Andy,

Thanks for this, do you know I found this to be the very problem today, and
as you suggested I added 0's to those fields that didn't contain data.

However I want to learn more so I think I'll have a go at the form, it may
take a while because of other work commitments but I'll get back you to you.

There is another thing that you may be able to help me with though if that's
ok, it's something I came across today.

I have a date field where usually I would set a date partamter for the user i.
e. Between[Enter the first date] And [Enter the last date] so they can put a
from and to date in to filter the records.

Using the same prinicpal of the 'Like IIf' coding, is there anyway that I can
incorporate the two i.e the user has the option of bypassing the parameters,
but entering them if they need to filter the data.

I've been trying all afternoon, moving the Between and And around the coding,
but I just haven't got anywhere.

Any ideas please.

Kind regards and thanks

Chris

Andy_Khosravi@bcbsmn.com wrote:
>The above method works OK so long as you have values in those fields.
>If the field is null, the * will not return that record in the search
>results. You can get around that by making sure none of the fields are
>null. If the field must be blank, you can set the field to accept zero
>length strings and then make sure that all blank fields are set to "".
>
>That being said, it is still a rather crude way of doing it. If you
>have a lot of records, it can also take longer for the query to run as
>you have unnecessary criteria in there. If you have a lot of records,
>or if you have users who are not computer savvy, I find it best to
>create a form that has the search fields in it along with a 'Search'
>button. The user fills in the form and then clicks search. In that
>search button, you 'assemble' the SQL statement into a string based on
>which fields the user indicated they want to search by, then set that
>string as the reports record source. If you don't know SQL, you can
>copy and paste it from the query builder in SQL view to help you.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200801/1