Multiple Parameter Query Issue

[kong_haakon]

Hello!

Ive just recently started learning a bit of access, so I apologize if my questions is dumb

Im building a real simple database with one table. It is a database of existing and potentially new employees. I run a musicschool so im often in the need of searching for substitutes and new staff members. Figured I might as well organize them, and make this search easier.

The database is going on our sharepoint site, and therefore Im using the web-database functionality in access 2010.

My table consist of 17 fields, firstname, lastname, adress, phone, attachment, etc, but there are three fields that come in to play when i try to make the database useable for quick searching by a query.

Ive made a form to input the parameters for the query.
One is called [instrumentparameter], and one [areaparameter]
All of this is tested and working.

The problem is that in the query-designview, no matter what I type in the criteria field, the query always returns all registers in the database when i input information to both parameters.
If i search Instrument and leave area blank, it works. If i search for Area and leaves instrument blank, it works. It seems to me like I cant get it to search through my table for hits with both parameters at the same time.
What I normally want to do is to define both parameteres and make the query show all hits in that specific area AND on that specific instrument.

Ive tried a number of combos in the designview but non seems to do the trick.
Again; im sorry if this is a noob-thing, but my head is starting to hurt from banging it against the wall the last couple of days.

Any help will be highly appreciated!

 

[AccessMSSQL]

can you post your query syntax?

 

[kong_haakon]

can you post your query syntax?

Thanks for your quick response!

Shure i can post this if you could tell me where to find it?

I've not done any code on any of this, since this is to be published on sharepoint.

In the querydesignview i have typed in [area] in the area column, and [instrument] in the instrument column, and defined the two as parameters as well. Then in the next row (Or-criteria row) i have just [area] in the area column, and then a Third row with just [instrument] in the instrument column.

I wish i could explain better, but a quick to where to find the query syntax and I'll put it up here.

Thanks again for your response!

 

[AccessMSSQL]

Here is a thread from this forum that may help:

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

 

[kong_haakon]

Here is a thread from this forum that may help:

Actually, I have kind of the opposite problem. If I type just one of the prompted parameters, it works like it`s supposed to. But when I type in both parameters, it just returns all of the databases records.

Thanks again for taking the time!

 

[Brianwarnock]

In the querydesignview i have typed in [area] in the area column, and [instrument] in the instrument column, and defined the two as parameters as well. Then in the next row (Or-criteria row) i have just [area] in the area column, and then a Third row with just [instrument] in the instrument column.

This puzzles me as you said in your first post that the control on the form was
Instrumentparameter and areaparameter .

As the parameters are coming from a form you refer to them as
Forms!formname!controlname

Also as you want to AND the parameters they should be on the same criteria row.

It is best to enter

Forms!formname!controlname or forms!formname!controlname is null

Then you can leave a parameter empty to just select on the other.


Brian

 

[kong_haakon]

Hi Brian and thanks for taking the time!

Yes you are right, I was a bit quick on my first post there. What I've entered is [Instrumentparameter] and [Areaparameter].
They were initially called what I first wrote, but I've changed the name of the parameter so that it would not be the same as the column.

I've tried linking the text from the form as you've described, but that didnt work. However I've managed to get the form to work anyway.

My inital thoughts was also that AND would solve this when the two parameters where on the same criteria row, but that doesnt seem to do the trick unfortunately.

I'll give it a few more tries and if I cant figure it out I'll post my database here. You guys can probably smell what I'm doing wrong as soon as you see it

Thanks again!

 

[Brianwarnock]

If you are posting a DB you will probably get more responses from an MDB ie per 2007 format as that is what most are on .

However you could try switching from design view to SQL view of your query and copy and pasting that to the forum, sometimes the problem can be solved with that.

Brian

 

[kong_haakon]

Hi again!
I would love to post the sql code, but SQL-view is not an option when making webdatabases so im not really shure where to find it..
I`m sorry!
I've tried to link the parameters directly to the form as you mentioned Brian, but no luck.

If you see the attached photo, thats where I cant figure out what I'm doing wrong..
Notes to the photo:
- område is norwegian for Area
- As you can see i have two columns called instrument and instrument 2, wich serve almost the same purpose. Sometimes some of the candidates that I want to put in to this database masters two instruments.
Anyway they get their parameters from the same textbox in the form, and that works like a charm..

I've tried both the AND operator and typing the parameters in both columns but on the same critera row. None of wich make any difference..
This is frustrating! It feels like I'm so close! And i have a feeling that it`s just some silly mistake that has got me stuck on this.

I bit of a drag that this has to go on sharepoint i realize now, just because of the webdatabases limitations. If I cant get this to work, i'll might have to start all over again..

Any thoughts and ideas would be highly appreciated!

 

[kong_haakon]

