Query Criteria (Office 2003)

Topic

New Reply

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

Reply | Quote

Replies

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]

Reply | Quote

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

Reply | Quote

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

Reply | Quote