Querys and Criteria

Yorkie71

New member
Local time
Today, 20:17
Joined
Nov 4, 2012
Messages
7
Hello...my first post, and i'm pretty new to Access - so please bear with me!

I've written a huge database for work, it contains projects, dates info are required, financial stuff and about 25 reports that generate every detail from project status to whats money has been spent, and whats left. So far so good! The forms all work well, as does the financial input / recording forms etc. The reports though have become a problem, to a point where the entire Database just isn't delivering whats required and kinda puts all my hard work under a cloud!

So, my problem(s) are:

I have a form that asks the user to select a Project Managers name (combobox) Project Engineer (combobox) and Project ID (combobox). It querys the various project tables for the 'Project Manager' 'Project Engineer' and Project ID and returns the correct text detail into the combobox.

What i would prefer is for the first combobox (Project Manager) to return its value, but then reduce the 'names' that then form the (Project Engineer) combobox selection - basically filter the results pre selection of the Project Engineer. That combobox (Project Engineer) then filters the results in the last combobox (Project ID) - in effect drill down to which project manager 'manages' which project engineer, who in turn manages which project.

Once that has been done, i then want the reports to display data based upon the combobox filters.

At the moment, i have the query written and working, the primary 'Criteria' is a date selection to give results that occur within the last say...30 days - that works fine on its own! But, if i then drop down a line to the OR statement / line on the Query and include a ref to the three noted combobox's, i dont get a filtered result, in fact i get nothing! So, i split the three refs to the combobox's over three OR statements and i can then filter via the Project Manager combobox, the Project Engineer combobox or the Project Status combobox - but not all three! The difference is about 90 pages of info when, if i could get the report to return the results filtered on the selection of the three combobox's; it would equate to circa 2 pages!

Tried loads of methods, not one works form me, and i am now very stuck!

Can anybody help me?

Even if the filtering down through the combobox's is too hard to do, the ability to have the report query only display the results that contain the stated 'project manager, project engineer, and project ID' would suffice! The date criteria is / will always to needed too.

Thanks for reading this...
 
What i would prefer is for the first combobox (Project Manager) to return its value, but then reduce the 'names' that then form the (Project Engineer) combobox selection - basically filter the results pre selection of the Project Engineer. That combobox (Project Engineer) then filters the results in the last combobox (Project ID) - in effect drill down to which project manager 'manages' which project engineer, who in turn manages which project.
This is referred to as cascading combo boxes. Look at this thread on how to set it up.

http://www.access-programmers.co.uk/forums/showthread.php?t=223980&highlight=cascade

Post back with any questions once you have go this set up.

Alan
 
Thanks for that - much appreciated...i'll give it a go, but will that resolve the issue of the Report Query just returning values of the three combobox's to filter the results?
 
If you want to filter on a date range and other fields, you need to put them on the same line in the criteria. That creates an And function in your logic. So if you want all projects with Project Mgr A, Project Engineer B, and Project Status X during the month of October, then you will need to have the criteria all on the same line.

I hope I am understanding your request.

Alan
 
Thank you Alan, i did try that: on the Criteria line i had the correct ref to link to the content of the relevant combobox, and then the date range filter too (on the same line in the criteria, and it returns zero results on the report. I have the Project Manager. Project Engineer and Project ID in seperate fields in the query, and their respective 'criteria linking to the form/ combobox. Is this correct?

Appreciate your time...
 
Is it working or not? If not how about posting a copy of your database- remove private/confidential info -first?
 
Hello again,

Sorry for the delay, it's been a long week so far! I have attached the file, but would you mind if i PM'd you the password etc rather than post it? Really appreciate your help as i am so stuck now! Even if you just 'fix' one report / query / table etc...i'll try do the rest myself! The reports that come under 'finance Reporting' are just fine as they are, and don't need filters.

Thanks...
 
Last edited:
OK go for it.
 
Can't find the PM button...never mind - the user name is: TK, the password is: TK.

Thanks...
 
With TK/TK
Message -- you are not authorized to use this database.
?????????????????????????????????????????????????????
 
With TK/TK
Message -- you are not authorized to use this database.
?????????????????????????????????????????????????????
Just open it holding the Shift Key. Then go to the UserDetails table for the user name and password which is tk (lower case by the way - don't know if that matters).
 
Thats ok...it's authorized by me - and i'm ok with you accessing it (haha).
 
BTW: It's been written on Access2010, and modified on 2003!
 
Just open it holding the Shift Key. Then go to the UserDetails table for the user name and password which is tk (lower case by the way - don't know if that matters).

Thanks Bob. 2010 is all new to me.
 

Users who are viewing this thread

Back
Top Bottom