Found aworkaround to see the syntax via the debugging window.
Heres the syntax that is in use:
SELECT KontakterT.Fornavn, KontakterT.Etternavn, KontakterT.Telefon, KontakterT.Epost, KontakterT.Område, KontakterT.Instrument, KontakterT.Instrument2, KontakterT.Status
FROM KontakterT
WHERE (((KontakterT.Område)=[områdeParameter]) AND ((KontakterT.Instrument)=[InstrumentParameter]) AND ((KontakterT.kontaktes)=Yes)) OR (((KontakterT.Område)=[områdeParameter]) AND ((KontakterT.Instrument2)=[InstrumentParameter]) AND ((KontakterT.kontaktes)=Yes)) OR (((KontakterT.Område)=[områdeParameter]) AND ((KontakterT.kontaktes)=Yes)) OR (((KontakterT.Instrument)=[InstrumentParameter]) AND ((KontakterT.kontaktes)=Yes)) OR (((KontakterT.Instrument2)=[InstrumentParameter]) AND ((KontakterT.kontaktes)=Yes));

 

[Daz]

I am working on a simalar problem and am trying to get the correct format for doing a parameter with a Null value. I tried putting as an "or" and it did not work.

 

[Brianwarnock]

Sorry kong been out all day, the correct format for this time of problem is as follows

Where
(fieldname1= parameter1ref or parameter1ref is null)
And (fieldname2 = parameter2ref or parameter2ref is null)

Parameterref can be of the form
[enter parameter]. Ie a pop up
Or a form reference as in one of my previous posts

This code allows for all parameters to be entered or any number to be left empty.

Brian

 

[kong_haakon]

Sorry kong been out all day, the correct format for this time of problem is as follows

Where
(fieldname1= parameter1ref or parameter1ref is null)
And (fieldname2 = parameter2ref or parameter2ref is null)

Parameterref can be of the form
[enter parameter]. Ie a pop up
Or a form reference as in one of my previous posts

This code allows for all parameters to be entered or any number to be left empty.

Brian

No worries Brian!

AllRight! Thanks so much!

This is moving in the right direction! Cant thank you guys enough for taking the time!

So now I've got the query running like it's supposed to on it's own!
My next problem now is that the same issue that I had in the query itself, now has "moved" to the subform used for defining the parameters.
If i type in both parameters in the searchform - it does exactly what I want it to. But when I type inn just one of the parameters, and leave the other blank, it returns nothing.
Wich leads me to think i need to do kind of the same thing, but somewhere else as well!

It seems like the linking between the search form and the query works, but not for just one of the parameters at the time..

Any ideas?

 

[Brianwarnock]

The parameter Is Null takes care of the empty parameter situation, this applies whether the parameter is from a pop up or a form

The example below is for a pop up prompt.

WHERE (((KontakterT.Område)= [områdeParameter] or [områdeParameter] Is Null )
AND ((KontakterT.Instrument)= [InstrumentParameter] or [InstrumentParameter] Is Null)

Brian

 

[kong_haakon]

Yeah I've tried that. But still the same..

The parameters is collected from a navigation sub form, but i can not for my life make it accept only one of the parameters.

And since it works with both parameters, I assume I've done the form references correctly. It is, after all, collecting information!

 

[Brianwarnock]

I have not done that so cannot help anymore, it would appear that it cannot collect and/or foreward a null parameter, but I am speaking from ignorance, sorry, perhaps a fresh thread on this paticular issue might prompt somebody else to take a look.

Brian

 

[kong_haakon]

I have not done that so cannot help anymore, it would appear that it cannot collect and/or foreward a null parameter, but I am speaking from ignorance, sorry, perhaps a fresh thread on this paticular issue might prompt somebody else to take a look.

Brian

Ok!

Thanks for all your help anyway Brian!

 

[Brianwarnock]

I wonder if the use of Like would work, I never usually recommend the use of Like in these circumstances as it should only be used when partial searches are needed as there are issues such as

1. You may not be able to restrict the search to a particular value
Eg bass would find bassoon
2. You will not exploit the use of indexes for efficiency

3 Like "*" does not return null fields

If none of these are an issue then try

Where fielda like "*" & [parameter1] & "*" and Fieldb Like etc

Brian

 

[kong_haakon]

I wonder if the use of Like would work, I never usually recommend the use of Like in these circumstances as it should only be used when partial searches are needed as there are issues such as

1. You may not be able to restrict the search to a particular value
Eg bass would find bassoon
2. You will not exploit the use of indexes for efficiency

3 Like "*" does not return null fields

If none of these are an issue then try

Where fielda like "*" & [parameter1] & "*" and Fieldb Like etc

Brian

Hi again Brian, and sorry for my late response!
Thanks for your percistence!!

I`ll try this asap and get back to you!

 

[kong_haakon]

Aaahhh!! Brian you genius!! That did it!!

Thanks so much! Now it works and I'm a happy man!

Really appreciate the help offered!! Thanks again!