473,322 Members | 1,610 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Query with Multi inputs

16
I am going to be as clear as possible about what i got right now and what i want to achieve

Table1: Libary
Title - memo
Last Name - memo
First Name - memo
Company - memo
Year - number
Type - memo
Industry - memo
Organizational Layout - memo
Major Processes - memo
Sub Processes - memo
Summary - memo
Keywords - memo

Table 2: IndustryInformation
Industry - memo

**This give information for the NewEntry form, so the person can selected it from the drop down menu**

Table 3: OrganizationLayout
Organization Layouts

**This give information for the NewEntry form, so the person can selected it from the drop down menu**

Table 4: Major Processes
Major Processes

**This give information for the NewEntry form, so the person can selected it from the drop down menu**

Table 5: Sub Processes
Sub Processes

**This give information for the NewEntry form, so the person can selected it from the drop down menu**

Form: New Entry
Title - Text box
Last Name - text box
First Name - text box
Company - text box
Year - drop down
Type - drop down
Industry - drop down
Organizational Layout - drop down
Major Processes - drop down (the criteria is changed based on the selection of organizational layout selection)
Sub Processes - dropw down (the criteria is changed based on the selection of major processes selection)
Summary - text box
Keywords - text box


what i want to do....
look at my image... that i have attached

so basically i want to create that form
then based on the critea choosen by the user i want to run the query
can someone give me step by step instruction on how to do this?
Attached Images
File Type: jpg Example.jpg (6.8 KB, 165 views)
Mar 3 '09 #1
26 2178
RuralGuy
375 Expert 256MB
Have you really defined all of those fields as MEMO fields? If so, why?
Mar 3 '09 #2
kpouya
16
because of the amount of the text that needs to be put in there. some of the articles titles are more then 255characters
Mar 3 '09 #3
RuralGuy
375 Expert 256MB
I see 10 MEMO fields in the Libary table and no RecordID AutoNumber field. Really?
Mar 3 '09 #4
kpouya
16
yeah my bad, i forgot to include that!
there is RecordID AutoNumber
Mar 3 '09 #5
RuralGuy
375 Expert 256MB
There are issues with Memo fields in queries so you may want to limit how many you use or avoid them if you can. Some developers have even gone so far as to keep all of the memo fields for a table in a separate table linked 1:1 to the 1st table. It looks like you simply want to create a query based on the value of three different ComboBoxes, correct?
Mar 3 '09 #6
kpouya
16
yes that right.
so they choose first option
then second option which its selection is based on first combo box
then choose third option which its selection is based on second combobox selection

i know how to do the filtering based on the selection.
what i need to know is how to send the selection from the combobox to the query and run it.

are you able to give me step by step on how to do this?
Mar 3 '09 #7
RuralGuy
375 Expert 256MB
I think this link should help you.
Mar 3 '09 #8
kpouya
16
i already know how to the cascading!
i want to know how take the values from the the drop downs and run query with it.
Mar 3 '09 #9
RuralGuy
375 Expert 256MB
I believe that is described toward the end of the article.
Mar 3 '09 #10
RuralGuy
375 Expert 256MB
OK, the link doesn't really describe what to do next but it is really just like the cascading technique. Create a query that references all three ComboBoxes.
Mar 3 '09 #11
kpouya
16
"Create a query that references all three ComboBoxes."
how do you do that?
whats the code/macro to take values from the combobox and put it in into a query to run.

I was able to get with one cascading box going. when i try to do the second on, it just returns no result.
Mar 3 '09 #12
RuralGuy
375 Expert 256MB
What is the SQL when one cbo works?
Mar 3 '09 #13
kpouya
16
i guess this would be it...

SELECT Libary.Title, Libary.[Last Name], Libary.[First Name], Libary.Company, Libary.Industry, Libary.[Organization Layout], Libary.[Major Proccess], Libary.[Sub Proccess], Libary.Summary, Libary.Attachment
FROM Libary
WHERE (((Libary.Industry)=[forms]![SearchByIndustry]![Industry]));
Mar 3 '09 #14
RuralGuy
375 Expert 256MB
So something like:

SELECT Libary.Title, Libary.[Last Name], Libary.[First Name], Libary.Company, Libary.Industry, Libary.[Organization Layout], Libary.[Major Proccess], Libary.[Sub Proccess], Libary.Summary, Libary.Attachment
FROM Libary
WHERE (((Libary.Industry)=[forms]![SearchByIndustry]![Industry])
AND ((Libary.Field2) = [Forms]![SearchByIndustry]![cbo2])
AND ((Libary.Field3) = [Forms]![SearchByIndustry]![cbo3]))
Mar 3 '09 #15
kpouya
16
ok thanks i will try that and let you know.
Mar 4 '09 #16
kpouya
16
hey
well i wrote this in my SQL of the Query i am running

