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 9 of 9
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Searching for Blank fields

    Hi,

    I'm having problems searching for customers who haven't filled in 3 fields. Basically my database (ms access) is pre-populated and has alot of data missing.

    If I wanted to search for a customer without an address, town and postcode - I woul have though it would be:

    strSQL = "SELECT ID, NAME FROM tblCustomer WHERE ADDRESS = "" AND TOWN = "" AND POSTCODE = "" ORDER BY NAME;"

    But I get the following errors:

    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC Microsoft Access Driver] Syntax error in string in query expression 'ADDRESS = [ AND TOWN = ] AND POSTCODE = " ORDER BY NAME;'.

    Can any1 spot my mistake? i'm puzzled

  • #2
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    As far as I know, you can't have empty values in such a search, so what you'll have to do is check if the value is empty or not and then build your sql request like that:


    Code:
    set rs = Server.CreateObject("ADODB.RecordSet")
    
    SQL = ""
    SQL = SQL & " SELECT ID, NAME"
    SQL = SQL & " FROM tblCustomer"
    SQL = SQL & " WHERE id > 0 " (that's cause you have to put some value after "where")
    if address<> "" then 
    SQL = SQL & " AND address= '" & address
    end if
    if town <> "" then 
    SQL = SQL & " AND town = '" & town
    end if
    if postcode<> "" then 
    SQL = SQL & " AND postcode= " & postcode
    end if
    SQL = SQL & " ORDER BY name"
    
    rs.open SQL, YourConnName
    try this..

    'hope I helped you

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    Thanks for your reply....

    I tried it - but unfortunately it also returned those customers that had filled in their address details...

    Any more ideas?

    Thanks

  • #4
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So you actually want to find the customers who haven't filled in some fields right?

    I think in that case that you'll have to record a default value in your db when a customer lets a field empty, like "1" ("1" being the value that shows that the field hasn't be filled, or the word "empty"). Then you make the same search where you'll search for the people with a 1 somewhere and if the value is not equal to 1, you don't perform the sql search for that precise part.

    something like:

    Code:
    set rs = Server.CreateObject("ADODB.RecordSet")
    
    SQL = ""
    SQL = SQL & " SELECT ID, NAME"
    SQL = SQL & " FROM tblCustomer"
    SQL = SQL & " WHERE id > 0 " (that's cause you have to put some value after "where")
    if address = "1" then 
    SQL = SQL & " AND address= " & address
    end if
    if town = "1" then 
    SQL = SQL & " AND town = " & town
    end if
    if postcode = "1" then 
    SQL = SQL & " AND postcode= " & postcode
    end if
    SQL = SQL & " ORDER BY name"
    
    rs.open SQL, YourConnName
    or with the value "empty":

    Code:
    set rs = Server.CreateObject("ADODB.RecordSet")
    
    SQL = ""
    SQL = SQL & " SELECT ID, NAME"
    SQL = SQL & " FROM tblCustomer"
    SQL = SQL & " WHERE id > 0 " (that's cause you have to put some value after "where")
    if address = "empty" then 
    SQL = SQL & " AND address= '" & address
    end if
    if town = "empty" then 
    SQL = SQL & " AND town = '" & town
    end if
    if postcode = "empty" then 
    SQL = SQL & " AND postcode= " & postcode
    end if
    SQL = SQL & " ORDER BY name"
    
    rs.open SQL, YourConnName
    to do that, you'll have to check the form that is submitted by your customer.

    Code:
    yourfield = request.form("yourfield")
    if yourfield = "" then
    yourfield = "empty" (or yourfield=1)
    else
    yourfield = request.form("yourfield")
    end if
    then you record it in your db so you can perform the search the way you want...

    good luck

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    good idea - but my data is already pre-populated, and there is too many records to start filling in the fields...

    there must be a simple way to return the IDs of people how haven't got a certain field filled in, but i'm stumped!!

    thanks for you help

  • #6
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,068
    Thanks
    0
    Thanked 256 Times in 252 Posts
    you have nested double quotes, you should use single quotes when specifying empty values for fields.

    strSQL = "SELECT ID, NAME FROM tblCustomer WHERE ADDRESS = '' AND TOWN = '' AND POSTCODE = '' ORDER BY NAME;"
    Glenn
    ____________________________________

    My Blog
    Tower of Hanoi Android app (FREE!)
    Tower of Hanoi Leaderboard
    Samegame Facebook App
    vBulletin Plugins
    ____________________________________

  • #7
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    There is a difference between an empty cell and a cell with a zero length. Your query looks for zero length values. For empty cells you need
    strSQL = "SELECT ID, NAME FROM tblCustomer WHERE (ADDRESS Is Null) AND (TOWN Is Null) AND (POSTCODE Is Null) ORDER BY NAME"

    Is you use '' then make sure there is no space between the quotes.
    <edit> posts crossed. The '' are two single quotes --> like glennv partially highlighted</edit>

  • #8
    New to the CF scene
    Join Date
    Aug 2006
    Location
    Leicester, UK
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hello,

    i'm a bit of a newbie with SQL and currently using an access database which logs medical research studies. i've managed the basics of writing statements - would i be able to use a similar statement (to the one above) to search for missing start/end dates within specified tables? and could i incorporate a parameter to invite the user to specify either a start date or end date?

    thanx!

  • #9
    Senior Coder
    Join Date
    Nov 2002
    Location
    North-East, UK
    Posts
    1,265
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You would be best off starting your own thread rather than hijacking this one.


  •  

    Posting Permissions

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