Query Parameter Problem

[purceld2]

I have a query (SQL below) where the user is requested to enter a date and when he does it returns the records as expected. I have a problem when the user does not enter a date, no records are returned. where I would like all records returned. I have tried "OR IS NULL" and it still returns no records

Can some one advise how this could be done with the SQL below

SELECT [TEST Table].[Project code] AS [Project Code], [TEST Table].[Bu Unique Reference] AS [CPN Ref], [TEST Table].[Bu Priority] AS [BU Priority], [TEST Table].[Project title] AS [Project Title], [TEST Table].DESCRIPTION AS Description, [TEST Table].LEVEL4 AS Area, [TEST Table].LEVEL5 AS [Project / Inlife], [TEST Table].INITIATOR AS [Consumer Owner], [TEST Table].SPONSOR AS Sponsor, [TEST Table].[Project Manager], [TEST Table].[Project Start Date], [TEST Table].CUSTOMER_REQUIRED_BY_DATE AS CRD, [TEST Table].OCCD, [TEST Table].CCCD, [TEST Table].Launch, [TEST Table].[Project End Date], [TEST Table].[Parent Project], [TEST Table].Stage AS [Project Stage], [TEST Table].[Status comment] AS [Project comments], [TEST Table].LAST_UPDATED AS [Last Updated], [TEST Table].ACTIVE
FROM [TEST Table]
WHERE ((([TEST Table].Stage)="BTR Rejected & Cancelled" Or ([TEST Table].Stage)="BTR Completed & Closed") AND (([TEST Table].LAST_UPDATED)>[Enter Last Updated Date]) AND (([TEST Table].[Milestone Name])="Entry"));

 

[Brianwarnock]

Read this for full info

Brian

 

[MSAccessRookie]

I have a query (SQL below) where the user is requested to enter a date and when he does it returns the records as expected. I have a problem when the user does not enter a date, no records are returned. where I would like all records returned. I have tried "OR IS NULL" and it still returns no records

Can some one advise how this could be done with the SQL below

SELECT [TEST Table].[Project code] AS [Project Code], [TEST Table].[Bu Unique Reference] AS [CPN Ref], [TEST Table].[Bu Priority] AS [BU Priority], [TEST Table].[Project title] AS [Project Title], [TEST Table].DESCRIPTION AS Description, [TEST Table].LEVEL4 AS Area, [TEST Table].LEVEL5 AS [Project / Inlife], [TEST Table].INITIATOR AS [Consumer Owner], [TEST Table].SPONSOR AS Sponsor, [TEST Table].[Project Manager], [TEST Table].[Project Start Date], [TEST Table].CUSTOMER_REQUIRED_BY_DATE AS CRD, [TEST Table].OCCD, [TEST Table].CCCD, [TEST Table].Launch, [TEST Table].[Project End Date], [TEST Table].[Parent Project], [TEST Table].Stage AS [Project Stage], [TEST Table].[Status comment] AS [Project comments], [TEST Table].LAST_UPDATED AS [Last Updated], [TEST Table].ACTIVE
FROM [TEST Table]
WHERE ((([TEST Table].Stage)="BTR Rejected & Cancelled" Or ([TEST Table].Stage)="BTR Completed & Closed") AND (([TEST Table].LAST_UPDATED)>[COLOR=teal][B][Enter Last Updated Date][/B][/COLOR]) AND (([TEST Table].[Milestone Name])="Entry"));

Try changing "[Enter Last Updated Date]" to something like "IIf([Enter Last Updated Date] Is Null, cDate('1/1/1901'), [Enter Last Updated Date])"

Note that the date '1/1/1901' is arbitrary and may need to be replaced with a date that will precede all dates in your database

 

[purceld2]

Thank MSAccessRockie

I have tried the statement you suggested but it still returns nothing. I did try with CDATE but just errored with data mismatch

Code attached

SELECT [TEST Table].[Project code] AS [Project Code], [TEST Table].[Bu Unique Reference] AS [CPN Ref], [TEST Table].[Bu Priority] AS [BU Priority], [TEST Table].[Project title] AS [Project Title], [TEST Table].DESCRIPTION AS Description, [TEST Table].LEVEL4 AS Area, [TEST Table].LEVEL5 AS [Project / Inlife], [TEST Table].INITIATOR AS [Consumer Owner], [TEST Table].SPONSOR AS Sponsor, [TEST Table].[Project Manager], [TEST Table].[Project Start Date], [TEST Table].CUSTOMER_REQUIRED_BY_DATE AS CRD, [TEST Table].OCCD, [TEST Table].CCCD, [TEST Table].Launch, [TEST Table].[Project End Date], [TEST Table].[Parent Project], [TEST Table].Stage AS [Project Stage], [TEST Table].[Status comment] AS [Project comments], [TEST Table].LAST_UPDATED AS [Last Updated], [TEST Table].ACTIVE
FROM [TEST Table]
WHERE ((([TEST Table].Stage)="BTR Rejected & Cancelled" Or ([TEST Table].Stage)="BTR Completed & Closed") AND (([TEST Table].LAST_UPDATED)=IIf([Enter Last Updated Date] Is Null,01/01/08,[Enter Last Updated Date])) AND (([TEST Table].[Milestone Name])="Entry"));

 

