Parameter queries

N

Nandini

I have created a bibliographic database using Access 2003. Data types of all
the fields in the table are textual. Here some parameter queries have been
made to retrieve textual information through the text box. For the parameter
queries the fields are created through combinations of more than one regular
fields present in the table. I have designed the criteria as Like "*" &
[Forms]![Form1]![TextBox1] & "*" for a particular field. This crieteria can
retrieve any exact single word or phrase. But I want to retrieve information
putting more than one keyword in a single textbox at a time. What should be
the criteria for this?
Anybody can help me anyway? I dont have much more knowledge about visual
basic codes.
Early response would be appreciated.
With best regards,
 
M

MGFoster

Nandini said:
I have created a bibliographic database using Access 2003. Data types of all
the fields in the table are textual. Here some parameter queries have been
made to retrieve textual information through the text box. For the parameter
queries the fields are created through combinations of more than one regular
fields present in the table. I have designed the criteria as Like "*" &
[Forms]![Form1]![TextBox1] & "*" for a particular field. This crieteria can
retrieve any exact single word or phrase. But I want to retrieve information
putting more than one keyword in a single textbox at a time. What should be
the criteria for this?
Anybody can help me anyway? I dont have much more knowledge about visual
basic codes.
Early response would be appreciated.
With best regards,

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

Something like the following will return records if any of the TextBoxes
contain data that is in "column_name."

WHERE (Forms!Form1!TextBox1 IS NOT NULL
AND column_name Like "*" & Forms!Form1!TextBox1 & "*")
OR (Forms!Form1!TextBox2 IS NOT NULL
AND column_name Like "*" & Forms!Form1!TextBox2 & "*")
OR (Forms!Form1!TextBox3 IS NOT NULL
AND column_name Like "*" & Forms!Form1!TextBox3 & "*")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSXDA8IechKqOuFEgEQLfhACfUouVCCJqpgazf93ebmxliSVARSEAoLEQ
LWezwBiqWwlI7Y2l3eoZ5wfa
=BG+T
-----END PGP SIGNATURE-----
 
N

Nandini

Thanks for your kind help. But I want to retrieve information putting more
than one word (not a phrase) in one text box. Please help me.
With regards.
--
nandini


MGFoster said:
Nandini said:
I have created a bibliographic database using Access 2003. Data types of all
the fields in the table are textual. Here some parameter queries have been
made to retrieve textual information through the text box. For the parameter
queries the fields are created through combinations of more than one regular
fields present in the table. I have designed the criteria as Like "*" &
[Forms]![Form1]![TextBox1] & "*" for a particular field. This crieteria can
retrieve any exact single word or phrase. But I want to retrieve information
putting more than one keyword in a single textbox at a time. What should be
the criteria for this?
Anybody can help me anyway? I dont have much more knowledge about visual
basic codes.
Early response would be appreciated.
With best regards,

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

Something like the following will return records if any of the TextBoxes
contain data that is in "column_name."

WHERE (Forms!Form1!TextBox1 IS NOT NULL
AND column_name Like "*" & Forms!Form1!TextBox1 & "*")
OR (Forms!Form1!TextBox2 IS NOT NULL
AND column_name Like "*" & Forms!Form1!TextBox2 & "*")
OR (Forms!Form1!TextBox3 IS NOT NULL
AND column_name Like "*" & Forms!Form1!TextBox3 & "*")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSXDA8IechKqOuFEgEQLfhACfUouVCCJqpgazf93ebmxliSVARSEAoLEQ
LWezwBiqWwlI7Y2l3eoZ5wfa
=BG+T
-----END PGP SIGNATURE-----
 
D

Dale Fye

Well, if you want to do this as a true parameter query, where you run the
query, and an input box pops up, I cannot help you. There may be a way, but
I have not seen one. On the other hand, if you want to type keywords into a
textbox on a form, and build a criteria string on the fly, you could try
something like the following. This assumes you have a text box
(txt_KeyWords) and a command button cmd_Filter_Click

Private Sub cmd_Filter_Click

Dim strSQL as string
Dim strCriteria as string
Dim aKeyWords() as string
Dim intLoop as integer

aKeyWords() = Split(me.txt_KeyWords, " ")
For intLoop = lbound(aKeyWords) to ubound(aKeyWords)
strCriteria = strCriteria & "OR [FieldName] like " & chr$(34) & "*"
& aKeyWords(intLoop) & "*" & chr$(34)
Next
'cut off the initial "OR "
strCriteria = mid(strCriteria, 4)

'If all you want to do is filter the current form, then
me.Filter = strCriteria
me.FilterON

'or, if you want to use this in the SQL of a query, then
strSQL = currentdb.querydefs("someQuery").SQL & "WHERE " & strCriteria
'then do something with this SQL string

End Sub

HTH
Dale
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top