parameter query passed from a form with wildcards

[MVM]

Hi everyone,
I am working on an Access project (ADP). I have a switchboard form setup to
allow the user to open up another form by entering search criteria in a text
box and clicking a command button. Then the form opens using input
parameters and grabbing the data in the switchboard text box. My question
is this, how do I grab the data from the switchboard form and put a wildcard
on both ends of the data when passed to the input parameters?

On the form that opens I have the recordset set to:
SELECT FixedAssets.*, Departments.Department FROM FixedAssets INNER JOIN
Departments ON FixedAssets.Dept = Departments.Dept_Num WHERE
FixedAssets.Description = ?

On the same form I have the Input Parameters set to:
FixedAssets.Description nvarchar LIKE "*" &
Forms![Switchboard]![txtDescription] & "*"

With this code it prompts me to input data when the form opens instead of
grabbing it from the Switchboard form. If I take off the wildcards I need
to have the exact description before it will find a record.

Thanks!
MVM

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The form's recordsource, you want to "grab" the data in the
"switchboard" form's TextBox, should look like this:

SELECT FixedAssets.*, Departments.Department
FROM FixedAssets INNER JOIN Departments ON FixedAssets.Dept =
Departments.Dept_Num
WHERE FixedAssets.Description LIKE "*" &
Forms![Switchboard]![txtDescription] & "*"

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5Sn2YechKqOuFEgEQJ6TACfVB+SSjxouayzgxSZkxtlb5 xs+twAn0Yv
XBCJUkDJclvAFINg3o6m8DRy
=djus
-----END PGP SIGNATURE-----

MVM wrote:

Hi everyone,
I am working on an Access project (ADP). I have a switchboard form setup to
allow the user to open up another form by entering search criteria in a text
box and clicking a command button. Then the form opens using input
parameters and grabbing the data in the switchboard text box. My question
is this, how do I grab the data from the switchboard form and put a wildcard
on both ends of the data when passed to the input parameters?

On the form that opens I have the recordset set to:
SELECT FixedAssets.*, Departments.Department FROM FixedAssets INNER JOIN
Departments ON FixedAssets.Dept = Departments.Dept_Num WHERE
FixedAssets.Description = ?

On the same form I have the Input Parameters set to:
FixedAssets.Description nvarchar LIKE "*" &
Forms![Switchboard]![txtDescription] & "*"

With this code it prompts me to input data when the form opens instead of
grabbing it from the Switchboard form. If I take off the wildcards I need
to have the exact description before it will find a record.

Thanks!
MVM

[MVM]

Thanks for the help. I tried this statement and I get an invalid SQL
statement error.
Any other ideas?
"MGFoster" <[e-mail]> wrote in message
news:sJ*****************@newsread4.news.pas.earthl ink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The form's recordsource, you want to "grab" the data in the
"switchboard" form's TextBox, should look like this:

SELECT FixedAssets.*, Departments.Department
FROM FixedAssets INNER JOIN Departments ON FixedAssets.Dept =
Departments.Dept_Num
WHERE FixedAssets.Description LIKE "*" &
Forms![Switchboard]![txtDescription] & "*"

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5Sn2YechKqOuFEgEQJ6TACfVB+SSjxouayzgxSZkxtlb5 xs+twAn0Yv
XBCJUkDJclvAFINg3o6m8DRy
=djus
-----END PGP SIGNATURE-----

MVM wrote:

Hi everyone,
I am working on an Access project (ADP). I have a switchboard form setup to allow the user to open up another form by entering search criteria in a text box and clicking a command button. Then the form opens using input
parameters and grabbing the data in the switchboard text box. My question is this, how do I grab the data from the switchboard form and put a wildcard on both ends of the data when passed to the input parameters?

On the form that opens I have the recordset set to:
SELECT FixedAssets.*, Departments.Department FROM FixedAssets INNER JOIN
Departments ON FixedAssets.Dept = Departments.Dept_Num WHERE
FixedAssets.Description = ?

On the same form I have the Input Parameters set to:
FixedAssets.Description nvarchar LIKE "*" &
Forms![Switchboard]![txtDescription] & "*"

With this code it prompts me to input data when the form opens instead of grabbing it from the Switchboard form. If I take off the wildcards I need to have the exact description before it will find a record.

Thanks!
MVM

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Doh... That's because I forgot you're working in an .adp project.
You'll have to set up the SQL statement in VBA & then put it into the
RecordSource. Something like the following in the form that's
opening:

Private Sub Form_Open(Cancel As Integer)

Dim strSQL As String

strSQL = "SELECT FixedAssets.*, Departments.Department " & _
"FROM FixedAssets INNER JOIN Departments " & _
"ON FixedAssets.Dept = Departments.Dept_Num " & _
"WHERE FixedAssets.Description LIKE '*" & _
Forms![Switchboard]![txtDescription] & "*'"

Me.RecordSource = strSQL

End Sub

Note the single-quotes in the WHERE clause. This should produce a
clause like this:

WHERE FixedAssets.Description Like '*television*'

if the Forms![Switchboard]![txtDescription] contains the word
"television."

