query criteria

[Jessica]

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

 

[Van T. Dinh]

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
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.

 

[Jessica]

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

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
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)

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

 

[Van T. Dinh]

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

 

[Jessica]

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]

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.

 

[Jessica]

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

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)

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:]));

 

[guochuanliang]

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

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)

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]

Please post your latest SQL String with Parameters.

 

[Jessica]

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
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
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

Please post your latest SQL String with Parameters.

--
HTH
Van T. Dinh
MVP (Access)

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

 

[Van T. Dinh]

My guess is that the IIF() returns a Variant and therefore your parameter
inputs are not recognised as Date value. Try the SQL like:

****Untested****
SELECT tblCaseCountReport.dtmStartDate, tblCaseCountReport.intFlavorCounter,
tblCaseCountReport.strShift, tblCaseCountReport.dtmStartTime,
tblCaseCountReport.strFlavor, tblCaseCountReport.strSize,
tblCaseCountReport.strLineNumber,
CDate(IIf([dtmStartTime]<#04:30:00#,
DateAdd("d",-1,[dtmStartDate]), [dtmStartDate])) AS ProductionDate
FROM tblCaseCountReport
WHERE CDate(IIf([dtmStartTime]<#04:30:00#,
DateAdd("d",-1,[dtmStartDate]), [dtmStartDate]))
BETWEEN [Enter Start Date:] AND [Enter End Date];
********

You see, when you enter "10/25/2005" and if Access is not expecting a date
value, it can be interpreted as 10 divided by 25 (then) divided by 2005!
Since IIF() returns Variant, it is likely that Access doesn't expect the
corresponding Parameter values are date values and hence interprets them
incorrectly. Hence, I use CDate() to force the data type to date data type.

In addition to the above, you can force Access to interpret the values
entered for the Paramters as date by declaring the data type using the Menu
Query / Parameters ...

OTOH, you can use the logic backwards to place the criteria on the
dtmStartTime and dtmStartDate like:

****Untested again****
SELECT tblCaseCountReport.dtmStartDate, tblCaseCountReport.intFlavorCounter,
tblCaseCountReport.strShift, tblCaseCountReport.dtmStartTime,
tblCaseCountReport.strFlavor, tblCaseCountReport.strSize,
tblCaseCountReport.strLineNumber,
CDate(IIf([dtmStartTime]<#04:30:00#,
DateAdd("d",-1,[dtmStartDate]), [dtmStartDate])) AS ProductionDate
FROM tblCaseCountReport
WHERE ( ([dtmStartDate] + [dtmStartTime])
BETWEEN ([Enter Start Date:] + #04:30:00#)
AND ([Enter End Date] + 1 + #04:29:59#) );

You should see the logic I used to construct the criteria:

* When date/time is earlier than 4:30 of the entered Start Date, the Record
will be excluded since it belongs to the day before or earlier.

* When date/time is on later than than 4:30 of the day after the entered End
Date (I assumed you wanted inclusive range), the Record will be excluded
since it belongs to the day after or later.

* Thus, the above will only select Records that have the derived
ProductionDate between [Start Date] and [End Date] (inclusive).

--
HTH
Van T. Dinh
MVP (Access)

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
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
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