Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    May 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How Do You Filter Out Text In Select?

    I have a column in a table called 'clients' and 'tracking' and tracking is filled with numbers and text.

    Now i want to filter out the text and display only the clients with a tracking that contains numbers.

    if (IsNumeric(objRS("tracking"))) then

    Set objRS = Server.CreateObject("ADODB.Recordset")
    strSQL = "SELECT * FROM tbl_jobs WHERE DATEVALUE(printdate) BETWEEN " & AccessDate(firstdate) & " AND " & AccessDate(firstdate+6) & "AND tracking <>' " & " " & " ' AND delivery=' " & "adfast" & " ' "
    objRS.Open strSQL, objconn

    however doesnt seem to work! displays the numbers and text - any ideas?

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Have you tried using InStr?

    Code:
    SELECT * FROM tbl_jobs WHERE DATEVALUE(printdate) BETWEEN " & AccessDate(firstdate) & " AND " & AccessDate(firstdate+6) & "AND InStr(tracking,' ') = 0 AND InStr(delivery,'adfast') = 0

  • #3
    New to the CF scene
    Join Date
    May 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    that filters out the numbers, and displays the text. not the other way round as wanted

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Can you post a record (or part of a record) so that I can play with it to get it exactly the way you want it?

    You may also want to try changing the InStr() from =0 to <>0.


  •  

    Posting Permissions

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