Form and Query Help Please!

[rsingh4377]

Hi, I am trying to create a form with certain criteria to open a query with the desired result. I will attach the db so that you can take a look before you have anymore questions regarding the db. The problem is that the query is very specific to every single answer I put out. I am generally looking for criteria that only matches yes, however my query takes into account that no is another desired criteria. Instead I want to only search for a few fields using the "yes" value and have the "no" value have no impact on the result. Therefore I want my results to not be so specific to both yes and no values. Any input and help would be great! thanks!

 

[theDBguy]

Hi. Is this related to your other thread? Just curious...

 

[rsingh4377]

Hi, yes it is! Just looking for any feedback. Any general ideas?

 

[theDBguy]

Hi, yes it is! Just looking for any feedback. Any general ideas?

I'm just curious... Have you tried looking at any of the demos or sample code I referred to earlier? They should show you exactly what technique you need to use. How about the very last one I posted? I thought that would have given you exactly what you wanted.

 

[rsingh4377]

The last demo was very interesting but it was for a search form which does not exactly match what I am trying to achieve. Is that what you are referencing? The copydbsample?

 

[theDBguy]

The last demo was very interesting but it was for a search form which does not exactly match what I am trying to achieve. Is that what you are referencing? The copydbsample?

Yes, that's it. What you're trying to do: select certain criteria and display the result, is very similar to performing a "search." That's why I was pointing you to those samples/demos. The technique used is the same. The user selects criteria from a form and then click a button to apply those selected items to a query to get the result. That's exactly what I think you're trying to do, correct? So, whether you want to display the query or display a form or report based on said query, you could use the same technique to apply the criteria to that query based on what the user selected from a "search" form. Hope it makes sense... If I was able to download your file, I would have done it the same way.

 

[rsingh4377]

Yes it was very interesting however it is not exactly what I am trying to achieve. I have combo boxes that I need to use where the end user can simply select their desired criteria and click a button to give them results. I am creating a similar db to see how your method works but it is not exactly what I need. I am looking to create a db with given values in which an end user can simply select what they are looking for instead of typing it in themselves (this db is for people who have no knowledge of access whatsoever, so I need to make it as simple as possible and literally guide them in the right direction with given criteria they can choose and select from). The only problem I am having is that the query gives too specific results, meaning that it takes both my yes and no values from the combo box from which the end user will select. I only want it to take my yes values and have tried a few different methods to no avail. Please let me know if this is making sense? I may not be explaining myself properly, I am also fairly new to access, therefore I apologize for any confusion and thank you again for your help and input!

 

[theDBguy]

Yes it was very interesting however it is not exactly what I am trying to achieve. I have combo boxes that I need to use where the end user can simply select their desired criteria and click a button to give them results. I am creating a similar db to see how your method works but it is not exactly what I need. I am looking to create a db with given values in which an end user can simply select what they are looking for instead of typing it in themselves (this db is for people who have no knowledge of access whatsoever, so I need to make it as simple as possible and literally guide them in the right direction with given criteria they can choose and select from). The only problem I am having is that the query gives too specific results, meaning that it takes both my yes and no values from the combo box from which the end user will select. I only want it to take my yes values and have tried a few different methods to no avail. Please let me know if this is making sense? I may not be explaining myself properly, I am also fairly new to access, therefore I apologize for any confusion and thank you again for your help and input!

Hi. Not sure why it's not making any sense. I am saying you are trying to do "exactly" what the demo is doing. The only difference is the demo, as you said (I couldn't download it either), lets users enter/type words into textboxes, while what you want to use is have users select items from a dropdown. In the end, both of those approaches simply translate into a criteria value to the VBA code for the query. For example, let's say the demo asks the user to enter a "size," and they enter "Small," but in your database, you ask the user to select a size from a list of small, medium, or large. If the user selects "small," it's just the same as if they typed it in themselves. So, as I was saying, the "technique" should be the same, either way.

 

[rsingh4377]

Hi. I believe the only problem would be that all of my fields simply require yes or no, therefore there would be several yes or no data fields. I am not that well-versed in access so can you tell me if that would be a problem? The only options to choose from are yes and no and there are about 19 different fields. Also, I saw the sql setup...would the OR function have an impact on the results, because I tried setting it up like that and it did not work. Also I attempted the Like "*" & ... & "*" already as well and there was no difference. Thank you.

 

[theDBguy]

Hi. I believe the only problem would be that all of my fields simply require yes or no, therefore there would be several yes or no data fields. I am not that well-versed in access so can you tell me if that would be a problem? The only options to choose from are yes and no and there are about 19 different fields. Also, I saw the sql setup...would the OR function have an impact on the results, because I tried setting it up like that and it did not work. Also I attempted the Like "*" & ... & "*" already as well and there was no difference. Thank you.

