Query Parameters (1 Viewer)

[christm]

Hi

Help please for a newbie.
I'm trying to create a query which will have multiple parameters which may or may not be input. If not input it should bring back everything for that parameter but still take into account other parameters.

The following is my issue just now, I have 3 parameters:
Town, Age, Nationality

Have parameters set up to select a town, have > age and select a nationality.

My parameters were set up as:
[Forms]![frmStaffLocator]![cboTown] Or Like [Forms]![frmStaffLocator]![cboTown] Is Null

If on my parameter form I select a Town of 'New York' and select an age of > 25 but leave Nationality blank:
I'd expect to get only people with a Town of New York who are over 25 regardless of Nationality

However what I'm getting is:
All people with a town of New York who are over 25 PLUS all people whose Town is NULL PLUS all people whose Age is NULL.

There will be null bits of data throughout this DB so how do I get the query to only bring these back when the parameter form isn't filled in?

Thanks

Martin

 

[pr2-eugin]

Can you please show us the SQL of your Query, if possible?

 

[Tiger955]

Hi Paul,

i hope it does not matter that I jump in in this thread with the same question, as I am having the poblem, that to many selection fields which a get from unbound fields in a form the query is to complex and does not execute.

I tried this:

SELECT * FROM tblLeistungen
WHERE (((tblLeistungen.LeistungsDatum= " & vglDatum(Me.SuchDatum) & ") Or like(" & vglDatum(Me.SuchDatum) & " Is Null)) AND " _
& "((tblLeistungen.LfNummer=" & Me.SuchLfNr & ") Or Like(" & Me.SuchLfNr & " Is Null)) AND " _
& "((tblLeistungen.Veranstaltung=" & Me.SuchVAnr & ") Or like(" & Me.SuchVAnr & " Is Null)) AND " _
& "((tblLeistungen.PrOID=" & Me.SuchProID & ") Or like(" & Me.SuchProID & " Is Null)) AND " _
& "((tblLeistungen.SdlID=" & Me.SuchAlarmart & ") Or like(" & Me.SuchAlarmart & " Is Null)) AND " _
& "((tblLeistungen.Kennummer=" & Me.Suchkennummer & ") Or like(" & Me.Suchkennummer & " Is Null)));"

BTW: vglDatum is a function...

then I tried with a Stored procudure a the backend is a SQLserver.

strsql = "EXEC dbo.spAlfSuchabfrage " & Nz(DateTimeForSQL(Me.SuchDatum)) & ", " & Nz(Me.SuchLfNr, Null) & ", " & Nz(Me.SuchVAnr, Null) & ", " & Nz(Me.SuchProID, Null) & ", " & Nz(Me.SuchAlarmart, Null) & ", " & Nz(Me.Suchkennummer, Null)
 
[COLOR=blue][FONT=Consolas]but...[/FONT][/COLOR]
[FONT=Consolas][COLOR=#0000ff][/COLOR][/FONT] 
[FONT=Consolas][COLOR=#0000ff]...[/COLOR][/FONT]
[COLOR=blue][FONT=Consolas]WHERE [/FONT][/COLOR][COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=teal][FONT=Consolas]LeistungsDatum[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@SuchDatum[/FONT][/COLOR][COLOR=gray][FONT=Consolas]OR[/FONT][/COLOR][COLOR=blue][FONT=Consolas]casewhen[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@SuchDatum[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=black][FONT=Consolas]0 [/FONT][/COLOR][COLOR=blue][FONT=Consolas]then[/FONT][/COLOR][COLOR=gray][FONT=Consolas]Null[/FONT][/COLOR][COLOR=blue][FONT=Consolas]end[/FONT][/COLOR][COLOR=gray][FONT=Consolas]ISNULL)AND[/FONT][/COLOR]
[COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=teal][FONT=Consolas]LfNummer[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@SuchLfNr[/FONT][/COLOR][COLOR=gray][FONT=Consolas]OR[/FONT][/COLOR][COLOR=blue][FONT=Consolas]casewhen[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@SuchLfNr[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=black][FONT=Consolas]0 [/FONT][/COLOR][COLOR=blue][FONT=Consolas]then[/FONT][/COLOR][COLOR=gray][FONT=Consolas]Null[/FONT][/COLOR][COLOR=blue][FONT=Consolas]end[/FONT][/COLOR][COLOR=gray][FONT=Consolas]ISNULL)AND[/FONT][/COLOR]
[COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=teal][FONT=Consolas]Veranstaltung[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@SuchVAnr[/FONT][/COLOR][COLOR=gray][FONT=Consolas]OR[/FONT][/COLOR][COLOR=blue][FONT=Consolas]casewhen[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@SuchVAnr[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=black][FONT=Consolas]0 [/FONT][/COLOR][COLOR=blue][FONT=Consolas]then[/FONT][/COLOR][COLOR=gray][FONT=Consolas]Null[/FONT][/COLOR][COLOR=blue][FONT=Consolas]end[/FONT][/COLOR][COLOR=gray][FONT=Consolas]ISNULL)AND[/FONT][/COLOR]
[COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=teal][FONT=Consolas]PrOID[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@SuchProID[/FONT][/COLOR][COLOR=gray][FONT=Consolas]OR[/FONT][/COLOR][COLOR=blue][FONT=Consolas]casewhen[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@SuchProID[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=black][FONT=Consolas]0 [/FONT][/COLOR][COLOR=blue][FONT=Consolas]then[/FONT][/COLOR][COLOR=gray][FONT=Consolas]Null[/FONT][/COLOR][COLOR=blue][FONT=Consolas]end[/FONT][/COLOR][COLOR=gray][FONT=Consolas]ISNULL)AND[/FONT][/COLOR]
[COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=teal][FONT=Consolas]SdlID[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@SuchSuchAlarmart[/FONT][/COLOR][COLOR=gray][FONT=Consolas]OR[/FONT][/COLOR][COLOR=blue][FONT=Consolas]casewhen[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@SuchSuchAlarmart[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=black][FONT=Consolas]0 [/FONT][/COLOR][COLOR=blue][FONT=Consolas]then[/FONT][/COLOR][COLOR=gray][FONT=Consolas]Null[/FONT][/COLOR][COLOR=blue][FONT=Consolas]end[/FONT][/COLOR][COLOR=gray][FONT=Consolas]ISNULL)AND[/FONT][/COLOR]
[COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=teal][FONT=Consolas]tblLeistungen[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]Kennummer[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@Suchkennummer[/FONT][/COLOR][COLOR=gray][FONT=Consolas]OR[/FONT][/COLOR][COLOR=blue][FONT=Consolas]casewhen[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@Suchkennummer[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=black][FONT=Consolas]0 [/FONT][/COLOR][COLOR=blue][FONT=Consolas]then[/FONT][/COLOR][COLOR=gray][FONT=Consolas]Null[/FONT][/COLOR][COLOR=blue][FONT=Consolas]end[/FONT][/COLOR][COLOR=gray][FONT=Consolas]ISNULL)[/FONT][/COLOR]
[COLOR=gray][FONT=Consolas][/FONT][/COLOR] 
[COLOR=gray][FONT=Consolas]gives an error[/FONT][/COLOR]
[FONT=Consolas][COLOR=#808080][/COLOR][/FONT] 
[COLOR=gray][FONT=Consolas]and[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas]....WHERE [/FONT][/COLOR][COLOR=gray][FONT=Consolas]([/FONT][/COLOR][COLOR=teal][FONT=Consolas]LeistungsDatum[/FONT][/COLOR][COLOR=gray][FONT=Consolas]=[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@SuchDatum[/FONT][/COLOR][COLOR=gray][FONT=Consolas]OR[/FONT][/COLOR][COLOR=teal][FONT=Consolas]@SuchDatum[/FONT][/COLOR][COLOR=gray][FONT=Consolas]ISNULL)AND ....[/FONT][/COLOR]
[COLOR=gray][FONT=Consolas][/FONT][/COLOR] 
[COLOR=gray][FONT=Consolas]gives an error too, as I send this to the server through the PT-query:[/FONT][/COLOR]
[COLOR=blue][FONT=Consolas][/FONT][/COLOR] 
[COLOR=blue][FONT=Consolas]EXEC[/FONT][/COLOR][COLOR=teal][FONT=Consolas]dbo[/FONT][/COLOR][COLOR=gray][FONT=Consolas].[/FONT][/COLOR][COLOR=teal][FONT=Consolas]spAlfSuchabfrage[/FONT][/COLOR][COLOR=red][FONT=Consolas]'2014-12-02 0:00:00'[/FONT][/COLOR][COLOR=gray][FONT=Consolas],,,,,[/FONT][/COLOR] 
 
but dbo.spAlfSuchabfrage failed as the parameters might be empty and I can't define the script.
 
[FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][SIZE=2][FONT=Consolas][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]
[SIZE=2][FONT=Consolas][COLOR=#808080][SIZE=2][FONT=Consolas][COLOR=#808080][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][COLOR=#000000]So I am very interested in your proposal to solve this problem.[/COLOR][/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][COLOR=#000000]I need it for a "search-Form"[/COLOR][/SIZE][/FONT][/SIZE][/FONT]
 
 
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][COLOR=#000000]Thanks[/COLOR][/SIZE][/FONT][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2][COLOR=#000000]Michael[/COLOR][/SIZE][/FONT]
[/SIZE][/FONT][/COLOR][/FONT][/SIZE][/COLOR][/FONT][/SIZE][/COLOR][/SIZE][/FONT][/COLOR][/FONT][/SIZE][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]

 

[christm]

SELECT [Player Details].[First Name], [Player Details].[Last Name], DateDiff("yyyy",[Player Details]![Date of Birth],Now()) AS Expr1, [Player Details].Nationality, [Player Details].EU_Passport, [Player Details].Height, [Player Details].[Preferred Position], [Player Details].[Other Position], [Player Details].[Current Status]
FROM [Player Details]
WHERE ((([Player Details].Height)>=[Forms]![Player List Query]![Height]) AND (([Player Details].[Preferred Position])=[Forms]![Player List Query]![Position])) OR ((([Player Details].[Preferred Position])=[Forms]![Player List Query]![Position]) AND ((([Player Details].[Height]) Like ([Player Details].[Height])>=[Forms]![Player List Query]![Height]) Is Null)) OR ((([Player Details].Height)>=[Forms]![Player List Query]![Height]) AND ((([Player Details].[Preferred Position]) Like [Forms]![Player List Query]![Position]) Is Null)) OR (((([Player Details].[Height]) Like ([Player Details].[Height])>=[Forms]![Player List Query]![Height]) Is Null) AND ((([Player Details].[Preferred Position]) Like [Forms]![Player List Query]![Position]) Is Null));

This is my current query - it's actually now based around a sports database.
So you can search for height or position or both.

Hope it makes sense.

Martin

 

[christm]

Hi

Just wondering if anyone had any ideas?

Thanks

Martin

 

[ConnorGiles]

Hi christm

would you be able to send me a sample copy of your DB, (2007 access format please - repair and compact before sending)

 

[christm]

Sure, is there a way I can send it to you directly rather than on the board?

 

[ConnorGiles]

I wouldn't recommend it. Email file attachment is too small.

 

[ConnorGiles]

I'm not too sure if you can PM me the attachment, try that.

 

[christm]

It's ok - I've taken some data out so here is bones of db - repaired and compacted.

Basically there on the menu there is a button called ' Player Details - by Parameters'. This called a query called 'Player Details Query' which in turn calls a report 'Players by Position'

It's the parameters that are input on the form and referenced by the query that I can't get to work.


Thanks

Martin

 

[ConnorGiles]

You have left out the Queries Martin - there is no file source - only the reports.

 

[christm]

How can that happen? When I open the DB here (the same one I uploaded) I see the queries:

 

[ConnorGiles]

ah never mind - it was filtered

Mind blank :s

 

[christm]

Cheers

 

[christm]

Might have kind of solved this but not sure in best way.
For the 'Position' field where it will either be an exact match to the position selected or be null (in which case it should bring back all positions), I've done:
Like Nz([Forms]![Player List Query]![Position],"*")

For the 'Height' field where is is a 'greater than' the height parameter or if parameter is null, bring back all heights, I've done:
>Nz([Forms]![Player List Query]![Height],0)

Only issue on this is assuming that everyone has a 'height' in the db as any null heights won't come back?

Martin

 

[ConnorGiles]

In a new column, Try adding this to specify age,

Working on the rest.

Age: DateDiff("yyyy",[Date of Birth],Now())

 

[christm]

Thanks. It's weird,
In the query I'm using DateDiff("yyyy",[Player Details]![Date of Birth],Now()) which is successfully bringing back and Age value.

However if you go into the main Player Details form (Player Details1), using the exact same expression I get a '#Name?' error in the Age field.

A more complicated thing I'd love to do but don't know if it's possible, this will make more sense to Connor likely as he's seen DB now.
The Player List query and report brings back a list of players meeting criteria - some of these players may have a record for their unique ID in a different table if we have a scout report on them - is there anyway on the player list report to have a column which puts out an identifier if that player has reports against them, and ideally, clicking on that identifier would produce a different report just for that player?

Thanks

Martin

 

[ConnorGiles]

Doesn't your Player Database already do this?

It creates a specific player report.

Searching for scout reports (criteria would be : Is Not Null)

P.s. this would require a relationship between the tables

 

[christm]

Yeah it does - I was just looking to integrate it into the report
For example, say we use the report to bring up a list of all players between 18 and 22 whos contract is expiring this Summer.

That will bring up a list of players, but it'd be nice to easily know which one's have already been scouted (and bring up their individual scout report with a click) and which one's haven't been scouted yet.

Thanks

Martin

 

[christm]

Also, I'm trying to do a parameter on 'Contract End Date' - where it will bring back all people with a contract end date of < the parameter - however I want the user to be able to define at this stage whether or not to bring back people with a blank contract end date.

To do this I have added a checkbox called Free Agents - if checked, it should also bring back those without a contract end date - and if the parameter is blank it should also bring back everyone.

To try and facilitate this I have done the following criteria in the query:
IIf([Forms]![Player List Query]![Free Agents]=True,(Nz([Contract End Date],'31-Dec-4712'))<=[Forms]![Player List Query]![Contract Expiry] Or [Player Details]![Contract End Date] Is Null,(Nz([Contract End Date],'31-Dec-4712'))<=[Forms]![Player List Query]![Contract Expiry])

However I am getting a message saying 'The expression is typed incorrectly or it is too complex to be evaluated'

Any ideas?

Thanks

Martin