[zepp]

Firstly, I know, I know, I don't like Access much either. It is what I have to use and support at work until I can manage to get a proper database. I will probably still use Access for a frontend though because it's already standard on all of our computers....anyway.


I am trying to build an SQL query that uses parameters from a form. I know that this is possible because I did this on a practice database today.

It is a bit daunting because there are over 10 fields/parameters that the query needs to handle. It also needs to be able to handle null fields, because the user will not always use every field during a search.

The problem is that the parameters in the fields do not appear to be affecting the query at all, even though everything is pointed to correctly in the SQL query.

I'm not at work, so I can't just copy and paste the query, but I can tell you that I am using all OR statements. This is because I do not want one parameter to be dependent on another parameter in any way. It simply needs to filter out data from the table based on the query and display it.

So, does anybody here with experience in this sort of thing have any insight into what I can check? I'm a bit perplexed, because I have an identitical, but much smaller, query working perfectly in another database.

 

[Max]

Just write the SQL query out manually?

 

[zepp]

I did.

To catch up, me and Max discussed this on fb chat, and the query itself doesn't seem to be the issue.

The query is formatted fine....but the access form doesn't want to send its values I suppose.

 

[105]

If they are all OR, then if any one of them is true it will return that data.

You need to use ISNULL()s and ANDs

WHERE
ISNULL ( var1, column1) = column1
AND ISNULL ( var2, column2) = column2
etc...

 

[zepp]

If they are all OR, then if any one of them is true it will return that data.

You need to use ISNULL()s and ANDs

WHERE
ISNULL ( var1, column1) = column1
AND ISNULL ( var2, column2) = column2
etc...

Hmm... how would I structure, or rather keep track, of this when querying a possibility of 10ish params at a time?

 

[zepp]

Also I am using Is Null. But where should I place the AND statements?

What I am having trouble with is that the queries can be completely random.

 

[105]

Are you building the query dynamically, or are you trying to have one catch all query?

 

[zepp]

I suppose you could call it a catch-all query. I don't yet know enough though.

This is the query, I modified it today but it still doesn't work. I suppose this is because all possibilities are true....

SELECT Assets.[Asset Number], Assets.[Location Code], Assets.[Asset Code], Assets.[Asset Description], Assets.[Last Inventory Date], Assets.[Follow-Up Date], Assets.[Department Code], Assets.[Custodian Code], Assets.Status, Assets.Manufacturer, Assets.Model, Assets.[Serial Number], Assets.[Acquired From], Assets.[Warranty Expires], Assets.[PO Number], Assets.[Acquisition Date], Assets.[Invoice Number], Assets.[User Defined 1], Assets.[User Defined 2], Assets.[User Defined 3], Assets.[User Defined 4], Assets.[Previous Location]
FROM Assets
WHERE (((Assets.[Asset Number])=[Forms]![advsearch]![AssetNumber])) OR (((Assets.[Asset Number]) Is Null) AND ((Assets.[Location Code])=[Forms]![advsearch]![LocationCode])) OR (((Assets.[Location Code]) Is Null) AND ((Assets.[Asset Code])=[Forms]![advsearch]![AssetCode])) OR (((Assets.[Asset Code]) Is Null) AND ((Assets.[Asset Description])=[Forms]![advsearch]![AssetDescription])) OR (((Assets.[Asset Description]) Is Null) AND ((Assets.[Department Code])=[Forms]![advsearch]![DepartmentCode])) OR (((Assets.[Department Code]) Is Null) AND ((Assets.[Custodian Code])=[Forms]![advsearch]![CustodianCode])) OR (((Assets.[Custodian Code]) Is Null) AND ((Assets.Status)=[Forms]![advsearch]![Status])) OR (((Assets.Status) Is Null) AND ((Assets.Manufacturer)=[Forms]![advsearch]![Manufacturer])) OR (((Assets.Manufacturer) Is Null) AND ((Assets.Model)=[Forms]![advsearch]![Model])) OR (((Assets.Model) Is Null) AND ((Assets.[Serial Number])=[Forms]![advsearch]![SerialNumber])) OR (((Assets.[Serial Number]) Is Null) AND ((Assets.[Acquired From])=[Forms]![advsearch]![Comments])) OR (((Assets.[Acquired From]) Is Null) AND ((Assets.[PO Number])=[Forms]![advsearch]![PONumber])) OR (((Assets.[PO Number]) Is Null) AND ((Assets.[Invoice Number])=[Forms]![advsearch]![SAPNumber])) OR (((Assets.[Invoice Number]) Is Null));

 

[105]

SELECT blah, blah, blah
FROM Assets
WHERE (Assets.[Asset Number])=ISNULL([Forms]![advsearch]![AssetNumber],Assets.[Asset Number])
AND (Assets.[Location Code])=ISNULL([Forms]![advsearch]![LocationCode],Assets.[Location Code])
AND...

The ISNULL is saying, if the value is null then use the value to which you are comparing it, that way NULLs get ignored...

 

[zepp]

No OR statements?

 

[105]

Nope, no ORs.

The ISNULL is basically an OR in this case. Compare the value in the table to the parameter, OR if the parameter is null, compare the value in the table to itself.

 

[zepp]

Hmm.....should that work correctly in Transact-SQL? The syntax appears to be slightly different.

Some links.

ISNULL (Transact-SQL)

IS [NOT] NULL (Transact-SQL)

 

[105]

Syntax

ISNULL ( check_expression , replacement_value )

Arguments

check_expression

Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

Return Types

Returns the same type as check_expression.
Remarks

The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.

 

[zepp]

Messing around with it

Does this look right?

AND ISNULL([Forms]![advsearch]![SerialNumber],Assets.[Serial Number])
AND ISNULL([Forms]![advsearch]![Comments],Assets.[Acquired From])

 

[105]

It does.

 

[zepp]

When I run it it says "Wrong number of arguments"

This is another reason why I am voting for Matt Crooks.

 

[zepp]

I'm going to acquire this soon.

Amazon.com: Microsoft Access 2003 Forms, Reports, and Queries (9780789731524): Paul McFedries: Books

 

[105]

Maybe you can't use direct references to a form in a query. I've only used T-SQL on SQL Server, I've never been saddled with Access.

 

[zepp]

Wirelessly posted (iPhone : Mozilla/5.0 (Linux; U; Android 2.2; en-us; Droid Build/FRG22D) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Mobile Safari/533.1)

That would suck pretty hard. Id hate to have to set up a filter.