Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Worthington, Ohio, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Querying from one table (2000)

    I am trying to query from one table. I need to tell access if the data in field A = the data in field B, return data in fields A,B,X,Y,Z. The data in both A&B are 8 didgit numbers. The data I need returned are a combo of both words and numbers in various fields (represented here as XYZ). Can anyone help?

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Querying from one table (2000)

    Try this in your query:

    IIf([A]=[b],[A] & [b] & [x] & [y] & [z],"NOPE")

    Change NOPE to whatever you need if the values do not match. This says, IF A = B then concatenate the values in A, B, X, Y, and Z without any spaces.

    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Querying from one table (2000)

    ... for some reason my last post does not appear correct. It should read as follows:

    IIF ([A] = [b], [A] & [b] & [X] & [Y] & [Z],"NOPE")
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Querying from one table (2000)

    .. my post still does not look correct when I view it, however, it is correct in edit mode.
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Querying from one table (2000)

    I don't quite understand.

    Do you want to return some fields (A,B,X,Y,Z) from only those records for which A = B? In that case, add all fields you want to display to the query grid. In the first Criteria row in the grid, enter "<!t>[b]<!/t>" (without the quotes) in the column of field A. See attached picture.

    If you also want to return the records for which A and B are both empty, enter "Is Null" (without the quotes) in the second Criteria row in the column of both A and B. Combined with the first row, this means: return records for which A = B (both non-empty), or for which A and B are both empty.

    If you want something else entirely, post back and try to explain a bit more.
    Attached Images Attached Images

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Querying from one table (2000)

    Gary, you need to use the new tag field (see the 1-click tag panel).

    Ordinarily, if you type <!t>[b]<!/t> in your post, it'll be interpreted as bold.

    If you want it to appear as you typed, use <!t>[t]<!/t>b<!t>[t]<!/t>. (It's confusing to create this, but it works)

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Querying from one table (2000)

    ... since I am having a problem with the B, assume B = C, then this...

    IIF ([A] = [C], [A] & [C] & [X] & [Y] & [Z],"NOPE")
    Regards,

    Gary
    (It's been a while!)

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Querying from one table (2000)

    Having turned Bold on, how do you turn Bold off?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    PS. I have worked out how to do it

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

    Re: Querying from one table (2000)

    For everyone who didn't figure it out, the <!t>[/b]<!/t> tag turns off bold.
    Charlotte

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Querying from one table (2000)

    Sorry Charlotte, I should have mentioned that, I always ask people for their solutions and here I am breaking my own rules.
    Pat <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

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