Searching a database

[Guest]

I have added an Access database to my website, and it seems that users are
able to add their information without a problem. However, when I try to make
it searchable, it never finds anything. Basically, the database is for
service providers - they input their information and the zip codes and cities
where they work. Then, a consumer can come to our site and find a service
provider either by entering their zip code or city name. It just keeps
giving me a "no records found" error message when I test it using records
that I know are there. I'm sure I'm missing something! Help would be
appreciated!

 

[Andrew Murray]

For a basic search I assume searching on postcode would be the best way so
your query would be (to the effect of)

Select * from Table [whatever] where ZIP = ::Zip::

and it should return all service providers in that area.

What does your query actually say? Can we see the search page?

I think the "no records found" is cause by the script wanting to match
*both* fields where you want it to return results for either searchin on
"City" or on "Zip Code", correct?

Maybe you can overcome this by making two search forms? One for "city" and
one for "zip code". Create two results pages - one for the Cities (e.g.
call it locations.asp) and one for the Zip. (call it zipcodes.asp). Run the
results wizard on each page, making the appropriate query for cities & zip
codes.

Then when you do the results wizard, check the option"put search form on
page".

You can copy then copy the code for the search forms to any other page
(copy/paste) as long as the form 'action' points to the results page
correctly.

Users will see the forms , enter the search in either field, click "search"
and are taken to the appropriate results page.

 

[Guest]

Thanks Andrew - I have a live "test" site that I am trying to get functional
before I move it to our main site. The test site is
http://cleanrx.bluemonkey.com/results.asp - this is the page where I have the
database tied in. The actual database is on the webserver - called
order_form.mdb. The submission form works fine, I can build the database
using user submissions.

My query string is: Select * from Results where ZipAreas =
::QueryZipAreas:: or where Cities = ::QueryCities::

I have tried it with and without quotation marks, no difference in the
results. Do you think it matters that each user who submits data can put up
to 20 zip codes with corresponding city names in the fields? The field
ZipAreas is a listing of 5-digit zip codes that are serviced by the provider
(separated by commas), the field Cities is a listing by names of cities
(separated by commas).

Thanks again for any help!
Sheri

For a basic search I assume searching on postcode would be the best way so
your query would be (to the effect of)

Select * from Table [whatever] where ZIP = ::Zip::

and it should return all service providers in that area.

What does your query actually say? Can we see the search page?

I think the "no records found" is cause by the script wanting to match
*both* fields where you want it to return results for either searchin on
"City" or on "Zip Code", correct?

Maybe you can overcome this by making two search forms? One for "city" and
one for "zip code". Create two results pages - one for the Cities (e.g.
call it locations.asp) and one for the Zip. (call it zipcodes.asp). Run the
results wizard on each page, making the appropriate query for cities & zip
codes.

Then when you do the results wizard, check the option"put search form on
page".

You can copy then copy the code for the search forms to any other page
(copy/paste) as long as the form 'action' points to the results page
correctly.

Users will see the forms , enter the search in either field, click "search"
and are taken to the appropriate results page.

I have added an Access database to my website, and it seems that users are
able to add their information without a problem. However, when I try to
make
it searchable, it never finds anything. Basically, the database is for
service providers - they input their information and the zip codes and
cities
where they work. Then, a consumer can come to our site and find a service
provider either by entering their zip code or city name. It just keeps
giving me a "no records found" error message when I test it using records
that I know are there. I'm sure I'm missing something! Help would be
appreciated!

 

[Clark]

Thanks Andrew - I have a live "test" site that I am trying to get functional
before I move it to our main site. The test site is
http://cleanrx.bluemonkey.com/results.asp - this is the page where I have the
database tied in. The actual database is on the webserver - called
order_form.mdb. The submission form works fine, I can build the database
using user submissions.

My query string is: Select * from Results where ZipAreas =
::QueryZipAreas:: or where Cities = ::QueryCities::

Since you are trying to select just part of a string of zip codes
separated by commas, You might try something such as:

Where ZipAreas Like '%::QueryZipZreas::%'

The % symbols are wild cards. If you dont use them and a user is
searching for zip code 12345, you wont get a match from a record that
has, for example,3 zipcodes separated by commas such as

23394, 12345, 55748

 

[Andrew Murray]

I tried to add a record, and that works OK, but I can't retrieve the results
from your search form. Hope it was OK, since this is the 'test' site?

I figured the data entry form was order_form.asp since the db is
order_form.mdb and FP creates the db based on the asp file name if you
haven't created the db separately in Access beforehand.

Thanks Andrew - I have a live "test" site that I am trying to get
functional
before I move it to our main site. The test site is
http://cleanrx.bluemonkey.com/results.asp - this is the page where I have
the
database tied in. The actual database is on the webserver - called
order_form.mdb. The submission form works fine, I can build the database
using user submissions.

My query string is: Select * from Results where ZipAreas =
::QueryZipAreas:: or where Cities = ::QueryCities::

I have tried it with and without quotation marks, no difference in the
results. Do you think it matters that each user who submits data can put
up
to 20 zip codes with corresponding city names in the fields? The field
ZipAreas is a listing of 5-digit zip codes that are serviced by the
provider
(separated by commas), the field Cities is a listing by names of cities
(separated by commas).

