Access - how to use combobox value in a query

[GolferJohn]

This question is probably so simple that no one else needs to ask it, at least I cannot find it in any postings anywhere.
I would like to construct a query to filter the records between dates in two combo boxes. This could then be applied from a macro called from a button?? I have tried a number of things, but no joy.

Anmy help would be much appreciated!
John

 

[psarros]

In your query:

In the date column:

Between #1/1/2000# And #1/1/2002#

or since you want to use a value on a form

Between forms!formname.comboboxname1 And forms!formname.comboboxname2

make sure the form is open when you open the query.

-Nick

 

[GolferJohn]

Hi, Thanks for the reply, some progress, yet still some problems. (I know so little)
My form is 'Rollbacks' and I have unbound combo boxes named 'Before' and 'After' and a button 'Apply'. In the CB's Row Source Type, I have 'Value list' and in Row Source I have dates like '31/12/96;31/03/97;30/06/97;30/09/96' entered. Boxes work and I can select different dates in each. For the Apply button in 'On Click' I have 'DateFilter' the name of a macro, in which I have under action in the top cell 'ApplyFilter' In the bottom as Filter Name I have 'Dates' a macro which has the field 'Date' selected and as Criteria 'Between [Forms]![Rollbacks].[before] And [Forms]![Rollbacks].[after]' (Access 2000 added the square brackets)
The first time I open the form select two dates and click the button, the the button, the correct number of records are filtered. If I change the dates, and click 'apply' again, however the same records are returned. Removing the filter first makes no difference. In order to achieve a different set of filtered records I must close the form and re-open it. If I do this and select different dates, then I will get a different record set. I'm sure the program is not that stupid, it must be me, but where? how?
Thanks for your tolerance and help in this simple matter,
John.

 

[GolferJohn]

Arghh! Now something else strange is happening!
I continued to play around changing dates etc & got an error message 'Error 3088' Too many expressions in ORDER BY clause' I opened my query and saw 30 or more columns with the field 'date' selected, most with 'decending' selected, but some with 'ascending' no criteria (I think I occasionally sorted on the Date field a-z then z-a).I deleted all and saved the query again but they re-appeared after I clicked 'Apply'
The last time I cleaned all these out & saved again, now I get no records returned!! Not even by loading the query and applying it directly!
For the combo boxes should there be any entries under the event tab? I tried selecting DateFilter under 'On Change' but is seems to make no difference. Likewise 'After update'
Thanks,
John

 

[GolferJohn]

Just dropped a couple of new text boxes on my form and typed in '31/12/96' and '01/04/00' then changed my criteria in design view to
Between [Forms]![Rollbacks].[text133] And [Forms]![Rollbacks].[text135] (double checked textbox names)
and still get no records returned (well, actually one blank record with no date and a few default entries. There are 1100 records in the table, and 38 with no date entered).If intead I type in
Between #31/12/96# And #01/04/00#
I get 393 records returned.
Even tried reversing the textbox order
Between [Forms]![Rollbacks].[text135] And [Forms]![Rollbacks].[text133]
Also tried re-booting. (This is a pristine installation of office 2000 as I restored (re-ghosted) my system today)
still no joy!

Am I missing something very simple???
John

 

[pbrodsky]

Did you set the format of the textboxes to 'date'?

 

[pbrodsky]

Never mind- that doesn't seem to be it