SELECT Libary.Title, Libary.LastName, Libary.FirstName, Libary.Company, Libary.Industry, Libary.OrganizationLayout, Libary.MajorProccess, Libary.SubProccess, Libary.Summary, Libary.Attachment
FROM Libary
WHERE (((Libary.Industry)=[forms]![SearchByMore]![Industry]) AND ((Libary.OrganizationLayout)=[forms]![SearchByMore]![Organizational Layout]) AND ((Libary.MajorProccess)=[forms]![SearchByMore]![Major Proccess]) AND ((Libary.SubProccess)=[forms]![SearchByMore]![Sub Proccess]));

but when i go into the form to do a query, it returns nothing.
Mar 4 '09 #17
Stewart Ross
2,545 Expert Mod 2GB
Before RuralGuy comes back to you, my immediate response would be 'and what have you done to check what your query is doing'? I guess RuralGuy could try to divine this on your behalf - but his powers may well not extend to divining answers at a distance without your database in front of him. Nor do mine...

You will need to at least verify the query by checking what happens to each form field criterion one by one. Start by removing the three form field criteria and ensure that the query returns all rows. If it does, put one of the criteria back in and see if the criteria selects what you expect. Then put in the second to see what happens. Finally add the third if the first two work. At some stage one of these criteria will cause a failure.

I am assuming you have some values in the form controls you are referring to when you run the query, as if you don't the query is bound not to return any values. You are running it AFTER you select from the combos, aren't you?

We expect you to help us to help you, by running logical tests that will at least narrow down the possibilities - just saying 'it does not work' or 'it returns nothing' is not at all helpful!

-Stewart
Mar 4 '09 #18
kpouya
16
well i have done that
as soon as insert a second crieteria it doesnt return anything
Mar 4 '09 #19
Stewart Ross
2,545 Expert Mod 2GB
And what have you done about that? Surely this is telling you that your comparison is either to fields that have different values, or to a null? It is impossible for us to debug your query at this end without access to your data, your code, the order in which you are doing things and so on.

Please think what you are expecting us to do to help you before you post! We cannot simply divine the state of your system with no test data to assist us - and no reports from you of what the system state is.

You really must do some more systematic investigation of your criteria and how they are being applied before you post again. Otherwise I feel I must close this thread, as there is no way we can assist when all we know is 'the second criterion does not work'.

-Stewart
Mar 4 '09 #20
OldBirdman
675 512MB
I created a table=Libary with only the fields (all Memo) as shown in the query SQL statement
SELECT Libary.Title, Libary.LastName, Libary.FirstName, Libary.Company, Libary.Industry, Libary.OrganizationLayout, Libary.MajorProccess, Libary.SubProccess, Libary.Summary, Libary.Attachment
FROM Libary
WHERE (((Libary.Industry)=[forms]![SearchByMore]![Industry]) AND ((Libary.OrganizationLayout)=[forms]![SearchByMore]![Organizational Layout]) AND ((Libary.MajorProccess)=[forms]![SearchByMore]![Major Proccess]) AND ((Libary.SubProccess)=[forms]![SearchByMore]![Sub Proccess]));
as supplied by kpouya in Post#17. This was then copied into the SQL view of Query Design.
I created a form with the 4 form controls used in above SQL.
I initialized the table with various numbers, from 1 to 9
I entered various combinations of these numbers on my form
I switched the Query Design View to Datasheet View, and in all cases it works.
Mar 5 '09 #21
RuralGuy
375 Expert 256MB
Thanks for jumping in guys. I welcome the assistance. I was away from the computer today.
Mar 5 '09 #22
kpouya
16
hey OldBirdman
were you able to select one option from every combobox and run the query and see if that works?
Mar 15 '09 #23
OldBirdman
675 512MB
I didn't keep my tests, but yes, as there was nothing in the SQL statement to handle a null value in any of the form objects. So what I wouldn't have done was test with fewer than 4 fields, or with invalid fields. I remember testing with combinations that returned zero records, which caused errors, but I wasn't trying make a program work, just verify that the logic presented was valid.
Mar 15 '09 #24
kpouya
16
cause right now
i checked all my stuff but if i choose like 2 out of four options, it returns null.
so if i get this right i have to have a value in all four to return a result?
Mar 15 '09 #25
OldBirdman
675 512MB
Not at home today, so I'll check recycle bin tomorrow & see if I still have.

