Help fix SQL Statement for query

[Budro49]

I am new to this forum so Hello everyone. I have a database that does not output like I want it to look. The IIf statement needs your help. I basically have two problems.

1) The model series field is a 3 character field. Two characters can be used but it must have a space as the 3rd character. If you look at the output of the query the IIf statement allows it to not only pick up the 3 digit control codes but it takes any first two characters of a 3 character code that is used with a space. Example The ALP code is good but it also picked the AL code out of the ALP and should not have unless it was shown as AL with a space.

2) I show in the table how the query is showing multiple rows for each code. Below I show how I want it to look. One row for each part number along with all the codes and description on the one row in a single string.



I hope you can help. Thanks Richard

[June7]

Why is the query a make-table?

1. Left([CONTROL_CODES].[CODE] & " ",3)

2. Check this thread http://forums.aspfree.com/microsoft-...ne-447601.html

[Budro49]

Thanks for you reply! I am not very good at using Access when it comes to SQL statements. I used the make table so I could show how the query was outputting now and below show how I need it to output. Here is my SQL query statement and I can't get it right on adding your fix for item #1 into statement. Your fix was: Left([CONTROL_CODES].[CODE] & " ",3)



SELECT Industrial_Items_DB.PART_NUMBER, Industrial_Items_DB.DESCRIPTION, Industrial_Items_DB.MODEL_SERIES, CONTROL_CODES.CODE_DESC, Industrial_Items_DB.ORGANIZATION_CODE, IIf(InStr(1,[INDUSTRIAL_ITEMS_DB].[MODEL_SERIES],[CONTROL_CODES].[CODE]),[CONTROL_CODES].[CODE],InStr(1,[INDUSTRIAL_ITEMS_DB].[MODEL_SERIES],[CONTROL_CODES].[CODE])) AS MODEL_SERIES_3CHAR INTO [Sample DU Database]
FROM Industrial_Items_DB, CONTROL_CODES
WHERE (((Industrial_Items_DB.MODEL_SERIES) Like "*DU*") AND ((Industrial_Items_DB.ORGANIZATION_CODE)="H03") AND ((IIf(InStr(1,[INDUSTRIAL_ITEMS_DB].[MODEL_SERIES],[CONTROL_CODES].[CODE]),[CONTROL_CODES].[CODE],InStr(1,[INDUSTRIAL_ITEMS_DB].[MODEL_SERIES],[CONTROL_CODES].[CODE])))<>"0"))
ORDER BY Industrial_Items_DB.PART_NUMBER;

Thanks, Richard

[June7]

Try:
SELECT Industrial_Items_DB.PART_NUMBER, Industrial_Items_DB.DESCRIPTION, Industrial_Items_DB.MODEL_SERIES, CONTROL_CODES.CODE_DESC, Industrial_Items_DB.ORGANIZATION_CODE, IIf(InStr(1,[INDUSTRIAL_ITEMS_DB].[MODEL_SERIES],Left([CONTROL_CODES].[CODE] & " ",3)),[CONTROL_CODES].[CODE],"NONE") AS MODEL_SERIES_3CHAR
FROM Industrial_Items_DB, CONTROL_CODES
WHERE (((Industrial_Items_DB.MODEL_SERIES) Like "*DU*") AND ((Industrial_Items_DB.ORGANIZATION_CODE)="H03") AND ((InStr(1,[INDUSTRIAL_ITEMS_DB].[MODEL_SERIES],Left([CONTROL_CODES].[CODE] & " ",3)))>0))
ORDER BY Industrial_Items_DB.PART_NUMBER;

[Budro49]

Thank! that fixed my problem 1 except why does it recognize the DU Code. If that code showed up on each part number the problem one would be solved. Thanks again for you help next I will look at the part 2 of my problem. Richard

Try:
SELECT Industrial_Items_DB.PART_NUMBER, Industrial_Items_DB.DESCRIPTION, Industrial_Items_DB.MODEL_SERIES, CONTROL_CODES.CODE_DESC, Industrial_Items_DB.ORGANIZATION_CODE, IIf(InStr(1,[INDUSTRIAL_ITEMS_DB].[MODEL_SERIES],Left([CONTROL_CODES].[CODE] & " ",3)),[CONTROL_CODES].[CODE],"NONE") AS MODEL_SERIES_3CHAR
FROM Industrial_Items_DB, CONTROL_CODES
WHERE (((Industrial_Items_DB.MODEL_SERIES) Like "*DU*") AND ((Industrial_Items_DB.ORGANIZATION_CODE)="H03") AND ((InStr(1,[INDUSTRIAL_ITEMS_DB].[MODEL_SERIES],Left([CONTROL_CODES].[CODE] & " ",3)))>0))
ORDER BY Industrial_Items_DB.PART_NUMBER;

