Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

syntax error in query expression

Discussion in 'Information Technology' started by access user, Jul 28, 2006.

  1. access user

    access user
    Expand Collapse
    Guest

    WHERE ((tblTenant.ContractEndDate)>=Date()) AND
    (tblVarRent.VariationNo)=(SELECT Max(tblVarRent.VariationNo)) AS MaxVarNo
    FROM tblVarRent

    please advise
    TIA
    James
     
  2. Loading...


  3. OP
    Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    Try

    WHERE tblTenant.ContractEndDate>=Date() AND
    tblVarRent.VariationNo In (SELECT Max(tblVarRent.VariationNo) AS MaxVarNo
    FROM tblVarRent)

    --
    Good Luck
    BS"D


    "access user" wrote:

    > WHERE ((tblTenant.ContractEndDate)>=Date()) AND
    > (tblVarRent.VariationNo)=(SELECT Max(tblVarRent.VariationNo)) AS MaxVarNo
    > FROM tblVarRent
    >
    > please advise
    > TIA
    > James
     
  4. OP
    access user

    access user
    Expand Collapse
    Guest

    Thanks Ofer - the syntax works, *but*, the resulting recordset is not what i
    was after. - ie, i *only* get those records where VariationNo is maximum, but
    what i want is:

    for any given propertyNo, *all* records with a contract end date>=date()
    AND then restrict the above to just those withe the highest variationNo for
    any given property. For example, in the table below:

    RecordIDPropertyNoContractEndDateVariationNo
    1 100007 03/01/2020 2
    2 100007 03/01/2020 1
    3 100014 24/04/2021 3
    4 100014 24/04/2021 2
    5 100014 24/04/2021 1
    6 100043 24/06/2009 3
    7 100043 24/06/2009 2
    8 100043 24/06/2009 1
    9 100045 25/09/2025 2
    10 100045 25/09/2025 1

    I need lines 1, 3, 6 and 9. I may have misled you in my original question.
    Sorry.

    TIA
    James

    "Ofer Cohen" wrote:

    > Try
    >
    > WHERE tblTenant.ContractEndDate>=Date() AND
    > tblVarRent.VariationNo In (SELECT Max(tblVarRent.VariationNo) AS MaxVarNo
    > FROM tblVarRent)
    >
    > --
    > Good Luck
    > BS"D
    >
    >
    > "access user" wrote:
    >
    > > WHERE ((tblTenant.ContractEndDate)>=Date()) AND
    > > (tblVarRent.VariationNo)=(SELECT Max(tblVarRent.VariationNo)) AS MaxVarNo
    > > FROM tblVarRent
    > >
    > > please advise
    > > TIA
    > > James
     
  5. OP
    Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    Try this

    WHERE tblTenant.ContractEndDate>=Date() AND
    tblVarRent.VariationNo In (SELECT Max(T1.VariationNo) AS MaxVarNo
    FROM tblVarRent As T1 Where T1.PropertyNo = tblVarRent.[PropertyNo])

    --
    Good Luck
    BS"D


    "access user" wrote:

    > Thanks Ofer - the syntax works, *but*, the resulting recordset is not what i
    > was after. - ie, i *only* get those records where VariationNo is maximum, but
    > what i want is:
    >
    > for any given propertyNo, *all* records with a contract end date>=date()
    > AND then restrict the above to just those withe the highest variationNo for
    > any given property. For example, in the table below:
    >
    > RecordIDPropertyNoContractEndDateVariationNo
    > 1 100007 03/01/2020 2
    > 2 100007 03/01/2020 1
    > 3 100014 24/04/2021 3
    > 4 100014 24/04/2021 2
    > 5 100014 24/04/2021 1
    > 6 100043 24/06/2009 3
    > 7 100043 24/06/2009 2
    > 8 100043 24/06/2009 1
    > 9 100045 25/09/2025 2
    > 10 100045 25/09/2025 1
    >
    > I need lines 1, 3, 6 and 9. I may have misled you in my original question.
    > Sorry.
    >
    > TIA
    > James
    >
    > "Ofer Cohen" wrote:
    >
    > > Try
    > >
    > > WHERE tblTenant.ContractEndDate>=Date() AND
    > > tblVarRent.VariationNo In (SELECT Max(tblVarRent.VariationNo) AS MaxVarNo
    > > FROM tblVarRent)
    > >
    > > --
    > > Good Luck
    > > BS"D
    > >
    > >
    > > "access user" wrote:
    > >
    > > > WHERE ((tblTenant.ContractEndDate)>=Date()) AND
    > > > (tblVarRent.VariationNo)=(SELECT Max(tblVarRent.VariationNo)) AS MaxVarNo
    > > > FROM tblVarRent
    > > >
    > > > please advise
    > > > TIA
    > > > James
     
  6. OP
    access user

    access user
    Expand Collapse
    Guest

    Thanks - sorry for being dumb, but I'm supposed to replace T1 with the table
    name right? I replaced both your T1's with

    tblProperty (which is the table that contains the property numbers) but I
    get the same set of records as I would get with only the
    contractEnddate>=date() restriction

    the extra restriction after the AND is not doing anything, although, once
    again, the syntax is accepted!!

    James

    "Ofer Cohen" wrote:

    > Try this
    >
    > WHERE tblTenant.ContractEndDate>=Date() AND
    > tblVarRent.VariationNo In (SELECT Max(T1.VariationNo) AS MaxVarNo
    > FROM tblVarRent As T1 Where T1.PropertyNo = tblVarRent.[PropertyNo])
    >
    > --
    > Good Luck
    > BS"D
    >
    >
    > "access user" wrote:
    >
    > > Thanks Ofer - the syntax works, *but*, the resulting recordset is not what i
    > > was after. - ie, i *only* get those records where VariationNo is maximum, but
    > > what i want is:
    > >
    > > for any given propertyNo, *all* records with a contract end date>=date()
    > > AND then restrict the above to just those withe the highest variationNo for
    > > any given property. For example, in the table below:
    > >
    > > RecordIDPropertyNoContractEndDateVariationNo
    > > 1 100007 03/01/2020 2
    > > 2 100007 03/01/2020 1
    > > 3 100014 24/04/2021 3
    > > 4 100014 24/04/2021 2
    > > 5 100014 24/04/2021 1
    > > 6 100043 24/06/2009 3
    > > 7 100043 24/06/2009 2
    > > 8 100043 24/06/2009 1
    > > 9 100045 25/09/2025 2
    > > 10 100045 25/09/2025 1
    > >
    > > I need lines 1, 3, 6 and 9. I may have misled you in my original question.
    > > Sorry.
    > >
    > > TIA
    > > James
    > >
    > > "Ofer Cohen" wrote:
    > >
    > > > Try
    > > >
    > > > WHERE tblTenant.ContractEndDate>=Date() AND
    > > > tblVarRent.VariationNo In (SELECT Max(tblVarRent.VariationNo) AS MaxVarNo
    > > > FROM tblVarRent)
    > > >
    > > > --
    > > > Good Luck
    > > > BS"D
    > > >
    > > >
    > > > "access user" wrote:
    > > >
    > > > > WHERE ((tblTenant.ContractEndDate)>=Date()) AND
    > > > > (tblVarRent.VariationNo)=(SELECT Max(tblVarRent.VariationNo)) AS MaxVarNo
    > > > > FROM tblVarRent
    > > > >
    > > > > please advise
    > > > > TIA
    > > > > James
     
  7. OP
    Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    Another way of doing it, mybe it will be more clear

    WHERE tblTenant.ContractEndDate>=Date() AND
    tblVarRent.VariationNo = DMax("VariationNo","tblVarRent", "PropertyNo = " &
    [PropertyNo])

    If the PropertyNo field is text type, then change it to
    WHERE tblTenant.ContractEndDate>=Date() AND
    tblVarRent.VariationNo = DMax("VariationNo","tblVarRent", "PropertyNo = '"
    & [PropertyNo] & "'")



    --
    Good Luck
    BS"D


    "Ofer Cohen" wrote:

    > Try this
    >
    > WHERE tblTenant.ContractEndDate>=Date() AND
    > tblVarRent.VariationNo In (SELECT Max(T1.VariationNo) AS MaxVarNo
    > FROM tblVarRent As T1 Where T1.PropertyNo = tblVarRent.[PropertyNo])
    >
    > --
    > Good Luck
    > BS"D
    >
    >
    > "access user" wrote:
    >
    > > Thanks Ofer - the syntax works, *but*, the resulting recordset is not what i
    > > was after. - ie, i *only* get those records where VariationNo is maximum, but
    > > what i want is:
    > >
    > > for any given propertyNo, *all* records with a contract end date>=date()
    > > AND then restrict the above to just those withe the highest variationNo for
    > > any given property. For example, in the table below:
    > >
    > > RecordIDPropertyNoContractEndDateVariationNo
    > > 1 100007 03/01/2020 2
    > > 2 100007 03/01/2020 1
    > > 3 100014 24/04/2021 3
    > > 4 100014 24/04/2021 2
    > > 5 100014 24/04/2021 1
    > > 6 100043 24/06/2009 3
    > > 7 100043 24/06/2009 2
    > > 8 100043 24/06/2009 1
    > > 9 100045 25/09/2025 2
    > > 10 100045 25/09/2025 1
    > >
    > > I need lines 1, 3, 6 and 9. I may have misled you in my original question.
    > > Sorry.
    > >
    > > TIA
    > > James
    > >
    > > "Ofer Cohen" wrote:
    > >
    > > > Try
    > > >
    > > > WHERE tblTenant.ContractEndDate>=Date() AND
    > > > tblVarRent.VariationNo In (SELECT Max(tblVarRent.VariationNo) AS MaxVarNo
    > > > FROM tblVarRent)
    > > >
    > > > --
    > > > Good Luck
    > > > BS"D
    > > >
    > > >
    > > > "access user" wrote:
    > > >
    > > > > WHERE ((tblTenant.ContractEndDate)>=Date()) AND
    > > > > (tblVarRent.VariationNo)=(SELECT Max(tblVarRent.VariationNo)) AS MaxVarNo
    > > > > FROM tblVarRent
    > > > >
    > > > > please advise
    > > > > TIA
    > > > > James
     
  8. OP
    access user

    access user
    Expand Collapse
    Guest

    Ofer - if ever there was a Nordic God of SQL you would be it!! The query is
    returning approx the no of lines expected - I have just got to check in
    detail, but I think that's done it!! Thanks!!!
    James

    "Ofer Cohen" wrote:

    > Another way of doing it, mybe it will be more clear
    >
    > WHERE tblTenant.ContractEndDate>=Date() AND
    > tblVarRent.VariationNo = DMax("VariationNo","tblVarRent", "PropertyNo = " &
    > [PropertyNo])
    >
    > If the PropertyNo field is text type, then change it to
    > WHERE tblTenant.ContractEndDate>=Date() AND
    > tblVarRent.VariationNo = DMax("VariationNo","tblVarRent", "PropertyNo = '"
    > & [PropertyNo] & "'")
    >
    >
    >
    > --
    > Good Luck
    > BS"D
    >
    >
    > "Ofer Cohen" wrote:
    >
    > > Try this
    > >
    > > WHERE tblTenant.ContractEndDate>=Date() AND
    > > tblVarRent.VariationNo In (SELECT Max(T1.VariationNo) AS MaxVarNo
    > > FROM tblVarRent As T1 Where T1.PropertyNo = tblVarRent.[PropertyNo])
    > >
    > > --
    > > Good Luck
    > > BS"D
    > >
    > >
    > > "access user" wrote:
    > >
    > > > Thanks Ofer - the syntax works, *but*, the resulting recordset is not what i
    > > > was after. - ie, i *only* get those records where VariationNo is maximum, but
    > > > what i want is:
    > > >
    > > > for any given propertyNo, *all* records with a contract end date>=date()
    > > > AND then restrict the above to just those withe the highest variationNo for
    > > > any given property. For example, in the table below:
    > > >
    > > > RecordIDPropertyNoContractEndDateVariationNo
    > > > 1 100007 03/01/2020 2
    > > > 2 100007 03/01/2020 1
    > > > 3 100014 24/04/2021 3
    > > > 4 100014 24/04/2021 2
    > > > 5 100014 24/04/2021 1
    > > > 6 100043 24/06/2009 3
    > > > 7 100043 24/06/2009 2
    > > > 8 100043 24/06/2009 1
    > > > 9 100045 25/09/2025 2
    > > > 10 100045 25/09/2025 1
    > > >
    > > > I need lines 1, 3, 6 and 9. I may have misled you in my original question.
    > > > Sorry.
    > > >
    > > > TIA
    > > > James
    > > >
    > > > "Ofer Cohen" wrote:
    > > >
    > > > > Try
    > > > >
    > > > > WHERE tblTenant.ContractEndDate>=Date() AND
    > > > > tblVarRent.VariationNo In (SELECT Max(tblVarRent.VariationNo) AS MaxVarNo
    > > > > FROM tblVarRent)
    > > > >
    > > > > --
    > > > > Good Luck
    > > > > BS"D
    > > > >
    > > > >
    > > > > "access user" wrote:
    > > > >
    > > > > > WHERE ((tblTenant.ContractEndDate)>=Date()) AND
    > > > > > (tblVarRent.VariationNo)=(SELECT Max(tblVarRent.VariationNo)) AS MaxVarNo
    > > > > > FROM tblVarRent
    > > > > >
    > > > > > please advise
    > > > > > TIA
    > > > > > James
     
Since you're here... we have a small favor to ask...     Become a Supporter      ::     Make a Contribution     


Share This Page