[purceld2]

This is the code which used CDATE it worked but return no rows

SELECT [TEST Table].[Project code] AS [Project Code], [TEST Table].[Bu Unique Reference] AS [CPN Ref], [TEST Table].[Bu Priority] AS [BU Priority], [TEST Table].[Project title] AS [Project Title], [TEST Table].DESCRIPTION AS Description, [TEST Table].LEVEL4 AS Area, [TEST Table].LEVEL5 AS [Project / Inlife], [TEST Table].INITIATOR AS [Consumer Owner], [TEST Table].SPONSOR AS Sponsor, [TEST Table].[Project Manager], [TEST Table].[Project Start Date], [TEST Table].CUSTOMER_REQUIRED_BY_DATE AS CRD, [TEST Table].OCCD, [TEST Table].CCCD, [TEST Table].Launch, [TEST Table].[Project End Date], [TEST Table].[Parent Project], [TEST Table].Stage AS [Project Stage], [TEST Table].[Status comment] AS [Project comments], [TEST Table].LAST_UPDATED AS [Last Updated], [TEST Table].ACTIVE
FROM [TEST Table]
WHERE ((([TEST Table].Stage)="BTR Rejected & Cancelled" Or ([TEST Table].Stage)="BTR Completed & Closed") AND (([TEST Table].LAST_UPDATED)=IIf([Enter Last Updated Date] Is Null,CDate('1/1/1901'),[Enter Last Updated Date])) AND (([TEST Table].[Milestone Name])="Entry"));

 

[purceld2]

Read this for full info

Brian

Brain the links seems to be invalid

 

[Brianwarnock]

Don't know what went wrong there try this

This uses a form to provide the parameter and shows how to allow for nulls

Brian

 

[Brianwarnock]

I think that this

([TEST Table].LAST_UPDATED)=IIf([Enter Last Updated Date] Is Null,CDate('1/1/1901'),[Enter Last Updated Date])

needs replacing by this

(([TEST Table].LAST_UPDATED =[Enter Last Updated Date] or IsNull([Enter Last Updated Date])= True)=True)

Brian

 

[purceld2]

I think that this

([TEST Table].LAST_UPDATED)=IIf([Enter Last Updated Date] Is Null,CDate('1/1/1901'),[Enter Last Updated Date])

needs replacing by this

(([TEST Table].LAST_UPDATED =[Enter Last Updated Date] or IsNull([Enter Last Updated Date])= True)=True)

Brian

Hi Brian

Thank you very much for you reply it worked fine. I am just trying to get my head round the TRUE part of the statement. could explain this to me or point me in the right direction.

Sorry to be a pain

Kind regards

Desmond

 

[Banana]

As matter of clarification on "IS NULL" and IsNull()-

IsNull() is a VBA function while IS NULL is a SQL syntax.

In any SQL statement, I would use IS NULL (or IS NOT NULL) instead of IsNull():

SELECT foo FROM bar WHERE foo IS NOT NULL;
SELECT foo FROM bar WHERE foo IS NULL;

It also requires less typing and comparison to True value compared to IsNull:

SELECT foo FROM bar WHERE IsNull(foo) = False;
SELECT foo FROM bar WHERE IsNull(foo) = True;

Though both will achieve the same result, the process they do to achieve the result is quite different. Also, if the database get upsized to SQL Server, the "IS NULL" is compatible because it's SQL standard whereas IsNull(), being a VBA function is unknown to SS and could be very expensive.


With regards to the OP's SQL:

SELECT [TEST Table].[Project code] AS [Project Code], [TEST Table].[Bu Unique Reference] AS [CPN Ref], [TEST Table].[Bu Priority] AS [BU Priority], [TEST Table].[Project title] AS [Project Title], [TEST Table].DESCRIPTION AS Description, [TEST Table].LEVEL4 AS Area, [TEST Table].LEVEL5 AS [Project / Inlife], [TEST Table].INITIATOR AS [Consumer Owner], [TEST Table].SPONSOR AS Sponsor, [TEST Table].[Project Manager], [TEST Table].[Project Start Date], [TEST Table].CUSTOMER_REQUIRED_BY_DATE AS CRD, [TEST Table].OCCD, [TEST Table].CCCD, [TEST Table].Launch, [TEST Table].[Project End Date], [TEST Table].[Parent Project], [TEST Table].Stage AS [Project Stage], [TEST Table].[Status comment] AS [Project comments], [TEST Table].LAST_UPDATED AS [Last Updated], [TEST Table].ACTIVE
FROM [TEST Table]
WHERE ([TEST Table].Stage="BTR Rejected & Cancelled" Or [TEST Table].Stage="BTR Completed & Closed") AND ([TEST Table].LAST_UPDATED=IIf([Enter Last Updated Date] Is Null,CDate('1/1/1901'),[Enter Last Updated Date])) AND (([TEST Table].[Milestone Name])="Entry"));

