By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

parameter query passed from a form with wildcards

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
-----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


Nov 12 '05 #2

P: n/a
MVM
Thanks for the help. I tried this statement and I get an invalid SQL
statement error.
Any other ideas?
"MGFoster" <me@privacy.com> 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

Nov 12 '05 #3

P: n/a
-----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" <me@privacy.com> 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



Nov 12 '05 #4

P: n/a
MVM
Woohoo! That worked. Thanks!
"MGFoster" <me@privacy.com> 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" <me@privacy.com> 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


Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.