question

AhmadAriefMohdNor-2381 avatar image
0 Votes"
AhmadAriefMohdNor-2381 asked AhmadAriefMohdNor-2381 answered

MS Access Query Question

Hi all, I have a question or you guys.

Is there a way to search queries using multiple words separately in one box? Like i have a table with system number for each row as follows:

MS1
UK2
BK3

How to i display MS1 and UK2 in query result if i search for only "MS1" or "UK2". Thanks

I dont know how to do that for different searches. I have like a thousands of data. I want to be able to group my search result. for example :

Searchbox value : MS1
Query Result : MS1, UK2

Searchbox value : BK3
Query Result : MI1, MO2, MU4

What should i write in the criteria box? My apologies if my question is not clear. Im new in developing access. Below are what i've done so far. I want to be able to search for one system number and i want the result to display a few of other different number like grouped together

120540-preview.jpg



Basically i want to make and "if then else" statement for the search function. how to code that?

office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered AhmadAriefMohdNor-2381 commented

In the query designer, enter your criteria in separate rows under the same column.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Basically i want to make and "if then else" statement for the search function. Would you teach me how to code that.

0 Votes 0 ·

Or case function

0 Votes 0 ·
AhmadAriefMohdNor-2381 avatar image
0 Votes"
AhmadAriefMohdNor-2381 answered

I dont know how to do that for different searches. I have like a thousands of data. I want to be able to group my search result. for example :

Searchbox value : MS1
Query Result : MS1, UK2

Searchbox value : BK3
Query Result : MI1, MO2, MU4

What should i write in the criteria box? My apologies if my question is not clear. Im new in developing access. Below are what i've done so far. I want to be able to search for one system number and i want the result to display a few of other different number like grouped together

120540-preview.jpg



preview.jpg (328.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

Perhaps, rather than using a criteria, you can create a separate table that has your search grouping and then join that table to your data table in your query and then apply the criteria to the group table.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BobLarson-6601 avatar image
0 Votes"
BobLarson-6601 answered AhmadAriefMohdNor-2381 commented

To really do what you want to do you would want to step into the world of VBA. Using VBA you can test to see if there are more than one criteria and then you can split the input to be able to build the where clause.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

There's an option in access to write VB code. I just don't know what to call for to make the search function. I just know how to write the statement ( If then else) or (Case)

0 Votes 0 ·
AhmadAriefMohdNor-2381 avatar image
0 Votes"
AhmadAriefMohdNor-2381 answered

For now I have temporary solution. By adding a new column on my table and put the different system names inside that cell. So, when i search for any one off those name, the query will display result for all name listed in that cell. I just hide that Field in the query design and display only the correct system name for each individual data. But if anyone have a better solution kindly share. Thank you.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.