Putting in a date range

[choolan01]

I'm a beginner to Microsoft Access, and I just started working on a project for a marketing company I work for. I have to build them a pretty straightforward database which has tables including Clients, Contacts, Vendors, Employees, Timesheets, etc. The point is to have any employee be able to work with the database and enter any piece of information they have into it. Again, it's a pretty straightforward database. Right now, I am working on a search form for specific jobs. For example, if they need to design a website for a particular company, that specific website job will be entered into the jobs table. I am working on the search form right now so that they can search for specific jobs and they will come up.

One of the sections of the form is a date range, for date received. For example, this would apply if they wanted to search for all jobs that they received between 1/1/05 and 7/10/05. In the query that I am designing for this form, I have the table entry "Date Received" apply to both of the two boxes in the form (I titled them "What Date Received 1" and "What Date Received 2"... my boss advised me to title the boxes in the search forms "What ___" to not get them confused with the forms displaying the information). In the query, the code I am using right now for Date Received is this:

Between [Forms]![Search Jobs]![What Date Received 1] And [Forms]![Search Jobs]![What Date Received 2]

I've also tried this code:

(Between [Forms]![Search Jobs]![What Date Received 1] And [Forms]![Search Jobs]![What Date Received 2]) Or IsNull([Forms]![Search Jobs]![What Date Received 1]) Or IsNull([Forms]![Search Jobs]![What Date Received 2])


The first code works if I have things entered into these boxes. However, if I try to search and leave these blank, I never get any results. I tried playing around with some "IfEmpty" statements, but none of those worked. The second code doesn't work either. Does anybody know how I should modify this statement so that if the "Date Received" boxes are empty, it just ignores it? Thanks a lot.

 

[ThreeCrow]

I'm taking for granted that one or both of these date fields might be emty (Null), is that correct.

And that you want show only those records that have one or the other or both fields with a date in the field.

 

[choolan01]

Well, every job that I enter into the table will have a Date Received entry. I just want the search form to ignore the date received entries if one or both of them are empty. For example, here is what I enter into the query for standard text boxes:

Like [Forms]![Search Jobs]![What Job Code] & "*" Or IsEmpty([Forms]![Search Jobs]![What Job Code])

So, I'm saying that if it is entered, then to search by that criteria. Or, if it's empty, then ignore. I would like to do that in the query for comparing the two dates, but I can't figure out what the "empty" or "null" statement should be. Thanks again.