query criteria

#1 (**permalink**) 28-Oct-2005, 18:00 PM

Hello All,

Here is theSQL view in my query

SELECT tblCaseCountReport.dtmStartDate, tblCaseCountReport.intFlavorCounter,
tblCaseCountReport.strShift, tblCaseCountReport.dtmStartTime,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/6042-query-criteria.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
tblCaseCountReport.strFlavor, tblCaseCountReport.strSize,
tblCaseCountReport.strLineNumber,
60*Hour([dtmStartTime])+Minute([dtmStartTime])-270 AS Expr2,
IIf([expr2]<0,DateAdd("d",-1,[dtmStartDate]),[dtmStartDate]) AS
ProductionDate
FROM tblCaseCountReport

I want to add a criteria in the dtmStartDate field, Between [Type the
beginning date:] And [Type the ending date:] . I tried using this but got a
message box that said HAVING clause (tblCaseCountReport.dtmStartDate Between
[Type the beginning date:] And [Type the ending date:]) without grouping or
aggregation.

Is this because of the expression in my ProductionDate field?

TIA,
Jessica

#2 (**permalink**) 28-Oct-2005, 18:00 PM

IIRC, the use of [expr2] in the "subsequent" IIF expression is generally
unreliable and may give this sort of problem since JET process the Query in
some order, NOT necessarily from left to right as you assumed.

If you don't need [Expr2] except for the "subsequent" IIF, include the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
calculation in the IIF expression itself.

Try:

