Parameter Query in a date Field.

[Vacco]

I am trying to run a parameter query with user input on a date field (out of
service date) but i Don't need a specific date, I just need to be prompted
for records with a date - records with no date - or all records. This will
give me items that are out of service, in service or both. Kind of like -
Null or Not Null or All
I tried different expressions in the criteria field but with no luck .
I am working in a select query criteria field.

All help is greatly appreciated
Thanks in advance Vacco

 

[John Spencer (MVP)]

The following should work. Be warned that the Access query tool will reorganize
this when you close it.

SELECT *
FROM Table
WHERE DateField Is Null And [Null, Not Null, Or All?] = "Null"
OR DateField is Not Null AND [Null, Not Null, Or All?] = "Not Null"
OR [Null, Not Null, Or All?] = "All"
OR [Null, Not Null, Or All?] is Null

That will get all records when the parameter response is "All" or is left blank.

 

[Vacco]

Thanks John, I dont know if I am doing something wrong but I entered your
expression into the select query's criteria field by choosing build and
pasting it in and modifiying it to my table names.
Here is what i wrote in there:

Select * from life belt where [oos date] is null and [Null,,not null, or
all?] = null
or oos date is not null and [Null,,not null, or all?] = not null
or [Null,,not null, or all?] = "all"
o r[Null,,not null, or all?] is null

Life Belt is a table name and oos date is the field name
When I run it I get an error stating The syntax of the subquery in this
expression is incorrect.

Please Help
Vacco

The following should work. Be warned that the Access query tool will reorganize
this when you close it.

SELECT *
FROM Table
WHERE DateField Is Null And [Null, Not Null, Or All?] = "Null"
OR DateField is Not Null AND [Null, Not Null, Or All?] = "Not Null"
OR [Null, Not Null, Or All?] = "All"
OR [Null, Not Null, Or All?] is Null

That will get all records when the parameter response is "All" or is left blank.

I am trying to run a parameter query with user input on a date field (out of
service date) but i Don't need a specific date, I just need to be prompted
for records with a date - records with no date - or all records. This will
give me items that are out of service, in service or both. Kind of like -
Null or Not Null or All
I tried different expressions in the criteria field but with no luck .
I am working in a select query criteria field.

All help is greatly appreciated
Thanks in advance Vacco

 

[John Spencer (MVP)]

You need to surround the Null and Not Null responses to the parameter with quote
marks. The last line is testing to see if the user left the parameter blank
(null). The other lines are testing for the presence of a string in response to
the parameter. By the way, I don't think this is the best solution, but it is
probably the easiest to create. ONe reason that I don't particularly like it is
that the user might type "NOT NUL" and this will return no records and won't
tell you why it failed to return any records.

Select * from life belt
where [oos date] is null and [Null, not null, or all?] = "null"
or oos date is not null and [Null, not null, or all?] = "not null"
or [Null, not null, or all?] = "all"
or [Null, not null, or all?] is null

Thanks John, I dont know if I am doing something wrong but I entered your
expression into the select query's criteria field by choosing build and
pasting it in and modifiying it to my table names.
Here is what i wrote in there:

Select * from life belt where [oos date] is null and [Null,,not null, or
all?] = null
or oos date is not null and [Null,,not null, or all?] = not null
or [Null,,not null, or all?] = "all"
o r[Null,,not null, or all?] is null

Life Belt is a table name and oos date is the field name
When I run it I get an error stating The syntax of the subquery in this
expression is incorrect.

Please Help
Vacco

The following should work. Be warned that the Access query tool will reorganize
this when you close it.

SELECT *
FROM Table
WHERE DateField Is Null And [Null, Not Null, Or All?] = "Null"
OR DateField is Not Null AND [Null, Not Null, Or All?] = "Not Null"
OR [Null, Not Null, Or All?] = "All"
OR [Null, Not Null, Or All?] is Null

That will get all records when the parameter response is "All" or is left blank.

I am trying to run a parameter query with user input on a date field (out of
service date) but i Don't need a specific date, I just need to be prompted
for records with a date - records with no date - or all records. This will
give me items that are out of service, in service or both. Kind of like -
Null or Not Null or All
I tried different expressions in the criteria field but with no luck .
I am working in a select query criteria field.

All help is greatly appreciated
Thanks in advance Vacco