Excel 2007 : Problem using "begins with" parameter in Excel Query

[vtalin]

Hi,

I'm trying to use a parameter in Excel's Microsoft Query that looks as follows:
(dwt_PCKSOD.PART_ID Like '[]%') where [] is a user input text field. It works fine when the statement is an "equal" but returns "Syntax Error" when I use "begins with" or "like".

Any solutions to my problem would be greatly appreciated.

Thanks,

John

[vtalin]

Bump no response

[Fotis1991]

John, is make no sense.

Would you like to upload a sample worksheet?

[vtalin]

Fotis,

Please see worksheet atached. It won't work without a link to our database though. This is the current Query definition:

SELECT SOFOD.SO_ID, SOFOD.SO_LINE_NO, dwt_PCKSOD.PART_ID, dwt_PCKSOH.PICK_DATE, dwt_PCKSOD.PICK_DATE, dwt_PCKSOH.OPERATOR_ID, dwt_PCKSOD.QTY, SOFOD.REV_ORDER_QTY, SOFOD.SHIPPED_QTY, SOFOD.BALANCE_DUE, SOFOD.SO_LINE_STATUS, dwt_PCKSOD.BATCH_NUMBER, SOFOD.STORES_CODE
FROM ESIDB.dbo.dwt_PCKSOD dwt_PCKSOD, ESIDB.dbo.dwt_PCKSOH dwt_PCKSOH, ESIDB.dbo.SOFOD SOFOD
WHERE dwt_PCKSOD.SO_LINE_NO = SOFOD.SO_LINE_NO AND dwt_PCKSOD.BATCH_NUMBER = dwt_PCKSOH.BATCH_NUMBER AND dwt_PCKSOH.SO_ID = SOFOD.SO_ID AND ((dwt_PCKSOD.BOX_TYPE='a') AND (dwt_PCKSOD.PART_ID Like 'DSMA%') AND (SOFOD.SO_LINE_STATUS='O'))
ORDER BY dwt_PCKSOD.PART_ID

If I change it as follows, I get a Syntax Error message :

SELECT SOFOD.SO_ID, SOFOD.SO_LINE_NO, dwt_PCKSOD.PART_ID, dwt_PCKSOH.PICK_DATE, dwt_PCKSOD.PICK_DATE, dwt_PCKSOH.OPERATOR_ID, dwt_PCKSOD.QTY, SOFOD.REV_ORDER_QTY, SOFOD.SHIPPED_QTY, SOFOD.BALANCE_DUE, SOFOD.SO_LINE_STATUS, dwt_PCKSOD.BATCH_NUMBER, SOFOD.STORES_CODE
FROM ESIDB.dbo.dwt_PCKSOD dwt_PCKSOD, ESIDB.dbo.dwt_PCKSOH dwt_PCKSOH, ESIDB.dbo.SOFOD SOFOD
WHERE dwt_PCKSOD.SO_LINE_NO = SOFOD.SO_LINE_NO AND dwt_PCKSOD.BATCH_NUMBER = dwt_PCKSOH.BATCH_NUMBER AND dwt_PCKSOH.SO_ID = SOFOD.SO_ID AND ((dwt_PCKSOD.BOX_TYPE='a') AND (dwt_PCKSOD.PART_ID Like '[]%') AND (SOFOD.SO_LINE_STATUS='O'))
ORDER BY dwt_PCKSOD.PART_ID

[OnErrorGoto0]

Perhaps you can use something like
Like [] & '%'
instead?

[vtalin]

I tried the Like [] & '%' and it returned the error message attached

Thanks,


John

[OnErrorGoto0]

What if you replace the & with + instead?

[ColoradoJay]

Did you find a solution to this? This might help me solve my current problem, where I really need some "IF" logic around the parameter...for example, I need to say, "If the user doesn't input anything, don't query - if they input something, query on that as the criteria." Since all user values start with the same character, in my case, I could just use the logic you were after.

[dazed007]

+ instead of & worked for me, thanks

[Natheplas]

Hello, I appreciate this thread is 4years old now, however I need to know what SO_LINE_NO means?!

Is it how many lines a particular sales order is? I'm contextualising someones old Microsoft query and that's the only column I don't understand?

I'd be very greatful if anyone can tell me what it stands for on an excel query database chart and what its used for?

Thank you