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/Sear...iderSearch.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" <(E-Mail Removed)> wrote in message
news:F15061BA-C4E6-4DD1-8B9E-(E-Mail Removed)...
> 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/Sear...iderSearch.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" <(E-Mail Removed)> wrote in message
news:F15061BA-C4E6-4DD1-8B9E-(E-Mail Removed)...
> 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/Sear...iderSearch.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.
>

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.

"Jon Spivey" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:F15061BA-C4E6-4DD1-8B9E-(E-Mail Removed)...
> > 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/Sear...iderSearch.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.
> >
>
>
>

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!

"Jon Spivey" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:F15061BA-C4E6-4DD1-8B9E-(E-Mail Removed)...
> > 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/Sear...iderSearch.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.
> >
>
>
>