Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not in subquery (Access97)

    this is driving me mad ! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    i have two tables
    1-a list of telephone services
    2-a list of bills received
    and want to supply a query (and then report) that will list all services that have not been billed in the last 3 months (or date provided by the user)
    I have written a query along the lines of :
    Select telephone number from list of services where telephone number is not in(select telephone number from bills received where billdate > [date])
    this query however, either returns all rows or no rows from the services table depending on date entered.
    running the subquery on its own show a gradual increase in telephone numbers as the date is moved back/forth.

    Please, <img src=/S/confused.gif border=0 alt=confused width=15 height=20>where am i going wrong, what can i do differently to get this report ?

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Not in subquery (Access97)

    Post the SQL. It doesn't look like there's anything wrong with the concept.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in subquery (Access97)

    Here it is:
    SELECT DISTINCT MainData.TelNo
    FROM MainData
    WHERE (((MainData.TelNo) Not In
    (SELECT CallCharges.ccTelNo
    FROM CallCharges
    WHERE ((CallCharges.ccBillDate) > #15/07/01#))));

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Not in subquery (Access97)

    I don't see anything wrong with it, although you might try a >= instead of just > and see if that makes a difference.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in subquery (Access97)

    thanks for this charlotte, it makes no difference though !
    how would the field format affect this query ? in the table all dates are 'short date' format, does the query have to match this of is it irrelevant ?
    I ask because when the day value of the date begins with a zero (ie before the tenth of the month) i get no rows returned, when the day value is greater than ten then i get all rows returned, the whole date in the query does not seem to be parsed properly.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in subquery (Access97)

    Further development:
    The query will exclude/include rows if i use the telephone number rather than the date in the subquery, how should the date value be expressed ?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Not in subquery (Access97)

    The criteria has to match the format of the date in the query field. I usually find it easiest to format both the field I'm matching and the criteria value. That way, no matter what format the date is coming up in, I can coerce it into a short date so that it finds matches.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in subquery (Access97)

    do you do this in VBA or is it possible in the query ?

    as you can see the table holds the dates as short date and the query contains a date in short date style.
    I have created a test database and a similar query that works as expected, can the data be corrupt or something like that ?
    I had a couple of ideas over night but they have proved to be red herrings, I am really struggling with this one, any ideas for an alternative solution ?

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Not in subquery (Access97)

    You can do it in the query. Check the properties of the date field in the query grid and make sure its format is set to a short date. Or create an expression from the field and use the format function to set it to the particular date format you want to use, then do the same in the criteria line. For example, you might want to use the "dd/mm/yy" format in both to make sure it always inserts a leading zero in days below ten.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •