...

View Full Version : Searching for Blank fields



holty
10-14-2003, 09:07 AM
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 :rolleyes:

jeskel
10-14-2003, 09:20 AM
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:





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

holty
10-14-2003, 09:43 AM
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

jeskel
10-14-2003, 09:56 AM
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:




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":




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.




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 ;)

holty
10-14-2003, 10:06 AM
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 :)

glenngv
10-14-2003, 11:47 AM
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;"

raf
10-14-2003, 11:47 AM
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>

PiCRA
08-25-2006, 12:07 PM
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!

degsy
08-31-2006, 03:04 PM
You would be best off starting your own thread rather than hijacking this one.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum