Access 07 - Query based on dates from a form

[PavelowFE]

I'm trying to get a query of a table output the data based on the start and end dates of a form (that remains open).

Here's what I have, but it isn't working. I've tried several ways using the WHERE function.

As always, I appreciate the help very much.

SELECT DISTINCTROW Sum([Teams Trained].[Team Members]) AS [# of Operators Trained]
FROM [Teams Trained]
WHERE Between [Forms]![Date Range]![Start Date] And [Forms]![Date Range]![End Date];


Vince

 

[PWise]

SELECT DISTINCTROW Sum([Teams Trained].[Team Members]) AS [# of Operators Trained]
FROM [Teams Trained]
WHERE [COLOR=red][i]Datefield [/i][/color]Between [Forms]![Date Range]![Start Date] And [Forms]![Date Range]![End Date];

 

[PavelowFE]

Does the word "Datefield" go there? If so, all I get is a popup window for Datefield.

 

[PavelowFE]

I got it now. I used

[Start Date] And [End Date] for Datefield.

Thanks!

Vince

 

[PWise]

did you test the query to see that it returns the rows that you want

 

[PavelowFE]

Yes, it checked good.

Now, for part II:

I want to have the Date Range as a subform on my main form. How can I get all the functions relying on [Forms]![Date Range] to work?

Thanks again for the help.

 

[PavelowFE]

Got it.

Have to call up the main form, then the subform.