Searching an Access 2003 Database Using Data Access Pages

#1 (**permalink**) 28-Jul-2006, 08:27 AM

Hey all, I'm working on a database for my company which holds project
information in one table, this is the only table I need to search, and
I want to basically run a query on it using the text boxes on my data
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12271-searching-access-2003-database-using-data.html
access page. That is - I want the user to type into the various
feilds, [all optional]. Anything that they do not enter will count as
wildcard, and everything they do will be used to search for a match in
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12271
that feild. I thought this would be a quite simple process,
considering a query of this type to me seemed quite common, however I
seem to be stumped. The closest I have found is something for adding a
find button on the microsoft help pages, however by its explanation it
only allows the user to search one feild - the primary key. Any
guidance would be apreciated,

-Zach

#2 (**permalink**) 28-Jul-2006, 08:27 AM

For criteria use this if they are to enter exact values --
Like IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
[Forms]![YourForm]![YourTextBox])

For criteria use this if they are to enter start of the value --
Like IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
[Forms]![YourForm]![YourTextBox]) & "*"

For criteria use this if they are to enter any part of the value --
Like "*" & IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
[Forms]![YourForm]![YourTextBox]) & "*"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12271

"Spent" wrote:

> Hey all, I'm working on a database for my company which holds project
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12271
> information in one table, this is the only table I need to search, and
> I want to basically run a query on it using the text boxes on my data
> access page. That is - I want the user to type into the various
> feilds, [all optional]. Anything that they do not enter will count as
> wildcard, and everything they do will be used to search for a match in
> that feild. I thought this would be a quite simple process,
> considering a query of this type to me seemed quite common, however I
> seem to be stumped. The closest I have found is something for adding a
> find button on the microsoft help pages, however by its explanation it
> only allows the user to search one feild - the primary key. Any
> guidance would be apreciated,
>
> -Zach
>
>

#3 (**permalink**) 28-Jul-2006, 08:28 AM

This seems to be what I need, specifically the first one, however I
don't know where to put this code, is this a vbscript? Sorry to ask
for such basic instructions, this is my first access project ever and I
am kind of lost. I am used to working with Iseries and stuff and
working directly with SQL is much different from this program.

KARL DEWEY wrote:
> For criteria use this if they are to enter exact values --
> Like IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
> [Forms]![YourForm]![YourTextBox])
>
> For criteria use this if they are to enter start of the value --
> Like IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
> [Forms]![YourForm]![YourTextBox]) & "*"
>
> For criteria use this if they are to enter any part of the value --
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12271
> Like "*" & IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
> [Forms]![YourForm]![YourTextBox]) & "*"
>
>
> "Spent" wrote:
>
> > Hey all, I'm working on a database for my company which holds project
> > information in one table, this is the only table I need to search, and
> > I want to basically run a query on it using the text boxes on my data
> > access page. That is - I want the user to type into the various
> > feilds, [all optional]. Anything that they do not enter will count as
> > wildcard, and everything they do will be used to search for a match in
> > that feild. I thought this would be a quite simple process,
> > considering a query of this type to me seemed quite common, however I
> > seem to be stumped. The closest I have found is something for adding a
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12271
> > find button on the microsoft help pages, however by its explanation it
> > only allows the user to search one feild - the primary key. Any
> > guidance would be apreciated,
> >
> > -Zach
> >
> >

#4 (**permalink**) 28-Jul-2006, 08:28 AM

What Karl's given you would be typed into the Criteria cell of the query
underneath the specific table field in question.

Note that there was word-wrap in the post. All 3 of Karl's suggestions are
each 1 line long.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Spent" wrote in message
news:1151588969.659822.150830@j72g2000cwa.googlegr oups.com...
> This seems to be what I need, specifically the first one, however I
> don't know where to put this code, is this a vbscript? Sorry to ask
> for such basic instructions, this is my first access project ever and I
> am kind of lost. I am used to working with Iseries and stuff and
> working directly with SQL is much different from this program.
>
> KARL DEWEY wrote:
> > For criteria use this if they are to enter exact values --
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12271
> > Like IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
> > [Forms]![YourForm]![YourTextBox])
> >
> > For criteria use this if they are to enter start of the value --
> > Like IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
> > [Forms]![YourForm]![YourTextBox]) & "*"
> >
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12271
> > For criteria use this if they are to enter any part of the value --
> > Like "*" & IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
> > [Forms]![YourForm]![YourTextBox]) & "*"
> >
> >
> > "Spent" wrote:
> >
> > > Hey all, I'm working on a database for my company which holds project
> > > information in one table, this is the only table I need to search, and
> > > I want to basically run a query on it using the text boxes on my data
> > > access page. That is - I want the user to type into the various
> > > feilds, [all optional]. Anything that they do not enter will count as
> > > wildcard, and everything they do will be used to search for a match in
> > > that feild. I thought this would be a quite simple process,
> > > considering a query of this type to me seemed quite common, however I
> > > seem to be stumped. The closest I have found is something for adding
a
> > > find button on the microsoft help pages, however by its explanation it
> > > only allows the user to search one feild - the primary key. Any
> > > guidance would be apreciated,
> > >
> > > -Zach
> > >
> > >
>