If you paste your SQL statement to query design on a new query, it should give you the correct data. If not, try working in desin view to see what is wrong, or what you need to change.

I'll look further tomorrow. Good luck
Mar 15 '09 #26
OldBirdman
675 512MB
Although I kept my test database, I did not keep any results of testing. I have a form, a query, and a table. These are the simplist that I could produce that would allow me to paste the SQL statement as shown by kpouya in post #17 and get meaningful results while in Query Design.

The query (from post #17):
SELECT Libary.Title, Libary.LastName, Libary.FirstName, Libary.Company, Libary.Industry, Libary.OrganizationLayout, Libary.MajorProccess, Libary.SubProccess, Libary.Summary, Libary.Attachment
FROM Libary
WHERE (((Libary.Industry)=[forms]![SearchByMore]![Industry]) AND ((Libary.OrganizationLayout)=[forms]![SearchByMore]![Organizational Layout]) AND ((Libary.MajorProccess)=[forms]![SearchByMore]![Major Proccess]) AND ((Libary.SubProccess)=[forms]![SearchByMore]![Sub Proccess]));
I have a table = "Libary" with the fields (all memo) used by the above query.
I have a form = "SearchByMore" with the controls used by the above query. This form has 4 unbound comboboxes for entering the query criteria. These comboboxes do not cascade, as that has nothing to do with the query.

Have you done the same kind of testing that I have, using the Query Design, with the design grid and the SQL window?
How are you using this query to change your form?
How do you select 2 of 4 comboboxes if they cascade? Notice that the original problem was stated as
so they choose first option
then second option which its selection is based on first combo box
then choose third option which its selection is based on second combobox selection
As stated, ALL criteria must be selected. Original problem had 3 criteria, not 4, so the problem got more complex before the simplier one was working.
What values are in your comboboxes if they are not selected?

cause right now
i checked all my stuff but if i choose like 2 out of four options, it returns null.
so if i get this right i have to have a value in all four to return a result?
If "it returns null" means that it returns a recordset with zero records, then that is what the query finds, running your table against your criteria.

When I entered this thread, it was because I could see that the query, as presented, worked. My table has just the fields used by the query. My form has just the fields used by the query. Whether the controls are textboxes, comboboxes, listboxes, etc. makes no difference to the query. Whether the fields in the table are memo, text, etc. makes no difference to the query.
You want to change what the query does, but you don't post what you are trying.

I have no further answers for you than Stewart Ross Inverness in post #20:
And what have you done about that? Surely this is telling you that your comparison is either to fields that have different values, or to a null? It is impossible for us to debug your query at this end without access to your data, your code, the order in which you are doing things and so on.

Please think what you are expecting us to do to help you before you post! We cannot simply divine the state of your system with no test data to assist us - and no reports from you of what the system state is.

You really must do some more systematic investigation of your criteria and how they are being applied before you post again.
Mar 16 '09 #27

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Mahesh Hardikar | last post by:
Hi , Oracle 8.1.7.0.0 on HP-UX 11.0 I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL T-SQL background. We have a report which uses a select statement . This select...
2
by: Quick Function | last post by:
For the <input type="file">, can I select directory? Multiple directories? Thanks, qq
2
by: Zlatko Matiæ | last post by:
Hello. How to reference selected values from a multi-select list box, as a criteria in a query ? Is it possible at all? Regards, Zlatko
2
by: Diz | last post by:
Can anyone please help with this? when i run the following query, using SELECT*, SELECT * FROM tbl_artwork, tbl_artworkmedium, tbl_medium WHERE tbl_artwork.artworkID =...
4
by: Andy_Khosravi | last post by:
I'm trying to build a search utility for users to find 'inquiries' in my database that involves several tables. This is normally easy to do with the query builder, but I have a unique situation...
5
by: sachu | last post by:
Hi , What i exactly needed is, I need user to build his own query on the client side. Something like user selecting column name from the listbox,etc and selecting the condition. for example:...
11
by: funky | last post by:
hello, I've got a big problem ad i'm not able to resolve it. We have a server running oracle 10g version 10.1.0. We usually use access as front end and connect database tables for data extraction....
4
by: John | last post by:
Hello All, Can someone help me with a problem I am having? I have a form which has a multi-select list where the user can select more than one company name from a list and I need to pass those...
7
by: Kelii | last post by:
Well, I've been searching around the groups for an answer to this one, and none of the suggestions seems to be working for me. I'm almost positive that this is a simple fix, but for the life of me...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.