Search using Combo Box - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Search using Combo Box

Johanna Wonderly

4 years ago

Hi again, Ok So from Search 2.0 Video extended cut, I am feeling pretty familiar with VBA code regarding searches, and have successfully used them for single line of text searches. I have double/triple checked my code. And added the SQL Box to check my work, I am unsure why the whole code is not running.

This is my first search with a combo box , is there something that should be set differently?

I cannot get StartDateSearch, EndDateSearch, and StaffSearch to work together If I only put in a staff it will reveal "SELECT * FROM DSSCategoryT WHERE StaffFK LIKE "*7*" "

But as soon as I add a date to the mix, it forgets the staff and only uses the date. (With StaffSearch 7 and StartDate of April 1st I get) SELECT * FROM DSSCategoryT WHERE ServiceDate >= #4/1/2022#

If I put in an EndDateSearch it does something similar where it ignores the previous information in StaffSearch and StartDateSearch and only plugs in the end date: Ex: "SELECT * FROM DSSCategoryT WHERE ServiceDate <= #4/10/2022# "

I do have Images to share, I Think I add those that after I submit?

	Johanna Wonderly OP @Reply 4 years ago

	Johanna Wonderly OP @Reply 4 years ago

	Adam Schwanz @Reply 4 years ago
Your problem is you're building onto your wherestr, and then the line immediately under that you're saying forget all that, this is going to be wherestr now. If wherestr <> "" is good, then the line below it wherestr = just this now

	Kevin Robertson @Reply 4 years ago
in each of your If blocks when you are setting the Where condition the entire WhereStr is being over-written. It needs to be `WhereStr = WhereStr & "the rest of your criteria"`

Scott Axton @Reply

4 years ago

In VBA Access looks from the top to the bottom. Hits your first WhereStr and evaluates that first if. Then it goes to the next one. That one says put xxx in WhereStr and use it instead - overwriting the first Where condition.
You missed one crucial step. You need to add on the new criteria to the WhereStr that is already there
I think your code will work if you change each to;

WhereStr = WhereStr & ...

	Scott Axton @Reply 4 years ago
LOL - Same answer (basically) - I guess I really need to refresh before posting. So the answer is-------- All of the above.

Johanna Wonderly OP @Reply

4 years ago

Oh Good gravy. Thank you. And both of your explanations helped me see my mistake... Suppose that's what I get for Copy & Pasting the top line of Code.

I am having another issue where the red box always defaults to "Front" On this particular page, but if I look at my back end tables, it is assigned to specific staff members. (front represents our receptionist) So I am sure I will be reaching out again soon.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View