My VBA query: SQL = "SELECT * FROM tblOptionBaseDataRules WHERE '" & selectedPart & "' IN (gExpandID([ID]))"
selectedpart is '18461'
[ID] '18460-18462'
gExpandID function returns '18460','18461','18462'
I expect a result record for this record and any record where [ID] = '18461' however it is only returning records where ID is '18461' and appearing to ignore '18460-18462.
Any thoughts?
Kevin Yip
@Reply 3 years ago
If gExpandID() returns a string, then that's the problem right there -- when you use IN (s), s is *not* a string, but SQL syntax that represents a collection of items. So the proper way to write that query is to use *string concatenation* to construct the proper SQL syntax with the result from gExpandID():
Dim ID As String
ID = "18460-18462"
SQL = "SELECT * FROM tblOptionBaseDataRules WHERE '" & selectedPart & "' IN (" & gExpandID(ID) & ")"
Note that ID is now a VBA variable, not a field name in the query.
Kevin Yip
@Reply 3 years ago
And if you must use your ID field in your query, consider splitting them into two fields, ID1 and ID2, representing the lower and upper limits ("18460" and "18462"). Then you can write:
SQL = "SELECT * FROM tblOptionBaseDataRules WHERE '" & selectedPart & "' Between ID1 And ID2;"
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Visitor Forum.
