Custom Query to Suppliment the DRW for a Search Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to build a few different search forms that query databases uploaded to my website. The data originates from an ODBC data source so I can export to many different formats. I'm currently using an access database connection. I'm working with FP03 and my Access database is 2000.
I want to query several different form fields and my needs are too complex for the DRW and for my level of programming. What I want is a statement that says (Return results for any field that has data in it but ignore those which are blank) Can anyone help? Please please please!
Here is a link to the page I started. The results are returning all records because the blank fields are being processed as data in the query.
http://new.caassistedliving.org/SearchSamples/ProviderSearch.asp
Thanks for your time.


PS Jim if you happen across this message - Front Page Inside and Out is an incredible book!! It is my bible. I appreciate the useful info and sprinkles of humor that you provide.
 
Hi,
Can you define "blank" Usually blank means null in which case
SELECT *
FROM Table
WHERE Field IS NOT NULL

would give you the results you want
Melissa said:
I'm trying to build a few different search forms that query databases
Click to expand...
uploaded to my website. The data originates from an ODBC data source so I
can export to many different formats. I'm currently using an access database
connection. I'm working with FP03 and my Access database is 2000.
I want to query several different form fields and my needs are too complex
Click to expand...
for the DRW and for my level of programming. What I want is a statement that
says (Return results for any field that has data in it but ignore those
which are blank) Can anyone help? Please please please!
Here is a link to the page I started. The results are returning all
Click to expand...
records because the blank fields are being processed as data in the query.
http://new.caassistedliving.org/SearchSamples/ProviderSearch.asp
Thanks for your time.


PS Jim if you happen across this message - Front Page Inside and Out is an
Click to expand...
incredible book!! It is my bible. I appreciate the useful info and sprinkles
of humor that you provide.
 
Hi,
Can you define "blank" Usually blank means null in which case
SELECT *
FROM Table
WHERE Field IS NOT NULL

would give you the results you want
Melissa said:
I'm trying to build a few different search forms that query databases
Click to expand...
uploaded to my website. The data originates from an ODBC data source so I
can export to many different formats. I'm currently using an access database
connection. I'm working with FP03 and my Access database is 2000.
I want to query several different form fields and my needs are too complex
Click to expand...
for the DRW and for my level of programming. What I want is a statement that
says (Return results for any field that has data in it but ignore those
which are blank) Can anyone help? Please please please!
Here is a link to the page I started. The results are returning all
Click to expand...
records because the blank fields are being processed as data in the query.
http://new.caassistedliving.org/SearchSamples/ProviderSearch.asp
Thanks for your time.


PS Jim if you happen across this message - Front Page Inside and Out is an
Click to expand...
incredible book!! It is my bible. I appreciate the useful info and sprinkles
of humor that you provide.
 
Thanks so very much Jon! I'm almost there!

I have two issues:

Can you please look at this and tell me if I have it right?

fp_sQry="SELECT * FROM ""PR MemberQuery - Imported"" WHERE (COMPANY LIKE '%::COMPANY::%' AND 'COMPANY' IS NOT NULL AND LAST_NAME LIKE '%::LAST_NAME::%' AND 'LAST NAME' IS NOT NULL AND CITY LIKE '%::CITY::%' AND 'CITY' IS NOT NULL AND ZIP LIKE '%::ZIP::%' AND 'ZIP' IS NOT NULL)"

I thought I should use "and" since I already have a "where" clause. Is this correct?

My second issue is that I generated the query with DRW, so when I edit the statement and attempt to save I get a message that a front page component was edited and cannot be saved. How do I stop this from happening?

Thanks a million. :)
 
Nevermind! I posted my last message to quickly. I added a custom query to the DRW and it works beautifully.

Thanks for your help, Jon!
 
Back
Top