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. 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
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page