SQL Parameter Null

[DuncB7]

Novice here. Don't bite. Building something very basic via MS Query to display in MS Excel.

My query has a parameter however, if the user does not select a parameter, I would like it to return all records.

With simply a parameter against the table (I'll call it Table1) the part in question looks like this when I open SQL statement:

WHERE Table1=?

Is it possible to add to this so all records will be displayed if for example the field was Null. Effectively ignoring the parameter for lack of a better explanation.

[Greenmantle]

Simple answer is no!

.....unless you use some extended TSQL code. if..then...else etc.

[Alex]

If you can edit the SQL statement the change to something like this:

WHERE Table1=@Parameter OR @Parameter is null

[i4got]

Something like

Select * from table1 where ( (@var is null and 1=1) or (field =@var))

[DuncB7]

If I am writing these correctly, I am simply getting results with Null and the selected parameter.

I see the parameters written with @ in so many articles online but it seems to be marked with '?' with mine.

[Gotama]

I'd suggest assigning your parameter with a default wildcard value. This way if a value is passed great...if not then * (or %)

[Alex]

Try this:

WHERE Table1= ? OR ? = ''

'' = single quotes

[DuncB7]

Alex said:

Try this:

WHERE Table1= ? OR ? = ''

'' = single quotes

Error message for that one. An object or column name is missing or empty. For SELECT INTO statements, etc, etc.

[DuncB7]

Found this after some searching, struggling how to translate it into my query without an error however.



I've tried;

WHERE Table1 = IIF(? Is Null, Table1, ?)

Edited by DuncB7 on Wednesday 4th March 15:25

[eps]

It's WHERE Attribute [not Table] = IIF(@Parameter is NULL, Attribute [so always true i.e. should retrieve everything], @Parameter)

It's a bit nonsensical, but should work in your example - although you haven't given a lot of information...

Is the information you are querying contained in the spreadsheet itself or externally held? What version of Excel are you using?

[DuncB7]

eps]It's WHERE Attribute [not Table said:

= IIF(@Parameter is NULL, Attribute [so always true i.e. should retrieve everything], @Parameter)

It's a bit nonsensical, but should work in your example - although you haven't given a lot of information...

Is the information you are querying contained in the spreadsheet itself or externally held? What version of Excel are you using?

Thank you but sadly gives me a Parameters are not allowed in queries that can't be displayed graphically error.

The information is held externally.

Using O365 version of Excel.