Weird Parameter problem

[Guest]

I was trying to create a form to enter parameters for my query for a date
range. I followed all instructions and could not get my query to work or
recognize the dates in my form. I've gone through all the posts on this
matter, and nothing worked.

=[forms]![frmInpDateRange]![beginDate] And <=[forms]![frmInpDateRange]![endDate]

or

Between CDate([forms]![frmInpDateRange]![beginDate]) And
CDate([forms]![frmInpDateRange]![endDate])

or

Between CDate([Forms]![frmInpDateRange].[beginDate]) And
CDate([Forms]![frmInpDateRange].[endDate])


form is open and filled with valid dates
form referenced correctly
I specified parameter types
I tried CDate
I tried Format
etc..


I finally narrowed it down to the query itself. I forgot about the form and
just tried filtering with direct dates. I found that the query filters fine
if I put in direct dates.

#12/06/2006# And <#01/04/2007#

Between #12/06/2006# And <#01/04/2007#


But as soon as I introduce a parameter, in any way, either by a control on a
form, or by closing the form and having it prompt me to type in the values,
it fails.

Can't figure out rhyme or reason which dates show up. Sometimes NONE of the
dates that come through are between the criteria dates.

the field is a date/time field and contains both date and time, but as I
understand it, it interprets a straight date as 12 midnight of the same date.

HELP!

 

[Van T. Dinh]

1. Make sure that the Field (against which the criteria are used) in your
Table is of DateTime data type. It may be Text value that looks like date
value (unlikely, though).

2. The last criteria you posted:

Between #12/06/2006# And <#01/04/2007#

has an incorrect less than sign (<) but I guess it was a typing mistake on
posting only.

3. I assume with the above criteria, you meant 06 December 2006 and 04
January 2007???

4. If you still can't work out, post relevant Table details and the SQL
String of your Query with Params.

--
HTH
Van T. Dinh
MVP (Access)

I was trying to create a form to enter parameters for my query for a date
range. I followed all instructions and could not get my query to work or
recognize the dates in my form. I've gone through all the posts on this
matter, and nothing worked.

=[forms]![frmInpDateRange]![beginDate] And
<=[forms]![frmInpDateRange]![endDate]

or

Between CDate([forms]![frmInpDateRange]![beginDate]) And
CDate([forms]![frmInpDateRange]![endDate])

or

Between CDate([Forms]![frmInpDateRange].[beginDate]) And
CDate([Forms]![frmInpDateRange].[endDate])


form is open and filled with valid dates
form referenced correctly
I specified parameter types
I tried CDate
I tried Format
etc..


I finally narrowed it down to the query itself. I forgot about the form
and
just tried filtering with direct dates. I found that the query filters
fine
if I put in direct dates.

#12/06/2006# And <#01/04/2007#

Between #12/06/2006# And <#01/04/2007#


But as soon as I introduce a parameter, in any way, either by a control on
a
form, or by closing the form and having it prompt me to type in the
values,
it fails.

Can't figure out rhyme or reason which dates show up. Sometimes NONE of
the
dates that come through are between the criteria dates.

the field is a date/time field and contains both date and time, but as I
understand it, it interprets a straight date as 12 midnight of the same
date.

HELP!

 

[Guest]

2. The last criteria you posted:

Between #12/06/2006# And <#01/04/2007#

has an incorrect less than sign (<) but I guess it was a typing mistake on
posting only.

Yes, this error was only on the post

3. I assume with the above criteria, you meant 06 December 2006 and 04
January 2007???

Yes, correct

4. If you still can't work out, post relevant Table details and the SQL
String of your Query with Params.