Thanks again for any help!
Sheri

For a basic search I assume searching on postcode would be the best way
so
your query would be (to the effect of)

Select * from Table [whatever] where ZIP = ::Zip::

and it should return all service providers in that area.

What does your query actually say? Can we see the search page?

I think the "no records found" is cause by the script wanting to match
*both* fields where you want it to return results for either searchin on
"City" or on "Zip Code", correct?

Maybe you can overcome this by making two search forms? One for "city"
and
one for "zip code". Create two results pages - one for the Cities (e.g.
call it locations.asp) and one for the Zip. (call it zipcodes.asp). Run
the
results wizard on each page, making the appropriate query for cities &
zip
codes.

Then when you do the results wizard, check the option"put search form on
page".

You can copy then copy the code for the search forms to any other page
(copy/paste) as long as the form 'action' points to the results page
correctly.

Users will see the forms , enter the search in either field, click
"search"
and are taken to the appropriate results page.

I have added an Access database to my website, and it seems that users
are
able to add their information without a problem. However, when I try
to
make
it searchable, it never finds anything. Basically, the database is for
service providers - they input their information and the zip codes and
cities
where they work. Then, a consumer can come to our site and find a
service
provider either by entering their zip code or city name. It just keeps
giving me a "no records found" error message when I test it using
records
that I know are there. I'm sure I'm missing something! Help would be
appreciated!

 

[Guest]

Clark - THANKS! I tried that, but now it is finding ALL the data, whether or
not it matches what I'm looking for. I haven't entered all of it in, so
there isn't much there yet, but it's finding everything that has anything in
the field I'm checking.

The good news is that it IS finding the database, which I was not certain
was happening - so now I know it can find it. Somehow my query strings is
not quite right I guess. I copied yours exactly, but now I find too much
data - that doesn't apply - It should only show the service providers for the
city name or zip searched for.

Any more thoughts would be appreciated! This forum has already gotten me a
lot further than any other internet searches! Thanks!
Sheri

 

[Guest]

Andrew - Thanks, yes that is exactly my problem. I can get stuff in, but not
out! LOL Clark had a good idea about using a wild card, but if you read my
post to his suggestion, now it finds everybody. Sigh.
Sheri

I tried to add a record, and that works OK, but I can't retrieve the results
from your search form. Hope it was OK, since this is the 'test' site?

I figured the data entry form was order_form.asp since the db is
order_form.mdb and FP creates the db based on the asp file name if you
haven't created the db separately in Access beforehand.

Thanks Andrew - I have a live "test" site that I am trying to get
functional
before I move it to our main site. The test site is
http://cleanrx.bluemonkey.com/results.asp - this is the page where I have
the
database tied in. The actual database is on the webserver - called
order_form.mdb. The submission form works fine, I can build the database
using user submissions.

My query string is: Select * from Results where ZipAreas =
::QueryZipAreas:: or where Cities = ::QueryCities::

I have tried it with and without quotation marks, no difference in the
results. Do you think it matters that each user who submits data can put
up
to 20 zip codes with corresponding city names in the fields? The field
ZipAreas is a listing of 5-digit zip codes that are serviced by the
provider
(separated by commas), the field Cities is a listing by names of cities
(separated by commas).

Thanks again for any help!
Sheri

For a basic search I assume searching on postcode would be the best way
so
your query would be (to the effect of)

Select * from Table [whatever] where ZIP = ::Zip::

and it should return all service providers in that area.

What does your query actually say? Can we see the search page?

I think the "no records found" is cause by the script wanting to match
*both* fields where you want it to return results for either searchin on
"City" or on "Zip Code", correct?

Maybe you can overcome this by making two search forms? One for "city"
and
one for "zip code". Create two results pages - one for the Cities (e.g.
call it locations.asp) and one for the Zip. (call it zipcodes.asp). Run
the
results wizard on each page, making the appropriate query for cities &
zip
codes.

Then when you do the results wizard, check the option"put search form on
page".

You can copy then copy the code for the search forms to any other page
(copy/paste) as long as the form 'action' points to the results page
correctly.

Users will see the forms , enter the search in either field, click
"search"
and are taken to the appropriate results page.




I have added an Access database to my website, and it seems that users
are
able to add their information without a problem. However, when I try
to
make
it searchable, it never finds anything. Basically, the database is for
service providers - they input their information and the zip codes and
cities
where they work. Then, a consumer can come to our site and find a
service
provider either by entering their zip code or city name. It just keeps
giving me a "no records found" error message when I test it using
records
that I know are there. I'm sure I'm missing something! Help would be
appreciated!

 

[Clark]

Hello Sheri, sorry for this late reply -- am traveling and not
checking in as often.

It sounds to me like there is something else in your query string that
is causing all the records to show. The WHERE clause I showed you
should limit the records to those having just the requested zipcode.
Are there some other "OR" phrases that could be adding to your
results?

Do you have Access on your computer? The easiest way I have found for
making queries is to use the query builder in Access. Once you get the
query right there, you are most of the way home (not all the way home
sometimes -- occasionally you have to slightly modify the query for
use with FP, as in using %'s for wildcards)