Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Cant search for NULL & Date in same query

Discussion in 'Information Technology' started by Trial & Error, Nov 1, 2005.

  1. Trial & Error

    Trial & Error
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    Cant believe its Chistmass Blogs Oct 17, 2015
    Opinion Violence Significantly Alters Children's DNA, Causes Premature Aging Breaking News Apr 25, 2012
    UK Archbishop of Canterbury Visits Hounslow Sikh Temple Breaking News Oct 19, 2011
    Sikhism Intoxicants and Pleasure Videos Jul 10, 2011
    India Binyat Sen's Contribution Significant in Chhattisgarh's "Mitanin" Programme Breaking News May 20, 2011

  3. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

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

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Trial & Error" <TrialError@discussions.microsoft.com> wrote in message
    news:5A999A18-1614-4319-B73B-9ADBB6216EB8@microsoft.com...
    > 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
     
  4. Ofer

    Ofer
    Expand Collapse
    Guest

    Does the query work if you type only the date? without the Null
    --
    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" wrote:

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

    Trial & Error
    Expand Collapse
    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.......



    "Van T. Dinh" wrote:

    > Post details of the relevant Table(s) and the SQL String of your Query.
    >
    > --
    > HTH
    > Van T. Dinh
    > MVP (Access)
    >
    >
    >
    > "Trial & Error" <TrialError@discussions.microsoft.com> wrote in message
    > news:5A999A18-1614-4319-B73B-9ADBB6216EB8@microsoft.com...
    > > 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

    >
    >
    >
     
  6. Ofer

    Ofer
    Expand Collapse
    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



    "Trial & Error" wrote:

    > 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" wrote:
    >
    > > Post details of the relevant Table(s) and the SQL String of your Query.
    > >
    > > --
    > > HTH
    > > Van T. Dinh
    > > MVP (Access)
    > >
    > >
    > >
    > > "Trial & Error" <TrialError@discussions.microsoft.com> wrote in message
    > > news:5A999A18-1614-4319-B73B-9ADBB6216EB8@microsoft.com...
    > > > 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

    > >
    > >
    > >
     
  7. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    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));

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Trial & Error" <TrialError@discussions.microsoft.com> wrote in message
    news:48F4A47F-8D65-4E15-BFF4-CD940014DB5D@microsoft.com...
    >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.......
    >
    >
    >
     

Share This Page