The above assumes that you have the tables FixedAssets and Departments
linked to the project (look in the Tables tab of the database window).

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5WKT4echKqOuFEgEQKQmQCeJup/yTeqdaoKvEekZ6koqGKtNscAn2Yh
3da/Al51QkM3cJ05Fw2COijc
=3xYv
-----END PGP SIGNATURE-----

MVM wrote:

Thanks for the help. I tried this statement and I get an invalid SQL
statement error.
Any other ideas?
"MGFoster" <[e-mail]> wrote in message
news:sJ*****************@newsread4.news.pas.earthl ink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The form's recordsource, you want to "grab" the data in the
"switchboard" form's TextBox, should look like this:

SELECT FixedAssets.*, Departments.Department
FROM FixedAssets INNER JOIN Departments ON FixedAssets.Dept =
Departments.Dept_Num
WHERE FixedAssets.Description LIKE "*" &
Forms![Switchboard]![txtDescription] & "*"

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5Sn2YechKqOuFEgEQJ6TACfVB+SSjxouayzgxSZkxtlb5 xs+twAn0Yv
XBCJUkDJclvAFINg3o6m8DRy
=djus
-----END PGP SIGNATURE-----

MVM wrote:

Hi everyone,
I am working on an Access project (ADP). I have a switchboard form
setup to
allow the user to open up another form by entering search criteria in a
text
box and clicking a command button. Then the form opens using input
parameters and grabbing the data in the switchboard text box. My
question
is this, how do I grab the data from the switchboard form and put a
wildcard
on both ends of the data when passed to the input parameters?

On the form that opens I have the recordset set to:
SELECT FixedAssets.*, Departments.Department FROM FixedAssets INNER JOIN
Departments ON FixedAssets.Dept = Departments.Dept_Num WHERE
FixedAssets.Description = ?

On the same form I have the Input Parameters set to:
FixedAssets.Description nvarchar LIKE "*" &
Forms![Switchboard]![txtDescription] & "*"

With this code it prompts me to input data when the form opens instead
of
grabbing it from the Switchboard form. If I take off the wildcards I
need
to have the exact description before it will find a record.

Thanks!
MVM

[MVM]

Woohoo! That worked. Thanks!
"MGFoster" <[e-mail]> wrote in message
news:gT***************@newsread4.news.pas.earthlin k.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Doh... That's because I forgot you're working in an .adp project.
You'll have to set up the SQL statement in VBA & then put it into the
RecordSource. Something like the following in the form that's
opening:

Private Sub Form_Open(Cancel As Integer)

Dim strSQL As String

strSQL = "SELECT FixedAssets.*, Departments.Department " & _
"FROM FixedAssets INNER JOIN Departments " & _
"ON FixedAssets.Dept = Departments.Dept_Num " & _
"WHERE FixedAssets.Description LIKE '*" & _
Forms![Switchboard]![txtDescription] & "*'"

Me.RecordSource = strSQL

End Sub

Note the single-quotes in the WHERE clause. This should produce a
clause like this:

WHERE FixedAssets.Description Like '*television*'

if the Forms![Switchboard]![txtDescription] contains the word
"television."

The above assumes that you have the tables FixedAssets and Departments
linked to the project (look in the Tables tab of the database window).

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5WKT4echKqOuFEgEQKQmQCeJup/yTeqdaoKvEekZ6koqGKtNscAn2Yh
3da/Al51QkM3cJ05Fw2COijc
=3xYv
-----END PGP SIGNATURE-----

MVM wrote:

Thanks for the help. I tried this statement and I get an invalid SQL
statement error.
Any other ideas?
"MGFoster" <[e-mail]> wrote in message
news:sJ*****************@newsread4.news.pas.earthl ink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The form's recordsource, you want to "grab" the data in the
"switchboard" form's TextBox, should look like this:

SELECT FixedAssets.*, Departments.Department
FROM FixedAssets INNER JOIN Departments ON FixedAssets.Dept =
Departments.Dept_Num
WHERE FixedAssets.Description LIKE "*" &
Forms![Switchboard]![txtDescription] & "*"

- --
MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5Sn2YechKqOuFEgEQJ6TACfVB+SSjxouayzgxSZkxtlb5 xs+twAn0Yv
XBCJUkDJclvAFINg3o6m8DRy
=djus
-----END PGP SIGNATURE-----

MVM wrote:

Hi everyone,
I am working on an Access project (ADP). I have a switchboard form

setup to

allow the user to open up another form by entering search criteria in a

text

box and clicking a command button. Then the form opens using input
parameters and grabbing the data in the switchboard text box. My

question

is this, how do I grab the data from the switchboard form and put a

wildcard

on both ends of the data when passed to the input parameters?

On the form that opens I have the recordset set to:
SELECT FixedAssets.*, Departments.Department FROM FixedAssets INNER JOINDepartments ON FixedAssets.Dept = Departments.Dept_Num WHERE
FixedAssets.Description = ?

On the same form I have the Input Parameters set to:
FixedAssets.Description nvarchar LIKE "*" &
Forms![Switchboard]![txtDescription] & "*"

With this code it prompts me to input data when the form opens instead

of

grabbing it from the Switchboard form. If I take off the wildcards I

need

to have the exact description before it will find a record.

Thanks!
MVM