Hi. You really don't have anything to be concerned about. Whatever you think or find out to not work, I'm pretty sure we can fix to make it work. If OR is not appropriate, we can try something else. But, I think OR should work because a lot of people use it all the time. It's just a matter of getting the syntax correct to get the expected result. Now, the basic idea is this (if I have already explained it before, I apologize), let's say you have 20 yes/no fields you want to use as a potential criteria. Right now, your query might look something like this:

SELECT * FROM Table WHERE YN1=Forms!... AND/OR YN2=Forms!... AND/OR YN3=Forms!... and so on untile YN20=...

So, the problem with that is Access will check all 20 fields for a matching Yes OR No only. So, if you want to check for matching fields like YN1, YN5, YN11, and YN18 only, it doesn't work. What you want to end up having is this:

SELECT * FROM Table WHERE YN1=... OR YN5=... OR YN11=... OR YN18=...

That's it, no extra fields in the criteria. This way, all the other Yes/No fields will be ignored, which means the query result will include those records whether the other Yes/No fields are Yes or No. This is exactly what the demo code does.

I won't be able to download your file until I get home tonight. If I get a chance, I'll take a look and let you know.

 

[theDBguy]

Hi. I believe the only problem would be that all of my fields simply require yes or no, therefore there would be several yes or no data fields. I am not that well-versed in access so can you tell me if that would be a problem? The only options to choose from are yes and no and there are about 19 different fields. Also, I saw the sql setup...would the OR function have an impact on the results, because I tried setting it up like that and it did not work. Also I attempted the Like "*" & ... & "*" already as well and there was no difference. Thank you.

Hi. I was finally able to download your file. After looking at it, I may not completely understand what you're trying to do here. The form you want to use is bound, which means if you change any of the dropdowns, it will also change the data in the table. Also, your dropdowns only have two options Yes or No. I thought you wanted to skip some of them? Without a skip or a blank option, how would you know which one to skip?


In any case, I modified your button to check only the first three dropdowns. If this is what you want, you should be able to add the rest of the dropdowns to the code. The other point of confusion for me is it's also not clear to me if you wanted AND or OR in the criteria. For example, if the user selects YES on the first dropdown and NO on the second one, did you want to see the records where it's Yes on the first field AND No on the second field? Or, did you want to see the records where it's Yes on the first field OR No in the second field?


One last thing, your naming convention might give you problems later on. You should avoid using spaces and special characters when naming db objects.


Hope this helps...

 

[JHB]

I think that is what you want, (look at the attached database).

 

[rsingh4377]

Hi, sorry for the late reply, was away from my computer. Thank you so much for all your continued help! JHB that is exactly what I am looking for! How did you get the query to do that? Please let me know! It is spot on! You're a life saver!

 

[JHB]

.. How did you get the query to do that? Please let me know! It is spot on! ..

Have you looked at the code behind the button, else look at it?

 

[rsingh4377]

Hi JHB, I did look at the code. I am still new and learning to code so I wanted to know if you had sort of a formula for me to mimic this same function? Also I wanted to know if it was possible to make this function show a report instead of a query but with the exact same results? So basically instead of linking that code to a query button to show query results, I'd rather have it open a report. I already tried linking a report to that query, however I did encounter an order of operations problem where the report does not refresh until the search is completed in the query first. Therefore I must first run the query and then the report because the report is linked directly to the query. Is there a way to transfer that responsibility entirely to the report? Please let me know! Thank you so much for your help.

 

[JHB]

Click at the new button in the form, is this what you want?

 

[JHB]

Have you looked at the attached database in post #16, is that what you want?

 

[rsingh4377]

Hi JHB, I was able to resolve that particular issue however I have another bump in the road...I will post the attachments here regarding what I messaged you. Thank you!

 

[JHB]

Yes you posted a printscreen how you want it but not which data there are involved and how they are related to each other.

 

[rsingh4377]

Hi JHB, so basically I want to be able to create a criteria in either access or excel where I would be able to find a BIN in all records and based on that BIN I want to put particular elements into a new table and so on going across. For example, if I am looking for BIN 1234, there are duplicate records under the BIN field with that same number so I want to find that BIN and move on to the next criteria for a particular cell/value and find multiple elements from the next field, so say the elements I am looking for include 1,2,3 and 4. Each element is entered in its own record so I am trying to create a criteria/code in either access or excel to find BIN 1234 for elements 1,2,3 and 4 for one particular cell. I am sure I will be able to duplicate this code once I am able to find the correct code for one cell. Please Help!