SELECT q5.Style_pk AS Style, q5.Buyer_fk AS Buyer, q5.MinXfO AS XFO,
First(q4.FitStatus_fk) AS FitStatus, First(q4.FitRejB) AS FitRejB,
First(q4.Comment) AS Comment, First(q4.MaxCommentDate) AS CommentDate,
First(q4.FitRejX) AS FitRejX, First(q4.Fty) AS Fty, First(q4.PoDate_fk) AS
PODate, First(q4.PoNo_fk) AS PONo, First(q4.XfR) AS XfR,
First(q4.OrderStatus) AS OrderStatus, First(q4.XfD) AS XfD, First(q4.XfCO) AS
XfCO, First(q4.XfCR) AS XfCR, First(q4.XfC) AS XfC, First(q4.Dups) AS Dups,
First(q4.Color_fk) AS Color, First(q4.Pairs) AS Pairs, First(q4.SumPairsS) AS
SumPairsS, First(q4.SumPairsB) AS SumPairsB, First(q4.CoOr) AS CoOr,
First(q4.Reg_fk) AS Reg, First(q4.FlagSO) AS FlagSO, First(q4.FlagSR) AS
FlagSR, First(q4.FlagS) AS FlagS, First(q4.FlagB) AS FlagB
FROM q5BuyerMin AS q5 INNER JOIN q4Uni AS q4 ON (q5.MinXfO = q4.XfO_fk) AND
(q5.Style_pk = q4.Style_pk) AND (q5.Buyer_fk = q4.Buyer_fk)
GROUP BY q5.Style_pk, q5.Buyer_fk, q5.MinXfO
HAVING (((First(q4.Comment)) Is Not Null) AND ((First(q4.MaxCommentDate))
Between CDate([Forms]![frmInpDateRange].[beginDate]) And
CDate([Forms]![frmInpDateRange].[endDate])))
ORDER BY q5.Style_pk, q5.MinXfO;


Thanks.

 

[Van T. Dinh]

Table Details?

It looks to me q4 & q5 are query names??? If that the case, post the SQL
Strings of these also.

Actually, try using the WHERE Clause for the criteria rather than HAVING
Clause and see if this works as the conditions you want should be in the
WHERE Clause ...

In fact, it may be more efficient to put the data criterion in the Query
"q4" rather than the Query you posted.

--
HTH
Van T. Dinh
MVP (Access)

2. The last criteria you posted:

Between #12/06/2006# And <#01/04/2007#

has an incorrect less than sign (<) but I guess it was a typing mistake
on
posting only.

Yes, this error was only on the post

3. I assume with the above criteria, you meant 06 December 2006 and 04
January 2007???

Yes, correct

4. If you still can't work out, post relevant Table details and the SQL
String of your Query with Params.

SELECT q5.Style_pk AS Style, q5.Buyer_fk AS Buyer, q5.MinXfO AS XFO,
First(q4.FitStatus_fk) AS FitStatus, First(q4.FitRejB) AS FitRejB,
First(q4.Comment) AS Comment, First(q4.MaxCommentDate) AS CommentDate,
First(q4.FitRejX) AS FitRejX, First(q4.Fty) AS Fty, First(q4.PoDate_fk) AS
PODate, First(q4.PoNo_fk) AS PONo, First(q4.XfR) AS XfR,
First(q4.OrderStatus) AS OrderStatus, First(q4.XfD) AS XfD, First(q4.XfCO)
AS
XfCO, First(q4.XfCR) AS XfCR, First(q4.XfC) AS XfC, First(q4.Dups) AS
Dups,
First(q4.Color_fk) AS Color, First(q4.Pairs) AS Pairs, First(q4.SumPairsS)
AS
SumPairsS, First(q4.SumPairsB) AS SumPairsB, First(q4.CoOr) AS CoOr,
First(q4.Reg_fk) AS Reg, First(q4.FlagSO) AS FlagSO, First(q4.FlagSR) AS
FlagSR, First(q4.FlagS) AS FlagS, First(q4.FlagB) AS FlagB
FROM q5BuyerMin AS q5 INNER JOIN q4Uni AS q4 ON (q5.MinXfO = q4.XfO_fk)
AND
(q5.Style_pk = q4.Style_pk) AND (q5.Buyer_fk = q4.Buyer_fk)
GROUP BY q5.Style_pk, q5.Buyer_fk, q5.MinXfO
HAVING (((First(q4.Comment)) Is Not Null) AND ((First(q4.MaxCommentDate))
Between CDate([Forms]![frmInpDateRange].[beginDate]) And
CDate([Forms]![frmInpDateRange].[endDate])))
ORDER BY q5.Style_pk, q5.MinXfO;


Thanks.

 

[Guest]

Hey Van,

Thanks so much for your reply! I am finding Access quite maddening, as its
behavior is quite erratic. I did not do anything to change it, and my query
is now working. However, I am still having problems. I will start a new
thread.