searching a range of data w/ multi-field form

[drew17]

Hello, I have been stuck on this problem for a while and I would really appreciate any help or advise. I am currently building a database for a family business that will contain all of the past job information. I've created a query that, among other things, pulls up contract prices.



My issue is that I want to be able to enter and search for a range of the contract prices, using the multi-field search form that I have already created, but i need the query to pull up results if no information is entered by the user or if only the upper bound or lower bound is entered. I'm hoping that I can just do this in the query design view. I have never done any sort of programming before, and although this is probably pretty basic I would greatly appreciate some help

Thank you in advance

[ranman256]

Your best bet may be to have a base query ready (with no criteria)
Then always start there and manually enter your criteria, be it :
a date range
or Client name
etc...

This way there's no programming and you get see what data you're asking for.

[orange]

The key part of a useful, easily maintained database is to get the database (tables and relationships) designed to meet the business requirements.

Can you
a) tell us in 4 -5 lines what the business is about?
b) show us (jpg) a picture of your relationships window(tables and relationships)?

[drew17]

First of all, thank you for your replies, I wasn't expecting any responses so quickly.

Anyway, the business is a general contracting firm (construction) that is mostly concerned with hospital renovations. The goal with this database is for users to be able to search past job information (contract prices, job type, hospital, duration etc...) in order to better gauge how much future projects will cost. I've attached pictures the following; relationships, the query that I mentioned earlier (in both datasheet view and design view), and the search form.

This database is by no means anywhere near completion, I still have to set up a few more relaionships etc., I am just trying to see if I can get a user-friendly search form to work.

Thank you

[orange]

I think Owners may be misleading --- sounds like Client, Customer -- Owner just doesn't seem right but you know your business.
Are ProjectManagers, Engineers and SuperIntendants all employees of your Company? If so you may want an EmployeeTable and a Role table assigned to a Job or Project.
I don't think your tables are normalized sufficiently. Can Job involve many Projects? Do you break Projects into Tasks or Activities?
Do Contracts only exist at the Job level?
ChangeOrders would be new table. It would be the Many side table of a 1 to Many relationship. I don't know what is being changed, but that would be the 1 side table.

I'm going to suggest you work through this tutorial to help you with normalization and identifying the "entities" in your business. Pay particular attention to his business statement/description.

Good luck with your project.

[atuhacek]

You definately need to break job table down. Working in construction of that size with architects and engineers you are going to havve multiply print changes that lead to add-on. Then for you query I'd apply filters based on combo's from your form using the recordset object.

[drew17]

Thank you for your input, I will work on making my tables less redundant. As far as the search query is concerned, is there a simple line of code that I could use to enable users to search a range of data? Again keeping in mind that the idea is to be able to do four things;

1.)Search for Final Contract prices greater than a certain value (leaving the "less than" entry blank)
2.) Search Final Contract prices less than a certain value
3.) input "greater than" and "less than" values at the same time
4.)Return all records in the query if nothing is entered in the greater than/less than criteria on the form

I have tried a few different things to make this work, including IIF statements, but I have no programming experience and am unsure of what type of function will work. I also do not fully understand all of the criteria, I tried to use is null and is not null (to check if one of the entries in the form was blank and then move on from there) in my statements but I think I just don't have a great understanding of what "null" is. If anyone could point me in the right direction I would greatly appreciate it.

Here's the IIF statement that I tried, so that you can have a laugh

IIf([Forms]![frm_search]![High] Is Not Null,IIf([Forms]![frm_search]![Low] Is Not Null,([tbl_Job].[FinalContract])<=[Forms]![frm_search]![Upper] And ([tbl_Job].[FinalContract])>=[Forms]![testforms]![Low],([tbl_Job].[FinalContract])<=[Forms]![testforms]![High]),IIf([forms]![testforms]![Low] Is Not Null,([tbl_Job].[FinalContract])>=[forms]![testforms]![Lower],([tbl_Job].[FinalContract]) Is Not Null))