Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with Parameter Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have the following records in a table with text fields strAddrName and strAddrNo

    The only way I can retrieve these records is to enter

    Start Street Arrow Trl
    End Street Arrow Trl
    Start Number 14
    End Number 9

    <pre>Address Name Expr3
    Arrow Trl 4
    Arrow Trl 9
    Arrow Trl 9
    Arrow Trl 14
    Arrow Trl 18
    Arrow Trl 20
    Arrow Trl 25
    Arrow Trl 25
    </pre>


    Is there a way to change the query parameter entrys as follows to achieve same results?

    Start Street Arrow Trl
    End Street Arrow Trl
    Start Number 4
    End Number 25

    Thanks, John

    <pre>SELECT DISTINCT qrytblBPermit.strAddrNameNo,
    qrytblBPermit.strOwnerName,
    qrytblBPermit.strSBLNo,
    qrytblBPermit.strPermitNo,
    qrytblBPermit.strAddrName,
    IIf(IsNull([strAddrNo]),True,IsNumeric([strAddrNo])) AS Expr1,
    IIf(IsNull([strAddrNo]),0,Val([strAddrNo])) AS Expr2,
    qrytblBPermit.strRecordType,
    qrytblBPermit.strAddrName,
    Val([strAddrNo]) AS Expr3
    FROM qrytblBPermit
    WHERE (((qrytblBPermit.strRecordType)=gloGetValue("Permi tTypeCode"))
    AND (((qrytblBPermit.strAddrName)>=[Enter Start Street, or OK for All]
    Or (qrytblBPermit.strAddrName) Like [Enter Start Street, or OK for All] & "*")
    And ((qrytblBPermit.strAddrName)<=[Enter End Street, or OK for All]
    Or (qrytblBPermit.strAddrName) Like [Enter End Street, or OK for All] & "*"))
    AND (((Val([strAddrNo]))>=[Enter Start Number, or OK for All]
    Or (Val([strAddrNo])) Like [Enter Start Number, or OK for All] & "*")
    And ((Val([strAddrNo]))<=[Enter End Number, or OK for All]
    Or (Val([strAddrNo])) Like [Enter End Number, or OK for All] & "*")))
    ORDER BY qrytblBPermit.strAddrName,
    IIf(IsNull([strAddrNo]),True,IsNumeric([strAddrNo])),
    IIf(IsNull([strAddrNo]),0,Val([strAddrNo]));
    </pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with Parameter Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Why don't you define the address number as a number field?

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Parameter Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    This was set up by another developer, I can

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with Parameter Query? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Declare the parameters explicitly in Query | Parameters... and set the data type for [Enter Start Number, or OK for All] and for [Enter End Number, or OK for All] to Long Integer. That tells Access to treat the condition as numeric instead of alphanumeric.

Posting Permissions

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