Dear All,

I require a SQL code for a search page. I have a search form where a user 
can search by keyword, category or by date. The keyword search is a 
regular text field, the category search a drop/list menu and the date 
search are 3 seperate drop/list menus.

The Access database is set up with the following fields:

ArchiveID
ArchiveName
ArchiveLink
ArchveDate
Category

I have the first 2 searches (Keyword & Category) sorted by using the 
following code, but I am having difficulty with incorporating the date 
search into this code:

SELECT DISTINCT ArchiveName, ArchiveLink, Category, ArchiveDate
FROM Archive
WHERE ArchiveName LIKE '%strKeyword%' AND Category LIKE 'strCategory'

Incidentally, I am using Macromedia UltraDev to create the search form if 
this makes any difference.

HELP!!

Regards,

Sati.

Sati
What do you mean by "the date search are 3 seperate drop/list menus."
And what Database are you using, because you state that it is Access, but 
the Select statement is T-SQL from SQL Server (or are you using a Access 
Pass-Through Query)?

What is you best attempt at the date search?

Regards

Greg Frazer


> Dear All,

> I require a SQL code for a search page. I have a search form where a 
user 
c> an search by keyword, category or by date. The keyword search is a 
r> egular text field, the category search a drop/list menu and the date 
s> earch are 3 seperate drop/list menus.

> The Access database is set up with the following fields:

> ArchiveID
A> rchiveName
A> rchiveLink
A> rchveDate
C> ategory

> I have the first 2 searches (Keyword & Category) sorted by using the 
f> ollowing code, but I am having difficulty with incorporating the date 
s> earch into this code:

> SELECT DISTINCT ArchiveName, ArchiveLink, Category, ArchiveDate
F> ROM Archive
W> HERE ArchiveName LIKE '%strKeyword%' AND Category LIKE 'strCategory'

> Incidentally, I am using Macromedia UltraDev to create the search form 
if 
t> his makes any difference.

> HELP!!

> Regards,

> Sati.

Greg,

I am using a Microsoft Access database but a SQL statement in order to 
retrieve the search results and display them on my web page. The SQL 
statement is part of a Macromedia UltraDev recordset, if that makes it any 
clearer. 

I have created 3 menus - one for day, one for month and one for year so 
when you click on the menu for:

day it lists all the dates (e.g 1,2,3) of the months
month it list all the months of the year
year it lists for now only 2002.

I am trying in essence to replicate the search facility that The Guardian 
has used for their own Archive search.  Check it out at:

http://www.guardian.co.uk/Archive/0,4271,,00.html

Does this make it any clearer??? My search is a simpler version of theirs 
in that I only want to search by keyword, category and date. 

I attempted to write my own SQL statement for the date search but came 
stumped when I did not know how to organise the date. I have one field in 
the database and 3 separate fields on the form for the date of the 
Archive. I attempted to separate the ArchiveDate filed into 3 fields in 
the database so it was the same as the search form so one field for day 
(ArchiveDay), one for month (ArchiveMonth), and one for year 
(ArxhiveYear). My attempt was:

SELECT DISTINCT ArchiveName, ArchiveLink, Category, ArchiveDay, 
ArchiveMonth, ArchiveYear
FROM Archive
WHERE ArchiveName LIKE '%strKeyword%' AND Category LIKE 'strCategory'AND 
ArchiveDay LIKE 'dd' AND ArchiveMonth LIKE 'mm' AND ArchiveYear LIKE 'yy'

When I amended my code to this I was able to get the date search working, 
but the keyword and category searches no longer worked. I want them all to 
work together!!!!

HELP!

>>Sati,
W> hat do you mean by "the date search are 3 seperate drop/list menus."
A> nd what Database are you using, because you state that it is Access, 
but 
t> he Select statement is T-SQL from SQL Server (or are you using a Access 
P> ass-Through Query)?

> What is you best attempt at the date search?

> Regards

> Greg Frazer

> 
>>  Dear All,

> > I require a SQL code for a search page. I have a search form where a 
u> ser 
c> > an search by keyword, category or by date. The keyword search is a 
r> > egular text field, the category search a drop/list menu and the date 
s> > earch are 3 seperate drop/list menus.

> > The Access database is set up with the following fields:

> > ArchiveID
A> > rchiveName
A> > rchiveLink
A> > rchveDate
C> > ategory

> > I have the first 2 searches (Keyword & Category) sorted by using the 
f> > ollowing code, but I am having difficulty with incorporating the date 
s> > earch into this code:

> > SELECT DISTINCT ArchiveName, ArchiveLink, Category, ArchiveDate
F> > ROM Archive
W> > HERE ArchiveName LIKE '%strKeyword%' AND Category LIKE 'strCategory'

> > Incidentally, I am using Macromedia UltraDev to create the search form 
i> f 
t> > his makes any difference.

> > HELP!!

> > Regards,

> > Sati.