SELECT tblCaseCountReport.dtmStartDate, tblCaseCountReport.intFlavorCounter,
tblCaseCountReport.strShift, tblCaseCountReport.dtmStartTime,
tblCaseCountReport.strFlavor, tblCaseCountReport.strSize,
tblCaseCountReport.strLineNumber,
IIf([dtmStartTime]<#04:30#,DateAdd("d",-1,[dtmStartDate]),[dtmStartDate]) AS
ProductionDate
FROM tblCaseCountReport

If you need help with the Parameters, post your attempted SQL WITH the
Parameters.

--
HTH
Van T. Dinh
MVP (Access)

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
"Jessica" wrote in message
news:GXa8f.23530$Bv6.12793@twister.nyroc.rr.com...
> Hello All,
>
> Here is theSQL view in my query
>
> SELECT tblCaseCountReport.dtmStartDate,
> tblCaseCountReport.intFlavorCounter, tblCaseCountReport.strShift,
> tblCaseCountReport.dtmStartTime, tblCaseCountReport.strFlavor,
> tblCaseCountReport.strSize, tblCaseCountReport.strLineNumber,
> 60*Hour([dtmStartTime])+Minute([dtmStartTime])-270 AS Expr2,
> IIf([expr2]<0,DateAdd("d",-1,[dtmStartDate]),[dtmStartDate]) AS
> ProductionDate
> FROM tblCaseCountReport
>
>
> I want to add a criteria in the dtmStartDate field, Between [Type the
> beginning date:] And [Type the ending date:] . I tried using this but got
> a message box that said HAVING clause (tblCaseCountReport.dtmStartDate
> Between [Type the beginning date:] And [Type the ending date:]) without
> grouping or aggregation.
>
> Is this because of the expression in my ProductionDate field?
>
>
> TIA,
> Jessica
>

#3 (**permalink**) 28-Oct-2005, 18:00 PM

Van
How you know what I am trying to do by looking at my SQL is beyond me. That
is amazing!!! The IT guy at work came up with that query and lost me within
the first 5 minutes. I guess he made the Expr2 field to have the query
subtract 270 minutes from the start time and if it was a negative number
then subtract a day from the start date. Anyway I entered in what you had
posted and the results were the same so I am going to keep it your way.
Could I still have the user enter a date range? I tried it again and got the
same message box as I posted before.

Thanks Van
Jess

"Van T. Dinh" wrote in message
news:%23r5emtz2FHA.3296@TK2MSFTNGP09.phx.gbl...
> IIRC, the use of [expr2] in the "subsequent" IIF expression is generally
> unreliable and may give this sort of problem since JET process the Query
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
> in some order, NOT necessarily from left to right as you assumed.
>
> If you don't need [Expr2] except for the "subsequent" IIF, include the
> calculation in the IIF expression itself.
>
> Try:
>
> SELECT tblCaseCountReport.dtmStartDate,
> tblCaseCountReport.intFlavorCounter,
> tblCaseCountReport.strShift, tblCaseCountReport.dtmStartTime,
> tblCaseCountReport.strFlavor, tblCaseCountReport.strSize,
> tblCaseCountReport.strLineNumber,
> IIf([dtmStartTime]<#04:30#,DateAdd("d",-1,[dtmStartDate]),[dtmStartDate])
> AS ProductionDate
> FROM tblCaseCountReport
>
> If you need help with the Parameters, post your attempted SQL WITH the
> Parameters.
>
>
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
>
>
> "Jessica" wrote in message
> news:GXa8f.23530$Bv6.12793@twister.nyroc.rr.com...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
>> Hello All,
>>
>> Here is theSQL view in my query
>>
>> SELECT tblCaseCountReport.dtmStartDate,
>> tblCaseCountReport.intFlavorCounter, tblCaseCountReport.strShift,
>> tblCaseCountReport.dtmStartTime, tblCaseCountReport.strFlavor,
>> tblCaseCountReport.strSize, tblCaseCountReport.strLineNumber,
>> 60*Hour([dtmStartTime])+Minute([dtmStartTime])-270 AS Expr2,
>> IIf([expr2]<0,DateAdd("d",-1,[dtmStartDate]),[dtmStartDate]) AS
>> ProductionDate
>> FROM tblCaseCountReport
>>
>>
>> I want to add a criteria in the dtmStartDate field, Between [Type the
>> beginning date:] And [Type the ending date:] . I tried using this but got
>> a message box that said HAVING clause (tblCaseCountReport.dtmStartDate
>> Between [Type the beginning date:] And [Type the ending date:]) without
>> grouping or aggregation.
>>
>> Is this because of the expression in my ProductionDate field?
>>
>>
>> TIA,
>> Jessica
>>
>
>

#4 (**permalink**) 28-Oct-2005, 18:00 PM

Post the SQL of your attempt to use the Parameter(s).

--
HTH
Van T. Dinh
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
MVP (Access)

"Jessica" wrote in message
news:Xcc8f.23836$Bv6.3811@twister.nyroc.rr.com...
> Van
> How you know what I am trying to do by looking at my SQL is beyond me.
> That is amazing!!! The IT guy at work came up with that query and lost me
> within the first 5 minutes. I guess he made the Expr2 field to have the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
> query subtract 270 minutes from the start time and if it was a negative
> number then subtract a day from the start date. Anyway I entered in what
> you had posted and the results were the same so I am going to keep it your
> way. Could I still have the user enter a date range? I tried it again and
> got the same message box as I posted before.
>
> Thanks Van
> Jess
>
>

#5 (**permalink**) 28-Oct-2005, 18:00 PM

SELECT tblCaseCountReport.dtmStartDate, tblCaseCountReport.intFlavorCounter,
tblCaseCountReport.strShift, tblCaseCountReport.dtmStartTime,
tblCaseCountReport.strFlavor, tblCaseCountReport.strSize,
tblCaseCountReport.strLineNumber, IIf([dtmStartTime]<#12/30/1899
4:30:0#,DateAdd("d",-1,[dtmStartDate]),[dtmStartDate]) AS ProductionDate
FROM tblCaseCountReport
HAVING (((tblCaseCountReport.dtmStartDate) Between [Type the beginning
date:] And [Type the ending date:]));

"Van T. Dinh" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
news:ODJDQy02FHA.3188@TK2MSFTNGP12.phx.gbl...
> Post the SQL of your attempt to use the Parameter(s).
>
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
>
>
> "Jessica" wrote in message
> news:Xcc8f.23836$Bv6.3811@twister.nyroc.rr.com...
>> Van
>> How you know what I am trying to do by looking at my SQL is beyond me.
>> That is amazing!!! The IT guy at work came up with that query and lost me
>> within the first 5 minutes. I guess he made the Expr2 field to have the
>> query subtract 270 minutes from the start time and if it was a negative
>> number then subtract a day from the start date. Anyway I entered in what
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
>> you had posted and the results were the same so I am going to keep it
>> your way. Could I still have the user enter a date range? I tried it
>> again and got the same message box as I posted before.
>>
>> Thanks Van
>> Jess
>>
>>
>
>

#6 (**permalink**) 28-Oct-2005, 18:00 PM

Use WHERE instead of HAVING. You only use HAVING if you use GROUP BY Clause
in the SQL.

You will need to check exactly what you require in light of the Calculated
Field [ProductionDate] . I am not sure whether you want to use dteStartDate
or [ProductionDate] in the WHERE clause.

--
HTH
Van T. Dinh
MVP (Access)

"Jessica" wrote in message
news:6Jd8f.112707$7b6.23911@twister.nyroc.rr.com.. .
> SELECT tblCaseCountReport.dtmStartDate,
> tblCaseCountReport.intFlavorCounter, tblCaseCountReport.strShift,
> tblCaseCountReport.dtmStartTime, tblCaseCountReport.strFlavor,
> tblCaseCountReport.strSize, tblCaseCountReport.strLineNumber,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
> IIf([dtmStartTime]<#12/30/1899
> 4:30:0#,DateAdd("d",-1,[dtmStartDate]),[dtmStartDate]) AS ProductionDate
> FROM tblCaseCountReport
> HAVING (((tblCaseCountReport.dtmStartDate) Between [Type the beginning
> date:] And [Type the ending date:]));
>

#7 (**permalink**) 29-Oct-2005, 12:35 PM

Van

Without having the user enter in a date the report displays as I want it to
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
but lists all dates entered in the table. I tried using [Type the beginning
date:] And [Type the ending date:] in the field ProductionDate but the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
results are not coming out the same. Can I use a parameter like this with
this expression?

Thanks,
Jess

"Van T. Dinh" wrote in message
news:ucBDj212FHA.2600@tk2msftngp13.phx.gbl...
> Use WHERE instead of HAVING. You only use HAVING if you use GROUP BY
> Clause in the SQL.
>
> You will need to check exactly what you require in light of the Calculated
> Field [ProductionDate] . I am not sure whether you want to use
> dteStartDate or [ProductionDate] in the WHERE clause.
>
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
>
>
> "Jessica" wrote in message
> news:6Jd8f.112707$7b6.23911@twister.nyroc.rr.com.. .
>> SELECT tblCaseCountReport.dtmStartDate,
>> tblCaseCountReport.intFlavorCounter, tblCaseCountReport.strShift,
>> tblCaseCountReport.dtmStartTime, tblCaseCountReport.strFlavor,
>> tblCaseCountReport.strSize, tblCaseCountReport.strLineNumber,
>> IIf([dtmStartTime]<#12/30/1899
>> 4:30:0#,DateAdd("d",-1,[dtmStartDate]),[dtmStartDate]) AS ProductionDate
>> FROM tblCaseCountReport
>> HAVING (((tblCaseCountReport.dtmStartDate) Between [Type the beginning
>> date:] And [Type the ending date:]));
>>
>
>

#8 (**permalink**) 31-Oct-2005, 11:19 AM

Please post your latest SQL String with Parameters.

--
HTH
Van T. Dinh
MVP (Access)

"Jessica" wrote in message
news:SEB8f.99627$K91.94999@twister.nyroc.rr.com...
> Van
>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
> Without having the user enter in a date the report displays as I want it
> to but lists all dates entered in the table. I tried using [Type the
> beginning date:] And [Type the ending date:] in the field ProductionDate
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
> but the results are not coming out the same. Can I use a parameter like
> this with this expression?
>
> Thanks,
> Jess
>

#9 (**permalink**) 01-Nov-2005, 13:40 PM

Van

Here is my SQL

SELECT tblCaseCountReport.dtmStartDate, tblCaseCountReport.intFlavorCounter,
tblCaseCountReport.strShift, tblCaseCountReport.dtmStartTime,
tblCaseCountReport.strFlavor, tblCaseCountReport.strSize,
tblCaseCountReport.strLineNumber, IIf([dtmStartTime]<#12/30/1899
4:30:0#,DateAdd("d",-1,[dtmStartDate]),[dtmStartDate]) AS ProductionDate
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
FROM tblCaseCountReport;

I did not enter in the date range parameter because the results are not
coming out like they should. The SQL above works perfectly but reports
everyday. I wanted to add a user defined date range. I tried putting it into
the ProductionDate field and using a date range 10/25/05-10/25/05 but it
doesn't give me the records past midnight - 4:30. Using the same date I then
I tried putting it into the dtmStartDate field and the results were from
0:00 - 4:30 were under the date 10/24/05 and from 4:30 - midnight on
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=6042
10/25/05.

I think I'm making this sound more difficult than it really is. You see what
I am trying to do we have two shifts For example Shift A starts on 10/25/05
at 4:30am - 16:30pm then Shift B starts on 10/25/05 at 16:30pm - 10/26/05
4:30am I want to build a query where the user would type a date or date
range for example 10/25/05 - 10/25/05 and the results would be for the time
listed above. What is messing me up is the system clock changing the day
after midnight. Is this possible to do?

TIA,
Jessica

"Van T. Dinh" wrote in message
news:uPN6Jub3FHA.3296@TK2MSFTNGP09.phx.gbl...
> Please post your latest SQL String with Parameters.
>
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
>
>
> "Jessica" wrote in message
> news:SEB8f.99627$K91.94999@twister.nyroc.rr.com...
>> Van
>>
>> Without having the user enter in a date the report displays as I want it
>> to but lists all dates entered in the table. I tried using [Type the
>> beginning date:] And [Type the ending date:] in the field ProductionDate
>> but the results are not coming out the same. Can I use a parameter like
>> this with this expression?
>>
>> Thanks,
>> Jess
>>
>
>