#5 (**permalink**) 28-Jul-2006, 08:28 AM

Thank you, thank you, thank you! Will this information work with a
data access page instead of a form too? As in,

Like IIF([Forms]![Input]![proj_morguard_num] Is Null, "*",
[Forms]![Input]![proj_morguard_num])

Worst case scenario I can revert to forms but I think pages would be
easier. It is my understanding that pages are just web based forms, in
which case I think this should work. Also, is "YourTextBox" value the
ID of my textbox? Because right now the ID of all my text boxes is the
same as the feild it relates to! I don't want to cause a problem due
to this. Thank you all for your patientience and help in the matter,
you've made my learning experience much less grueling.

-Zach

Douglas J Steele wrote:
> What Karl's given you would be typed into the Criteria cell of the query
> underneath the specific table field in question.
>
> Note that there was word-wrap in the post. All 3 of Karl's suggestions are
> each 1 line long.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Spent" wrote in message
> news:1151588969.659822.150830@j72g2000cwa.googlegr oups.com...
> > This seems to be what I need, specifically the first one, however I
> > don't know where to put this code, is this a vbscript? Sorry to ask
> > for such basic instructions, this is my first access project ever and I
> > am kind of lost. I am used to working with Iseries and stuff and
> > working directly with SQL is much different from this program.
> >
> > KARL DEWEY wrote:
> > > For criteria use this if they are to enter exact values --
> > > Like IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
> > > [Forms]![YourForm]![YourTextBox])
> > >
> > > For criteria use this if they are to enter start of the value --
> > > Like IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
> > > [Forms]![YourForm]![YourTextBox]) & "*"
> > >
> > > For criteria use this if they are to enter any part of the value --
> > > Like "*" & IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
> > > [Forms]![YourForm]![YourTextBox]) & "*"
> > >
> > >
> > > "Spent" wrote:
> > >
> > > > Hey all, I'm working on a database for my company which holds project
> > > > information in one table, this is the only table I need to search, and
> > > > I want to basically run a query on it using the text boxes on my data
> > > > access page. That is - I want the user to type into the various
> > > > feilds, [all optional]. Anything that they do not enter will count as
> > > > wildcard, and everything they do will be used to search for a match in
> > > > that feild. I thought this would be a quite simple process,
> > > > considering a query of this type to me seemed quite common, however I
> > > > seem to be stumped. The closest I have found is something for adding
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12271
> a
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12271
> > > > find button on the microsoft help pages, however by its explanation it
> > > > only allows the user to search one feild - the primary key. Any
> > > > guidance would be apreciated,
> > > >
> > > > -Zach
> > > >
> > > >
> >

#6 (**permalink**) 28-Jul-2006, 08:28 AM

Thank you, thank you, thank you! Will this information work with a
data access page instead of a form too? As in,

Like IIF([Forms]![Input]![proj_morguard_num] Is Null, "*",
[Forms]![Input]![proj_morguard_num])
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12271

Worst case scenario I can revert to forms but I think pages would be
easier. It is my understanding that pages are just web based forms, in
which case I think this should work. Also, is "YourTextBox" value the
ID of my textbox? Because right now the ID of all my text boxes is the
same as the feild it relates to! I don't want to cause a problem due
to this. Thank you all for your patientience and help in the matter,
you've made my learning experience much less grueling.

-Zach

Douglas J Steele wrote:
> What Karl's given you would be typed into the Criteria cell of the query
> underneath the specific table field in question.
>
> Note that there was word-wrap in the post. All 3 of Karl's suggestions are
> each 1 line long.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Spent" wrote in message
> news:1151588969.659822.150830@j72g2000cwa.googlegr oups.com...
> > This seems to be what I need, specifically the first one, however I
> > don't know where to put this code, is this a vbscript? Sorry to ask
> > for such basic instructions, this is my first access project ever and I
> > am kind of lost. I am used to working with Iseries and stuff and
> > working directly with SQL is much different from this program.
> >
> > KARL DEWEY wrote:
> > > For criteria use this if they are to enter exact values --
> > > Like IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
> > > [Forms]![YourForm]![YourTextBox])
> > >
> > > For criteria use this if they are to enter start of the value --
> > > Like IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
> > > [Forms]![YourForm]![YourTextBox]) & "*"
> > >
> > > For criteria use this if they are to enter any part of the value --
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12271
> > > Like "*" & IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
> > > [Forms]![YourForm]![YourTextBox]) & "*"
> > >
> > >
> > > "Spent" wrote:
> > >
> > > > Hey all, I'm working on a database for my company which holds project
> > > > information in one table, this is the only table I need to search, and
> > > > I want to basically run a query on it using the text boxes on my data
> > > > access page. That is - I want the user to type into the various
> > > > feilds, [all optional]. Anything that they do not enter will count as
> > > > wildcard, and everything they do will be used to search for a match in
> > > > that feild. I thought this would be a quite simple process,
> > > > considering a query of this type to me seemed quite common, however I
> > > > seem to be stumped. The closest I have found is something for adding
> a
> > > > find button on the microsoft help pages, however by its explanation it
> > > > only allows the user to search one feild - the primary key. Any
> > > > guidance would be apreciated,
> > > >
> > > > -Zach
> > > >
> > > >
> >

#7 (**permalink**) 28-Jul-2006, 08:28 AM

To be honest, I haven't worked with data access pages. If they use ADO, you
might have to replace the * with % in that statement.

Not sure what you mean by "the ID of my textbox". Do you mean the name of
the text box is the same as the name of the field to which it's bound? That
shouldn't be an issue.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Spent" wrote in message
news:1151590836.227886.120390@i40g2000cwc.googlegr oups.com...
> Thank you, thank you, thank you! Will this information work with a
> data access page instead of a form too? As in,
>
> Like IIF([Forms]![Input]![proj_morguard_num] Is Null, "*",
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12271
> [Forms]![Input]![proj_morguard_num])
>
> Worst case scenario I can revert to forms but I think pages would be
> easier. It is my understanding that pages are just web based forms, in
> which case I think this should work. Also, is "YourTextBox" value the
> ID of my textbox? Because right now the ID of all my text boxes is the
> same as the feild it relates to! I don't want to cause a problem due
> to this. Thank you all for your patientience and help in the matter,
> you've made my learning experience much less grueling.
>
> -Zach
>
> Douglas J Steele wrote:
> > What Karl's given you would be typed into the Criteria cell of the query
> > underneath the specific table field in question.
> >
> > Note that there was word-wrap in the post. All 3 of Karl's suggestions
are
> > each 1 line long.
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "Spent" wrote in message
> > news:1151588969.659822.150830@j72g2000cwa.googlegr oups.com...
> > > This seems to be what I need, specifically the first one, however I
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12271
> > > don't know where to put this code, is this a vbscript? Sorry to ask
> > > for such basic instructions, this is my first access project ever and
I
> > > am kind of lost. I am used to working with Iseries and stuff and
> > > working directly with SQL is much different from this program.
> > >
> > > KARL DEWEY wrote:
> > > > For criteria use this if they are to enter exact values --
> > > > Like IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
> > > > [Forms]![YourForm]![YourTextBox])
> > > >
> > > > For criteria use this if they are to enter start of the value --
> > > > Like IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
> > > > [Forms]![YourForm]![YourTextBox]) & "*"
> > > >
> > > > For criteria use this if they are to enter any part of the value --
> > > > Like "*" & IIF([Forms]![YourForm]![YourTextBox] Is Null, "*",
> > > > [Forms]![YourForm]![YourTextBox]) & "*"
> > > >
> > > >
> > > > "Spent" wrote:
> > > >
> > > > > Hey all, I'm working on a database for my company which holds
project
> > > > > information in one table, this is the only table I need to search,
and
> > > > > I want to basically run a query on it using the text boxes on my
data
> > > > > access page. That is - I want the user to type into the various
> > > > > feilds, [all optional]. Anything that they do not enter will
count as
> > > > > wildcard, and everything they do will be used to search for a
match in
> > > > > that feild. I thought this would be a quite simple process,
> > > > > considering a query of this type to me seemed quite common,
however I
> > > > > seem to be stumped. The closest I have found is something for
adding
> > a
> > > > > find button on the microsoft help pages, however by its
explanation it
> > > > > only allows the user to search one feild - the primary key. Any
> > > > > guidance would be apreciated,
> > > > >
> > > > > -Zach
> > > > >
> > > > >
> > >
>