Cant search for NULL & Date in same query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.......



Van T. Dinh said:
Post details of the relevant Table(s) and the SQL String of your Query.

--
HTH
Van T. Dinh
MVP (Access)



Trial & Error said:
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
Click to expand...
Click to expand...
 
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



Trial & Error said:
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.......



Van T. Dinh said:
Post details of the relevant Table(s) and the SQL String of your Query.

--
HTH
Van T. Dinh
MVP (Access)



Trial & Error said:
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
Click to expand...
Click to expand...
Click to expand...
 
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));
 
Back
Top