I have a form that contains a couple of combo boxes and a three text boxes.
The combo boxes pull their data from two different fields within one table but they are unbound. This is the Row Source for one of them -
SELECT [FAQs_table].[Department] FROM FAQs_table GROUP BY [FAQs_table].[Department]; )
The text boxes are also unbound and I use these to enter dates.
The idea is to use the combo boxes to select required data and then add a From date and a To date so that the search would return all the items chosen from the combo box between the dates specified.
The problem I have is getting it to work (obviously)
In the query I have this in the criteria of the combo boxes
Like "*" & [Forms]![Form1]![Department Combo] & "*"
Like "*" & [Forms]![Form1]![Products Combo] & "*"
This for the Keywords
Like "*" & [Forms]![Form1]![Keyword] & "*"
All this works fine and if I only choose data from one combo box it will return the results based on that selection only, if I choose data from two combo boxes etc, it will return the results that match the two choices.
Then I complicate things by adding the date search...
Currently I have this in the Criteria
Like "*" & [Forms]![Form1]![From] & [Forms]![Form1]![To] & "*"
and this in the Or filed
Between [Forms]![Form1]![From] And [Forms]![Form1]![To]
When I only use
Between [Forms]![Form1]![From] And [Forms]![Form1]![To] I have to input two dates or there will be no results and not every entry will have a date, so those that don't have dates will be excluded from the results.
When I only use
Like "*" & [Forms]![Form1]![From] & [Forms]![Form1]![To] & "*"
I might as well not have the date option as it will return all the records regardless of the date or if there is no date...
Could anyone help so that if I don't specify a date the search will continue and show all the results (regardless of date) that match the combo box/search box options and if I specify a date range the results shown will only be between these dates?
I will be happy to email the database if this would help. It is less than 200k (Access2000)
Thanks in advance.
The combo boxes pull their data from two different fields within one table but they are unbound. This is the Row Source for one of them -
SELECT [FAQs_table].[Department] FROM FAQs_table GROUP BY [FAQs_table].[Department]; )
The text boxes are also unbound and I use these to enter dates.
The idea is to use the combo boxes to select required data and then add a From date and a To date so that the search would return all the items chosen from the combo box between the dates specified.
The problem I have is getting it to work (obviously)
In the query I have this in the criteria of the combo boxes
Like "*" & [Forms]![Form1]![Department Combo] & "*"
Like "*" & [Forms]![Form1]![Products Combo] & "*"
This for the Keywords
Like "*" & [Forms]![Form1]![Keyword] & "*"
All this works fine and if I only choose data from one combo box it will return the results based on that selection only, if I choose data from two combo boxes etc, it will return the results that match the two choices.
Then I complicate things by adding the date search...
Currently I have this in the Criteria
Like "*" & [Forms]![Form1]![From] & [Forms]![Form1]![To] & "*"
and this in the Or filed
Between [Forms]![Form1]![From] And [Forms]![Form1]![To]
When I only use
Between [Forms]![Form1]![From] And [Forms]![Form1]![To] I have to input two dates or there will be no results and not every entry will have a date, so those that don't have dates will be excluded from the results.
When I only use
Like "*" & [Forms]![Form1]![From] & [Forms]![Form1]![To] & "*"
I might as well not have the date option as it will return all the records regardless of the date or if there is no date...
Could anyone help so that if I don't specify a date the search will continue and show all the results (regardless of date) that match the combo box/search box options and if I specify a date range the results shown will only be between these dates?
I will be happy to email the database if this would help. It is less than 200k (Access2000)
Thanks in advance.
