Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    Williamsburg, VA
    Posts
    2

    Red face Unanswered: Access sub-query problem

    I'm trying to include a subquery in an Access query. But when
    I run my query, I get prompted to supply the value for a field referenced in my sub-query. Here's a snippet of code that I hope clarifies what I'm trying to do:

    blah, blah blah.....
    And (([SATURN_spriden].[spriden_pidm])
    not in (select [payroll_ pdrdedn].[pdrdedn_pidm]
    from [payroll_pdrdedn]
    where format([payroll_pdrdedn].[pdrdedn_effective_date],'DDMMMYYYY') = '01032003'
    and [payroll_pdrdedn].[pdrdedn_bdca_code] in ("200","201","210","211","250","251","260","261 ")
    and [payroll_pdrdedn].[pdrdedn_ref_no] like '%e%p%' or [payroll_pdrdedn].[pdrdedn_ref_no] like 'ra%'))
    blah, blah, blah...

    I'd like to point out, that if I remove the sub-query, the rest of the query runs fine. HELP!!!!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you use both [payroll_ pdrdedn] and [payroll_pdrdedn] in the subquery

    notice one o' them has a space, so they're different, so whichever one isn't the actual table name is interpreted as a parameter field and is prompted

    you can eliminate this type of situation by not enclosing your table and column names in square brackets (which means they cannot contain special characters or blanks)

    also, you have a couple of other problems --
    Code:
     from [payroll_pdrdedn]
    where format([payroll_pdrdedn].[pdrdedn_effective_date],'DDMMMYYYY') 
          = '01032003'
      and [payroll_pdrdedn].[pdrdedn_bdca_code] 
          in ("200","201","210","211","250","251","260","261")
      and [payroll_pdrdedn].[pdrdedn_ref_no] like '%e%p%' 
       or [payroll_pdrdedn].[pdrdedn_ref_no] like 'ra%'))
    your WHERE clause is essentially

    ... where a and b and c or d

    which will be evaluated

    ... where ( a and b and c ) or d

    which, i'm fairly certain, is not what you want

    finally, DDMMMYYYY will generate 9 characters, and it's never going to equal '01032003'


    rudy
    http://r937.com/

  3. #3
    Join Date
    May 2003
    Location
    Williamsburg, VA
    Posts
    2

    Cool

    Thanks! I had been looking at it for so long that I couldn't see the obvious! By the way, I checked out your website out of curiosity. Looks like you guys are having fun!

    Take care, and thanks for the help.

    Bobbi

Posting Permissions

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