I make it work! Thanks to all for your responses. What I did is I eliminate
the ID column from my table and my combo boxes now have only one column
corresponding to the building, area, fromdate and todate respectively. The
code now is like this:
SELECT PitchTable.Date, PitchTable.Building, PitchTable.Area,
PitchTable.Goal, PitchTable.Actual
FROM PitchTable
WHERE (((PitchTable.Date) Between Forms!PitchTableWeekly1!FromDate.Value And
Forms!PitchTableWeekly1!ToDate.Value) And
((PitchTable.Building)=Forms!PitchTableWeekly1!Building0.Value) And
((PitchTable.Area)=Forms!PitchTableWeekly1!Area0.Value))
ORDER BY PitchTable.Date;
Jebentle, I hope this works for you too. If you need any help, just pst.
Thanks again to everyone for your responses.
jebentle said:
Just wondering if you were able to get this to work for you? I have a similar
problem, but am trying to run my query from a List box instead of a combo
box. The Query is not recognizing the criteria I choose from the List. In my
query, I have the criteria: [Forms]![frmName]![List7]
Any ideas? Thanks!!!
:
I'm taking the dates for the combo boxes from the table (PitchTable). I can
change them for textboxes but I will not be able to change the area combo to
a textbox, so I still need to figure out how to make it work with a combo
box. I was using an ID clumn in my table to make them unique by this number.
When I create the combo, Access automatically adds this column (hidden) to
the combo box as the first column.
I really appreciated your responses. Thanks all.
:
A couple of dumb questions.
Why pick dates from combos? Why not just text boxes?
If these boxes are on your selection input form why do you have the extra
column for ID?
--
KARL DEWEY
Build a little - Test a little
:
Specifiyng the column is needed but the query prompts an error saying that
the function Forms!FormName!ControlName!Column is undefined. Btw, the column
number should be 1 because the column 0 corresponds to the ID.
Please let know if u find any other suggestion. Thanks for your help.
:
If you're pulling data from a combo box, you should specify, which column to
use.
I.E. Me.[Building0].Column(0) or
[Forms]![PitchTableWeekly1]![Building0]!Collumn(0)
:
Thanks for your response Karl. I tried the code you sent me but when I open
the query it still prompts a blank table. The query haven't recognizes the
values from the combo boxes.
Note:
The form has a submit button which has an event procedure that opens the
query and the form is opening by the time the query opens...
If any suggestion, please let me know. Thanks again.
:
Try it this way --
SELECT PitchTable.Date, PitchTable.Building, PitchTable.Area,
PitchTable.Goal, PitchTable.Actual
FROM PitchTable
WHERE (PitchTable.Date Between [Forms]![PitchTableWeekly1]![FromDate] And
[Forms]![PitchTableWeekly1]![ToDate]) AND
(PitchTable.Building=[Forms]![PitchTableWeekly1]![Building0]) AND
(PitchTable.Area=[Forms]![PitchTableWeekly1]![Area0])
ORDER BY PitchTable.Date;
--
KARL DEWEY
Build a little - Test a little
:
Good afternoon all:
I have a form with 4 combo boxes:
1. FromDate
2. ToDate
3. Building0
4. Area0
I would like to create a query that uses these values as parameters. The
query haven't work properly yet, it just prompt a window with a blank table,
so it isn't getting the actual values of the combo boxes in the form. This is
the code I have for the query:
SELECT PitchTable.Date, PitchTable.Building, PitchTable.Area,
PitchTable.Goal, PitchTable.Actual
FROM PitchTable
WHERE (((PitchTable.Date) Between
Eval("Forms!PitchTableWeekly1!FromDate.Column(1)") And
Eval("Forms!PitchTableWeekly1!ToDate.Column(1)")) AND
((PitchTable.Building)=Eval("Forms!PitchTableWeekly1!Building0.Column(1)"))
AND ((PitchTable.Area)=Eval("Forms!PitchTableWeekly1!Area0.Column(1)")))
ORDER BY PitchTable.Date;
I added the eval expression because
Forms!PitchTableWeekly1!ControlName.Column was prompting an unidentified
error message.
Any help will be really appreciated.