The extra ()s that Access inserts make it confusing enough, but I'd want to make that there are a () between OR and ANDs; we don't want to create a weird result because Jet thought you wanted to do a (x OR y AND z) when you want ((x OR y) AND z).

 

[purceld2]

As matter of clarification on "IS NULL" and IsNull()-

IsNull() is a VBA function while IS NULL is a SQL syntax.

In any SQL statement, I would use IS NULL (or IS NOT NULL) instead of IsNull():

SELECT foo FROM bar WHERE foo IS NOT NULL;
SELECT foo FROM bar WHERE foo IS NULL;

It also requires less typing and comparison to True value compared to IsNull:

SELECT foo FROM bar WHERE IsNull(foo) = False;
SELECT foo FROM bar WHERE IsNull(foo) = True;

Though both will achieve the same result, the process they do to achieve the result is quite different. Also, if the database get upsized to SQL Server, the "IS NULL" is compatible because it's SQL standard whereas IsNull(), being a VBA function is unknown to SS and could be very expensive.


With regards to the OP's SQL:

SELECT [TEST Table].[Project code] AS [Project Code], [TEST Table].[Bu Unique Reference] AS [CPN Ref], [TEST Table].[Bu Priority] AS [BU Priority], [TEST Table].[Project title] AS [Project Title], [TEST Table].DESCRIPTION AS Description, [TEST Table].LEVEL4 AS Area, [TEST Table].LEVEL5 AS [Project / Inlife], [TEST Table].INITIATOR AS [Consumer Owner], [TEST Table].SPONSOR AS Sponsor, [TEST Table].[Project Manager], [TEST Table].[Project Start Date], [TEST Table].CUSTOMER_REQUIRED_BY_DATE AS CRD, [TEST Table].OCCD, [TEST Table].CCCD, [TEST Table].Launch, [TEST Table].[Project End Date], [TEST Table].[Parent Project], [TEST Table].Stage AS [Project Stage], [TEST Table].[Status comment] AS [Project comments], [TEST Table].LAST_UPDATED AS [Last Updated], [TEST Table].ACTIVE
FROM [TEST Table]
WHERE ([TEST Table].Stage="BTR Rejected & Cancelled" Or [TEST Table].Stage="BTR Completed & Closed") AND ([TEST Table].LAST_UPDATED=IIf([Enter Last Updated Date] Is Null,CDate('1/1/1901'),[Enter Last Updated Date])) AND (([TEST Table].[Milestone Name])="Entry"));

The extra ()s that Access inserts make it confusing enough, but I'd want to make that there are a () between OR and ANDs; we don't want to create a weird result because Jet thought you wanted to do a (x OR y AND z) when you want ((x OR y) AND z).

thanks

That was very informative

Regards

Desmond

 

[Brianwarnock]

Banana

With regards to the OP's SQL:

The extra ()s that Access inserts make it confusing enough, but I'd want to make that there are a () between OR and ANDs; we don't want to create a weird result because Jet thought you wanted to do a (x OR y AND z) when you want ((x OR y) AND z).

Are you saying that I got the bracketing wrong?


Desmond
In the Link I posted Jon gives a good clear explanation of the technique I used.

Brian

 

[Banana]

I don't think so; I'm just too lazy to use brackets. The trouble is that Access formats the query with lot of ()s so it's hard to read which is in () and which is out. In this case, I just write the criteria out myself to be sure it's what I am expecting, and when it get saved, it get reformatted with all brackets and extra () but at least what I added stays.

 

[gemma-the-husky]

i find the easiest way to see what the syntax SHOULD be (or could be) is to do a visual query (say to test for null) and then see how access shows the SQL.

then make your query look like that

out of interest pretty well all queries other than union queries can be designed in the visual query designer.

 

[Banana]

and even UNION query can be designed with a little hack:

Design the first query, go to SQL view, copy it, go back to design view and design the second query, back to SQL view, paste back the first query's SQL and add the UNION between (making sure that there's no ; in the first SQL).

Still, it doesn't get past the fact that SQL can get ugly when Access formats it with lot of ()s and []s.