Multiple fields with data taken from FORM TEXTBOXES

[kryptor]

Hi all,
I have setup a form that has multiple textboxes on there and I can get the query to search the straight forward fields of my query.

My question is in regard to having 16 fields that may or may not contain a DATE. What I have on my form is a StartDate and EndDate textbox. What I need to be able to do is search the data in the table accross 16 fields for dates BETWEEN and INCLUDING the StartDate and EndDate.

I have setup a SQL query to drag the fields I require in the output of the query and have built it using the code WHERE table.Date1 between startdate.text and enddate.text or table.date2 between startdate.text and enddate.text Etc.

However when I run this query then I get no results could anybody possibly point me in the direction of where I may have gone wrong or even if there is a much easier way of just getting the answers I need.

Many Thanks
A frustrated IT Network Systems Admin/DBase dabbler/Anything else that you can think of...

If @ 1st U don't suceed then I guess your not a bird

 

[randy700]

Something like this?

SELECT * FROM theTable WHERE
FirstDateField BETWEEN Forms!frmName!StartDate AND Forms!frmName!EndDate OR
SecondDateField BETWEEN Forms!frmName!StartDate AND Forms!frmName!EndDate OR   ...  etc  ...

Randy

 

[dhookom]

16 fields for dates

This suggests your table is not normalized. I would expect a related table of dates with one record per date.

Duane
Hook'D on Access
MS Access MVP