Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

query criteria

Discussion in 'Information Technology' started by Jessica, Oct 28, 2005.

Tags:
  1. Jessica

    Jessica
    Expand Collapse
    Guest

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

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    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)



    "Jessica" <dfizer@hotmail.com> 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
    >
     
  4. Jessica

    Jessica
    Expand Collapse
    Guest

    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" <VanThien.Dinh@discussions.microsoft.com> 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
    > 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" <dfizer@hotmail.com> 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
    >>

    >
    >
     
  5. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

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

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Jessica" <dfizer@hotmail.com> 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
    > 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. Jessica

    Jessica
    Expand Collapse
    Guest

    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" <VanThien.Dinh@discussions.microsoft.com> wrote in message
    news:ODJDQy02FHA.3188@TK2MSFTNGP12.phx.gbl...
    > Post the SQL of your attempt to use the Parameter(s).
    >
    > --
    > HTH
    > Van T. Dinh
    > MVP (Access)
    >
    >
    >
    > "Jessica" <dfizer@hotmail.com> 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
    >> 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
    >>
    >>

    >
    >
     
  7. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    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" <dfizer@hotmail.com> 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. Jessica

    Jessica
    Expand Collapse
    Guest

    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" <VanThien.Dinh@discussions.microsoft.com> 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" <dfizer@hotmail.com> 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:]));
    >>

    >
    >
     
  9. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    Please post your latest SQL String with Parameters.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Jessica" <dfizer@hotmail.com> 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
    >
     
  10. Jessica

    Jessica
    Expand Collapse
    Guest

    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


    "Van T. Dinh" <VanThien.Dinh@discussions.microsoft.com> wrote in message
    news:uPN6Jub3FHA.3296@TK2MSFTNGP09.phx.gbl...
    > Please post your latest SQL String with Parameters.
    >
    > --
    > HTH
    > Van T. Dinh
    > MVP (Access)
    >
    >
    >
    > "Jessica" <dfizer@hotmail.com> 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
    >>

    >
    >
     
  11. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    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)



    "Jessica" <dfizer@hotmail.com> wrote in message
    news:M_w9f.101781$K91.57844@twister.nyroc.rr.com...
    > 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
    >
    >
     

Share This Page