search function on forms

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

Guest

Hi,
Ive been able to create forms that has a menu which looks up values from one
field and displays the list of possible/closest matching entries.

However, i cant seem to create one which uses multiple attributes:
basically where i would have 4 different fields :
- Day
- Time
- Type
- Year

and a user could input any of the values to search like on amazon book
search facility, you can search by many different values using multple
criteria and find the best possible match.?

Is that possible on Access 2000? any helpful tips would be appreciated
thanks
 
I'd normally go for building an SQL string on the fly... something like...

sql="select whatever from wherever where "
if not isnull(me.day) then
'add day criterion
sql=sql+"day="+me.day+" and "
endif
if not isnull(me.time) then
'add time criterion
endif
if not isnull(me.type) then
'add year criterion
endif
if not isnull(me.year) then
'add year criterion
endif
'some string manipulation to chop off a closing 'and' of to
'catch the situation where no options were
'chosen and the sql string ends 'where'

'use sql as the source of whatever you're using to display the matching
records
 
Just a couple of (somewhat picky) comments.

You should use & rather than + to concatenate text.

You shouldn't use names such as Day, Time, Type or Year for your own
purposes: they're reserved words, and using them inappropriately can lead to
problems. If you must use them, be sure to enclose them in square brackets
(like [day] = ) in your SQL strings.
 
Nothing wrong with the occasional piece of justifiable pickiness. <g>


Douglas J. Steele said:
Just a couple of (somewhat picky) comments.

You should use & rather than + to concatenate text.

You shouldn't use names such as Day, Time, Type or Year for your own
purposes: they're reserved words, and using them inappropriately can lead to
problems. If you must use them, be sure to enclose them in square brackets
(like [day] = ) in your SQL strings.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Rob Oldfield said:
I'd normally go for building an SQL string on the fly... something like...

sql="select whatever from wherever where "
if not isnull(me.day) then
'add day criterion
sql=sql+"day="+me.day+" and "
endif
if not isnull(me.time) then
'add time criterion
endif
if not isnull(me.type) then
'add year criterion
endif
if not isnull(me.year) then
'add year criterion
endif
'some string manipulation to chop off a closing 'and' of to
'catch the situation where no options were
'chosen and the sql string ends 'where'

'use sql as the source of whatever you're using to display the matching
records

from
one
Click to expand...
Click to expand...
 
Back
Top