Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a query qryActiveEnv to give me only the active envelope numbers for a church. If I put
    (<551 Or >799) And <6000
    directly into the criteria field, it runs correctly.

    I want the end user to be able to edit these numbers to reflect changes to them over time, so I set up a storage table to store this value and have a form which displays it and the reference to [forms]![frmAdminFunctions]![txtEnvRng]

    The query runs but returns no records. I tried being explicit (((tblEnvelopeAssignment.EnvelopeNumber)<551 Or (tblEnvelopeAssignment.EnvelopeNumber)>799) And (tblEnvelopeAssignment.EnvelopeNumber)<6000))) but this doesn't work either.

    Clearly the query isn't getting info it needs from the text box. EnvelopeNumber is a number field. Right now, txtEnvRng is a textbox. Is this perhaps part of the problem?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can only replace constant values such as 551 and 799 with references to a control on a form, for example

    <[Forms]![frmParameters]![txtFirstValue] Or Between [Forms]![frmParameters]![txtSecondValue] And [Forms]![frmParameters]![txtThirdValue]

    You can't replace an entire condition including operators such as < and Or with a reference to a control, because the condition as a whole is not a string but an expression.

    As a workaround you could put a command button on the form that assembles the SQL string for the query, then use this SQL string directly or to set it as the SQL of the query.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='789546' date='18-Aug-2009 10:33']You can only replace constant values such as 551 and 799 with references to a control on a form, for example

    <[Forms]![frmParameters]![txtFirstValue] Or Between [Forms]![frmParameters]![txtSecondValue] And [Forms]![frmParameters]![txtThirdValue]

    You can't replace an entire condition including operators such as < and Or with a reference to a control, because the condition as a whole is not a string but an expression.

    As a workaround you could put a command button on the form that assembles the SQL string for the query, then use this SQL string directly or to set it as the SQL of the query.[/quote]

    OK. Thanks. This is a once per year function, so it isn't crucial. I'll do the SQL thing since the operators will remain constant it is just the actual numbers that will fluctuate. In fact, for the forseeable future, it is only the 551 which is likely to change, so it shouldn't be a big deal to insert it into a SQL string at all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •