Searching an Access 2003 Database Using Data Access Pages

[Spent]

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

 

[Guest]

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]) & "*"

 

[dbahooker]

I always apply a filter, or serverfilter is it?? on the datasource
control

-Aaron

 

[Spent]

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.

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]) & "*"

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

 

[Douglas J Steele]

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

(no e-mails, please!)

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.

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]) & "*"

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

 

[Spent]

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

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

(no e-mails, please!)

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.

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]) & "*"


:

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

 

[Spent]

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

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

(no e-mails, please!)

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.

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]) & "*"


:

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

 

[Douglas J Steele]

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

(no e-mails, please!)

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

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

(no e-mails, please!)

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]) & "*"


:

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