Make Table Query Help

[efgh]

I have a query that works when I put in the date but not when I put in the date field name from a form. What's odd about this is that I use the date field in the same query and it works fine there.
Here's the SQL:

SELECT [Latitude Rate Plans].Number, [Latitude Rate Plans].Type, [Latitude Data Units].Template, Count([Latitude Data Units].Unit) AS CountOfUnit INTO TempActiveUnitsTW
from [Latitude Data Units], [Latitude Rate Plans]
where [Latitude Rate Plans].Code = [Latitude Data Units].Template
and (([Latitude Data Units].InDate <= [Forms]![Main]![EDate]) and (([Latitude Data Units].TmDate >= [Forms]![Main]![EDate]) OR (([Latitude Data Units].TmDate) is Null)))
and (([Latitude Data Units].Template) Is Not Null)
GROUP BY [Latitude Rate Plans].Number, [Latitude Rate Plans].Type, [Latitude Data Units].Template
ORDER BY [Latitude Rate Plans].Type;

If I replace [Latitude Data Units].InDate <= [Forms]![Main]![EDate] with [Latitude Data Units].InDate <= #6/24/2006# which is the date entered in the form, it works fine and I get the results that I want. Any ideas as to what's wrong?

 

[OfficeDev18 via AccessMonster.com]

I bet I know what's happening. Are you running the query while the form's
open in form view? If so, the query can't see the form's controls' values
correctly, and the query will always run - with wrong results. Wanna prove it?
Close the form altogether - or even put it in design view - and rerun the
query. Now the query will present you with an input box to enter the date
(parameter) that's missing. The only way to fix it is to run the query in,
say, the EDate OnExit or OnUpdate event, or the form's OnUpdate event.

If you're running the query from within VBA, all bets are off; meaning that
you need a different fix altogether.

Hope this helps,

Sam

I have a query that works when I put in the date but not when I put in the date field name from a form. What's odd about this is that I use the date field in the same query and it works fine there.
Here's the SQL:

SELECT [Latitude Rate Plans].Number, [Latitude Rate Plans].Type, [Latitude Data Units].Template, Count([Latitude Data Units].Unit) AS CountOfUnit INTO TempActiveUnitsTW
from [Latitude Data Units], [Latitude Rate Plans]
where [Latitude Rate Plans].Code = [Latitude Data Units].Template
and (([Latitude Data Units].InDate <= [Forms]![Main]![EDate]) and (([Latitude Data Units].TmDate >= [Forms]![Main]![EDate]) OR (([Latitude Data Units].TmDate) is Null)))
and (([Latitude Data Units].Template) Is Not Null)
GROUP BY [Latitude Rate Plans].Number, [Latitude Rate Plans].Type, [Latitude Data Units].Template
ORDER BY [Latitude Rate Plans].Type;

If I replace [Latitude Data Units].InDate <= [Forms]![Main]![EDate] with [Latitude Data Units].InDate <= #6/24/2006# which is the date entered in the form, it works fine and I get the results that I want. Any ideas as to what's wrong?

 

[efgh]

It helped a lot although my case seemed to be the opposite. With the form
in Form View, the query ran fine and gave me the expected results. With the
form in Design View, the query doesn't run. Thanks for the help.

I bet I know what's happening. Are you running the query while the form's
open in form view? If so, the query can't see the form's controls' values
correctly, and the query will always run - with wrong results. Wanna prove
it?
Close the form altogether - or even put it in design view - and rerun the
query. Now the query will present you with an input box to enter the date
(parameter) that's missing. The only way to fix it is to run the query in,
say, the EDate OnExit or OnUpdate event, or the form's OnUpdate event.

If you're running the query from within VBA, all bets are off; meaning
that
you need a different fix altogether.

Hope this helps,

Sam

I have a query that works when I put in the date but not when I put in the
date field name from a form. What's odd about this is that I use the date
field in the same query and it works fine there.
Here's the SQL:

SELECT [Latitude Rate Plans].Number, [Latitude Rate Plans].Type, [Latitude
Data Units].Template, Count([Latitude Data Units].Unit) AS CountOfUnit
INTO TempActiveUnitsTW
from [Latitude Data Units], [Latitude Rate Plans]
where [Latitude Rate Plans].Code = [Latitude Data Units].Template
and (([Latitude Data Units].InDate <= [Forms]![Main]![EDate]) and
(([Latitude Data Units].TmDate >= [Forms]![Main]![EDate]) OR (([Latitude
Data Units].TmDate) is Null)))
and (([Latitude Data Units].Template) Is Not Null)
GROUP BY [Latitude Rate Plans].Number, [Latitude Rate Plans].Type,
[Latitude Data Units].Template
ORDER BY [Latitude Rate Plans].Type;

If I replace [Latitude Data Units].InDate <= [Forms]![Main]![EDate] with
[Latitude Data Units].InDate <= #6/24/2006# which is the date entered in
the form, it works fine and I get the results that I want. Any ideas as
to what's wrong?