1

I try to redefine a SQL query with VBA to include the content of a multivalued table field as a where clause but I can't get it to work.

First I gather all the values from the specific multivalued field (formatted as text, can't use numbers due limitations during the import)

BLPN_Query = DLookup("Kostenstellen_Report", "Optionen_Reportgenerierung", "ID=1")

MsgBox will return this (mind the space between ";" and the next text): 34; 44

This string can contain several different text entries. I would like to use the string "BLPN_Query" as a where clause condition. What I've got so far is this:

"WHERE (PROKALK.NK_STK>0) AND (PROKALK.TERMIN>{d '" & Startjahr & "-01-01'}) AND (PROKALK.BLPNR='" & BLPN_Query & "')"

If there is only one entry it works, but in case there are more than one it won't work (obviously). Not sure but I guess the space between the semicolon and the next text is an issue as well as I have to use "IN" instead of "=" but I don't know how to do this.

Solution (thanks to Andre!)

1.) Get data from table (looks like: 34; 35; 36), remove the spaces and replace the semicolon by a comma including single quotes between the elements for the IN clause. Now it looks like: 34','35','36

BLPN_Query = DLookup("Kostenstellen_Report", "Optionen_Reportgenerierung", "ID=1")
BLPN_Query = Replace(BLPN_Query, " ", "")
BLPN_Query = Replace(BLPN_Query, ";", "','")

2.) Include the string within the where clause (and add a single quote before and after the string) --> Final string: '34','35','36'

AND (PROKALK.BLPNR IN ('" & BLPN_Query & "'))"

it's not pretty but will help us until we finally get the new ERP system and can replace all the old stuff

Improve this question
6
  • Why do you have a multivalued string field? That will make the query difficult. You'll have to make sure that the string you are creating is equal to the string that is being inserted, even a single miss match will not return you a result. Commented Jun 21, 2019 at 7:17
  • I know a multivalued field is definitely not the best way to go but that's all I get from another department :/ At least they can make sure the string I'll get is always looking like this: 34; 44; 55 or 26; 38; 67; 104 (always separated by semicolon and always with a leading space on the second entry (until the last one) Commented Jun 21, 2019 at 7:24
  • Why don't you link the tables (if required) and run a single SELECT query? Commented Jun 21, 2019 at 7:26
  • Well that was my first approach but as I need those data just for report reasons I would like to gather only the information I need without linking the table. The source is a pretty old program (but still in active use) so I would like to go this way :) and it's only this little problem which prevents me from getting the stuff I want. Commented Jun 21, 2019 at 7:34
  • is the sequence of concatenation is predetermined? can you show us some example values which are not working and possibly your table structure? Commented Jun 21, 2019 at 8:33

1 Answer 1

Reset to default
1

IN (...) expects comma, so you need to do:

BLPN_Query = Replace(BLPN_Query, ";", ",")

and then in the WHERE clause:

"WHERE ... AND (PROKALK.BLPNR IN (" & BLPN_Query & "))"

Extra spaces don't hurt. This also works if BLPN_Query contains a single value, but it doesn't if it is empty.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

it's not working but I think (as far as I can remember) I have to wrap all elements with " ' " --> example: '34', '38', '38'
attached my working solution above, thank you very much for your help!
Excellent. I wasn't sure if the single quotes were necessary, if they are, those two Replace() calls and the final wrap with ' ... ' would have been my suggestion too. :) @Moritz

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.