Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Query Criteria (Office 2003)

    Hi, I am tring to display records on a form based on a query which limits records based on items in a combo box. The following code works great ...

    ...Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center] & "*" ...

    But if the combo box displays a "1", I get records that have a "1", "10", and "19". So I tried to get the string length by using the following in the query Criteria...

    ... IIf([Forms]![Pricing]![frmTARTypeCut].[Form]![vBCLength]="1",(Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center],(Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center] & "*") ...

    However, this ends up giving me no records at all. Also, when I go back and tryy to tweek the query criteria, I have noticed that Access has automatically changed the query criteria to ...

    ... IIf([Forms]![Pricing]![frmTARTypeCut].[Form]![vBCLength]="1",([Pricing Detail TAR].[Burden_Center]) Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center],([Pricing Detail TAR].[Burden_Center]) Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center] & "*").


    Can any one shed some light on this for me?

    Thanks
    Kevin
    Kevin

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

    Re: Query Criteria (Office 2003)

    If you want to select values that are *equal* to that of the combo box, you shouldn't use Like and the wildcard *. Simply use

    =[Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center]

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (Office 2003)

    Thanks Hans, I thought of that and tried the following...

    ... IIf([Forms]![Pricing]![frmTARTypeCut].[Form]![vBCLength]="1",[Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center],Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center] & "*") ...

    ... I can get a single criteria to work. In other words if I remove the IIF statement and just use ...

    ... [Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center] ...
    or
    ... Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center] & "*") idependently, it works, but as soon as I tie them to the IIF statement it bombs.


    My most recent attempt was that since the query looks at more than one combo box on that subform and that this one combo box is the only one set up with an IIF statement, that it is the combination of the two criteria that is messing it up.

    The below is the complete SQL ...

    ... SELECT [Pricing Detail TAR].Pricing_ID, [Pricing Detail TAR].Rate_ID, [Pricing Detail TAR].CLIN, [Pricing Detail TAR].Ship, [Pricing Detail TAR].POP, [Pricing Detail TAR].Burden_Center, [Pricing Detail TAR].TAR_Est_Hours, [Pricing Detail TAR].TAR_Matl_Esc, [Pricing Detail TAR].TAR_Matl_Desc, [Pricing Detail TAR].[TAR Hours Cut], [Pricing Detail TAR].[TAR De-Esc Material Cut], [Pricing Detail TAR].[TAR Esc Material Cut], [Pricing Detail TAR].[TAR Position Hours], [Pricing Detail TAR].[TAR De-Esc Mat Position], [Pricing Detail TAR].[TAR Esc Mat Position]
    FROM [Pricing Detail TAR]
    WHERE ((([Pricing Detail TAR].Pricing_ID)=[Forms]![Pricing]![Pricing_ID]) AND (([Pricing Detail TAR].CLIN) Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboCLIN] & "*") AND (([Pricing Detail TAR].Ship) Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboShip] & "*") AND (([Pricing Detail TAR].POP) Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboPOP] & "*") AND (([Pricing Detail TAR].Burden_Center)=IIf([Forms]![Pricing]![frmTARTypeCut].[Form]![vBCLength]="1",[Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center],([Pricing Detail TAR].[Burden_Center]) Like [Forms]![Pricing]![frmTARTypeCut].[Form]![cboBurden_Center] & "*")));


    I may not be able to seperate these records out the way I am thinking.

    Thanks
    Kevin
    Kevin

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

    Re: Query Criteria (Office 2003)

    I don't really understand what you want to accomplish. Can you try to explain in words?

Posting Permissions

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