iif statement in a query

[to47122]

Help please



I am querying my primary table off the primary key. From that query I get [CustomerPartNumber]. Then [CustomerPartNumber] is linked to another secondary table [PartNumber]. The problem is sometimes [CustomerPartNumber] has extra characters in it that I don't want. Example: Primary table has (001)123456 and the secondary table has 123456, these are the same and I need them to be linked. I want to check if [CustomerPartNumber] start with "(" and if it does I want to skip the first 5 characters and finish the query. How do I do this in the Design view of the Query. I tried the following in the Criteria field on both fields.

iif(left([CustomerPartNumber],1)="(",mid([CustomerPartNumber],6),[CustomerPartNumber])

Help Please

[chris.williams]

Try changing the mid to a right.

[to47122]

right will not work because i do not know the length of the feild

[chris.williams]

OK - I see what you are doing. If you are not including the expression, then you will need to:

nochris: IIf(Left([first_name],5)="Chris",Mid([first_name],6),[first_name])

[to47122]

Im not sure I understand. How does that help to continue the query. Sorry I am a beginner over my head. Please see attached

[chris.williams]

Create a new field in the query with the expression. Make sure you give it a new name such as "checkedpartnumber". Choose a blank column in the query and under the field name enter this:

checkedpartnumber:iif(left([CustomerPartNumber],1)="(",mid([CustomerPartNumber],6),[CustomerPartNumber])

Then in your forms that use the query, refer to the field [checkedparnumber] instead of [CustomerPartNumber]

[to47122]

I need to change the customer part number mid query because if it does the query will not return any information to the form. the two customer part numbers are linked and I need information out of the secondardy table

[chris.williams]

Then you can insert the following code in the field name and either eliminate the unmodified field of the same name or rename it.

CustomerPartNumber:iif(left([dbo_PSC17OrderDetail].[CustomerPartNumber],1)="(",mid([dbo_PSC17OrderDetail].[CustomerPartNumber],6),[dbo_PSC17OrderDetail].[CustomerPartNumber])

To rename the original field insert this into the field name:

OriginalCustomerPartNumber:[dbo_PSC17OrderDetail].[CustomerPartNumber]

[to47122]

Thank you that will work but how do I get the new [CustomerPartNumber] linked to [CustPart]. I do not have access to the source table to rename the feilds?

[John_G]

Hi -

If I understand you correctly, you want to use the (sometimes) modified part number to link two tables. To do this, you are going to need two queries. The first will use the IIF expression to generate a calculated field. Let's call that field CustomerPartNumber as you suggested, and call the query Query1. Now create a second query, with Query1 and Partnumber, and link the two using CustomerPartNumber in Query1 and the corresponding field in the PartNumber table. Use the new Query2 in your form.

John