Cant search for NULL & Date in same query

[Guest]

Trying to run a search where a user can enter a date, and have the query
return any records containing that date that have 1 specific field with a
null value.

If I enter Is Null in the criteria under the "Request" field, my query will
return the records I am looking for. However, I do not want ALL the records
from now till the end of eternity... I would like to specify what date

So I enter [Enter Date: dd-mmm-yy] in the criteria under the date field.
I type a date that I know has records with Null values in the Request field,
and no records are returned by the query.

Really bizar.......
any thoughts???

thanks

 

[Guest]

Does the query work if you type only the date? without the Null

 

[Van T. Dinh]

Post details of the relevant Table(s) and the SQL String of your Query.

 

[Guest]

I removed the "Is Null" and only asked for a date, and YES I do recieve the
results I am expecting....
When I add the "Is Null" back in, I recieve NO results.

I am running the query on a table named "Lessonbooker"... the fields in
question are "Date" (medium format, Date/Time) and "Request" which is a
combo box linked to my staff table.

The query works great if I only use 1 criteria, the moment I use both, it
stops returning results.

The SQL looks like this

SELECT LessonBooker.[Lesson #], LessonBooker.Phone, LessonBooker.[First
Name], LessonBooker.Date, LessonBooker.Time, LessonBooker.Duration,
LessonBooker.Type, LessonBooker.[SKI / BOARD], LessonBooker.Request,
LessonBooker.Ability
FROM LessonBooker
WHERE (((LessonBooker.Date)=[Enter Date]) AND ((LessonBooker.Request) Is
Null));


Thanks for the help guys.......

Post details of the relevant Table(s) and the SQL String of your Query.

--
HTH
Van T. Dinh
MVP (Access)

Trying to run a search where a user can enter a date, and have the query
return any records containing that date that have 1 specific field with a
null value.

If I enter Is Null in the criteria under the "Request" field, my query
will
return the records I am looking for. However, I do not want ALL the
records
from now till the end of eternity... I would like to specify what date

So I enter [Enter Date: dd-mmm-yy] in the criteria under the date field.
I type a date that I know has records with Null values in the Request
field,
and no records are returned by the query.

Really bizar.......
any thoughts???

thanks

 

[Guest]

Something I would try, if the Request field is text

SELECT LessonBooker.[Lesson #], LessonBooker.Phone, LessonBooker.[First
Name], LessonBooker.Date, LessonBooker.Time, LessonBooker.Duration,
LessonBooker.Type, LessonBooker.[SKI / BOARD], LessonBooker.Request,
LessonBooker.Ability
FROM LessonBooker
WHERE LessonBooker.Date=[Enter Date] AND (LessonBooker.Request Is
Null Or Trim(LessonBooker.Request) = "")
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

I removed the "Is Null" and only asked for a date, and YES I do recieve the
results I am expecting....
When I add the "Is Null" back in, I recieve NO results.

I am running the query on a table named "Lessonbooker"... the fields in
question are "Date" (medium format, Date/Time) and "Request" which is a
combo box linked to my staff table.

The query works great if I only use 1 criteria, the moment I use both, it
stops returning results.

The SQL looks like this

SELECT LessonBooker.[Lesson #], LessonBooker.Phone, LessonBooker.[First
Name], LessonBooker.Date, LessonBooker.Time, LessonBooker.Duration,
LessonBooker.Type, LessonBooker.[SKI / BOARD], LessonBooker.Request,
LessonBooker.Ability
FROM LessonBooker
WHERE (((LessonBooker.Date)=[Enter Date]) AND ((LessonBooker.Request) Is
Null));


Thanks for the help guys.......

Post details of the relevant Table(s) and the SQL String of your Query.

--
HTH
Van T. Dinh
MVP (Access)

Trying to run a search where a user can enter a date, and have the query
return any records containing that date that have 1 specific field with a
null value.

If I enter Is Null in the criteria under the "Request" field, my query
will
return the records I am looking for. However, I do not want ALL the
records
from now till the end of eternity... I would like to specify what date

So I enter [Enter Date: dd-mmm-yy] in the criteria under the date field.
I type a date that I know has records with Null values in the Request
field,
and no records are returned by the query.

Really bizar.......
any thoughts???

thanks

 

[Van T. Dinh]

It sounds to me that values for the Request Field may be empty String rather
than Null.

Try the following which trap for Null / empty String and white spaces:

SELECT LessonBooker.[Lesson #], LessonBooker.Phone,
LessonBooker.[First Name], LessonBooker.[Date], LessonBooker.[Time],
LessonBooker.Duration, LessonBooker.Type, LessonBooker.[SKI / BOARD],
LessonBooker.Request, LessonBooker.Ability
FROM LessonBooker
WHERE (((LessonBooker.[Date])=[Enter Date])
AND (Len(Trim(LessonBooker.Request & "")) = 0));