[June7]

The DU code is recognized because of the Like operator and wildcards in the MODEL_SERIES criteria. You had this in the query. Why did you use it if you do not understand how it works?

[Budro49]

The DU code is recognized because of the Like operator and wildcards in the MODEL_SERIES criteria. You had this in the query. Why did you use it if you do not understand how it works?

I understand why it is in the statement. I don't understand why it didn't come out with the rest of the coded parts as output. It should be as a row for each of the two part numbers. It was before and now it is not.

[June7]

Because the DU in the Model_Series is not 3 characters. Per your criteria "Example The ALP code is good but it also picked the AL code out of the ALP and should not have unless it was shown as AL with a space." The DU at the end of the Model_Series value does not have a space to match the "DU " 3 character string. Use Left([Model_Series] & " ", Len([Model_Series] & " ")) in the IIF expressions.

[Budro49]

Because the DU in the Model_Series is not 3 characters. Per your criteria "Example The ALP code is good but it also picked the AL code out of the ALP and should not have unless it was shown as AL with a space." The DU at the end of the Model_Series value does not have a space to match the "DU " 3 character string. Use Left([Model_Series] & " ", Len([Model_Series] & " ")) in the IIF expressions.

Got it, you solved my part 1 of my post. Thank you very much. Hopefully the leads I was given for part 2 will work and I am golden. Will close post with solved as soon as I can see if the leads pan out.

[Budro49]

Because the DU in the Model_Series is not 3 characters. Per your criteria "Example The ALP code is good but it also picked the AL code out of the ALP and should not have unless it was shown as AL with a space." The DU at the end of the Model_Series value does not have a space to match the "DU " 3 character string. Use Left([Model_Series] & " ", Len([Model_Series] & " ")) in the IIF expressions.

Thought this fixed it but it didn't. I tried adding the string above but it didn't seem to work. Please take sql above and add string to be sure I am doing it like you are saying.

The model series AK DU is killing me. If any two character code is on the end it is left off. I want to keep it from doing as before with the ALP and AL but I need it to recognize a two character when it is the last code of string. Thanks

[June7]

Does this work for you:
SELECT Industrial_Items_DB.PART_NUMBER, Industrial_Items_DB.DESCRIPTION, Industrial_Items_DB.MODEL_SERIES, CONTROL_CODES.CODE_DESC, Industrial_Items_DB.ORGANIZATION_CODE, IIf(InStr(1,Left([Model_Series] & " ", Len([Model_Series] & " ")),Left([CONTROL_CODES].[CODE] & " ",3)),[CONTROL_CODES].[CODE],"NONE") AS MODEL_SERIES_3CHAR
FROM Industrial_Items_DB, CONTROL_CODES
WHERE (((Industrial_Items_DB.MODEL_SERIES) Like "*DU*") AND ((Industrial_Items_DB.ORGANIZATION_CODE)="H03") AND ((InStr(1,Left([Model_Series] & " ", Len([Model_Series] & " ")),Left([CONTROL_CODES].[CODE] & " ",3)))>0))
ORDER BY Industrial_Items_DB.PART_NUMBER;

[Budro49]

Does this work for you:
SELECT Industrial_Items_DB.PART_NUMBER, Industrial_Items_DB.DESCRIPTION, Industrial_Items_DB.MODEL_SERIES, CONTROL_CODES.CODE_DESC, Industrial_Items_DB.ORGANIZATION_CODE, IIf(InStr(1,Left([Model_Series] & " ", Len([Model_Series] & " ")),Left([CONTROL_CODES].[CODE] & " ",3)),[CONTROL_CODES].[CODE],"NONE") AS MODEL_SERIES_3CHAR
FROM Industrial_Items_DB, CONTROL_CODES
WHERE (((Industrial_Items_DB.MODEL_SERIES) Like "*DU*") AND ((Industrial_Items_DB.ORGANIZATION_CODE)="H03") AND ((InStr(1,Left([Model_Series] & " ", Len([Model_Series] & " ")),Left([CONTROL_CODES].[CODE] & " ",3)))>0))
ORDER BY Industrial_Items_DB.PART_NUMBER;

Perfect! Thank you so much. Now if I can get this to concatenate I am all set.

[Budro49]

Part 1 of this thread is solved but part 2 is still open. I need concatenation help. Do I need to start a new thread??