Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query won't work (Access 2002)

    I'm trying to create this query & in one particular field (EIS_No) it doesn't matter what I put in the criteria, I can't get a result. I've even tried copying some data into the criteria box, using 'like' etc, but to no avail. Can anybody explain where I'm going wrong?

    I've attached a sample.
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Query won't work (Access 2002)

    I looked at attached db & had no problem specifying criteria for EIS_No field - see attd zip file with modified query. I think you may be having problems because of the input mask defined for EIS_No field in Table2: 99 99999 99 99. This input mask will display the EIS_No in this format:

    03 12345 01 10

    but it is saved as:

    03123450110

    This is how same code appears in related field in Table3, the EIS field, which does not have input mask defined. Example of revised query SQL:

    SELECT Table1.RND_no, Table1.Customer, Table2.EIS_No, Table2.DieNumber, Table3.EIS
    FROM (Table1 INNER JOIN Table2 ON Table1.RND_no = Table2.RndNo) INNER JOIN Table3 ON Table2.EIS_No = Table3.EIS
    WHERE (((Table2.EIS_No)="03210290310"));

    Note that EIS_No used as criteria is NOT formatted with the spaces. You should keep this in mind when specifying criteria for the query.

    HTH
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query won't work (Access 2002)

    Thanks for that Mark, I kinda got it sorted in the query, but then when I tried to do it via a form (i.e. like [forms]![formname], etc) I couldn't get anywhere. The same thing was happening with the RND_no, but I've changed that so it works. Unfortunately, I'm not allowed to change any part of the format for the EIS_no [img]/forums/images/smilies/sad.gif[/img]

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

    Re: Query won't work (Access 2002)

    If you put a text box on the form and enter a value without the spaces, such as 03123450110, you can use this as criteria.
    If you put a combo box on the form with Table2 as Row Source, you can use this as criteria. It will display the values without spaces.

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query won't work (Access 2002)

    Thank you gents, I'm now sorted [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

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