Hi all,
I am working on a multiple optional field search form, the query for which is created by a VBA setQry.
I have very little experience with VBA as I am working with a Web Database which forces one to rely on Macros.
The form and query are working great, except that in order to make one of the fields work, I have to manually edit the criteria in the query design window because I don't know how to make the VBA create it correctly. Which would be fine if I only had to do it once, but I hope to expand the capabilities of this search form, which means modifying the VBA, running to to set the query up, then pasting the criteria multiple times into the query design window, every time I change it! :banghead:
The relevant line in the VBA code is
(pCat1 is a text parameter)
which places multiple instances of [pCat1] in the criteria for CategoryIDs.
Then I paste this criteria
into each place [pCat1] appears.
This works perfectly, so please don't try to fix my actual criteria, all I want to know at this stage is how to have the VBA create the criteria I otherwise have to paste in, if possible.
The reason for the criteria is to pick a number out of a list of numbers like so: 95,96,1033,864,854,874 without giving me other numbers which contain the number, and taking into account the varying places the numbers can appear in relation to the commas.
I hope to eventually add the ability to optionally search for multiple CategoryIDs in one search, but before I can even start experimenting with that, I need to get to the point where I don't have to be pasting the criteria in every time I modify and run the VBA as it is terribly time consuming!
I am working on a multiple optional field search form, the query for which is created by a VBA setQry.
I have very little experience with VBA as I am working with a Web Database which forces one to rely on Macros.
The form and query are working great, except that in order to make one of the fields work, I have to manually edit the criteria in the query design window because I don't know how to make the VBA create it correctly. Which would be fine if I only had to do it once, but I hope to expand the capabilities of this search form, which means modifying the VBA, running to to set the query up, then pasting the criteria multiple times into the query design window, every time I change it! :banghead:
The relevant line in the VBA code is
Code:
"( (CategoryIDs=[pCat1]) OR ([pCat1]="""") ) AND " & _which places multiple instances of [pCat1] in the criteria for CategoryIDs.
Then I paste this criteria
Code:
Like [pCat1] Or Like "*," & [pCat1] Or Like [pCat1] & ",*" Or Like "*," & [pCat1] & ",*"This works perfectly, so please don't try to fix my actual criteria, all I want to know at this stage is how to have the VBA create the criteria I otherwise have to paste in, if possible.
The reason for the criteria is to pick a number out of a list of numbers like so: 95,96,1033,864,854,874 without giving me other numbers which contain the number, and taking into account the varying places the numbers can appear in relation to the commas.
I hope to eventually add the ability to optionally search for multiple CategoryIDs in one search, but before I can even start experimenting with that, I need to get to the point where I don't have to be pasting the criteria in every time I modify and run the VBA as it is terribly time consuming!
