Welcome to SPN

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

Sign Up Now!

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

Share This Page