|
 |
access thread: Getting field for a query from a Combo Box and the criteria from a text box
Message #1 by "Claudiu Grosaru" <maggi@d...> on Sun, 15 Jul 2001 19:50:32
|
|
Hello!
I am new in access and I whant to create a form wich query a database.
I whant the field being taken from a Combo Box, and the criteria from a
text box.
Something like:
SELECT * FROM MyTable WHERE a like b
and "a" is a value from a Combo Box and b is typed in a text box by the
user.
I whant to do this with RunSQL command, but I don't know exactly how.
Please give me some advices.
Thank you verry much!
Claudiu G.
Message #2 by m.noordegraaf@a... on Mon, 16 Jul 2001 14:28:31
|
|
Hello,
It depends where you want the output to be sent to. The easiest is if you
send the output selection to a report.
The DoCmd.OpenReport has several options; you could use the Where option
or the filtername option.
example:
Dim strSQL As String
strSql = "SELECT * FROM MyTable WHERE a like b"
DoCmd.OpenReport Report1, acViewPreview, strSQL
In your case it would be something like"
Dim strSQL As String
strSql = "SELECT * FROM MyTable WHERE " & cmbbxCodeA.Value & " like " &
txtCodeB.Value
DoCmd.OpenReport Report1, acViewPreview, strSQL
You could use this method with OpenForm & othermethods to!
succes,
Micha
> Hello!
>
> I am new in access and I whant to create a form wich query a database.
> I whant the field being taken from a Combo Box, and the criteria from a
> text box.
> Something like:
>
> SELECT * FROM MyTable WHERE a like b
>
> and "a" is a value from a Combo Box and b is typed in a text box by the
> user.
>
> I whant to do this with RunSQL command, but I don't know exactly how.
> Please give me some advices.
>
> Thank you verry much!
> Claudiu G.
Message #3 by "M.Puk" <M.Puk@x...> on Tue, 17 Jul 2001 20:55:20 +1200
|
|
Subject: [access] Re: Getting field for a query from a Combo Box and the
criteria from a text box
Hi All.
The following sql string sent as a possible answer to original question from
Claudia Grosaru will not work because the strings returned from the combobox
need to be enclosed in single quoation marks within the sql string..
> Dim strSQL As String
>
> strSql = "SELECT * FROM MyTable WHERE " & cmbbxCodeA.Value & " like " &
> txtCodeB.Value
>
> DoCmd.OpenReport Report1, acViewPreview, strSQL
The following code will work, it opens a recordset but the syntax for the
select where statement will be the same.
Note the position of the single quoate marks.
The code uses a text box and a combobox to complete the select statement
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("select * from YourTable where FieldInYourTable
'" & ATextBox & "'" & " and AnotherField = '" ComboboxValue & "'")
The following code may help anyone trying to use part of a date as a sql
string.
I tried using hash marks to enclose the part date as with normal full date
sql strings but wouldn,t work.
I use this code to pull records for the month that the user entered in a
inputbox.
You need to format the date field in the where clause to the same format as
what you expect to get from the inputbox so
that the LIKE can compare the string from the inputbox with the formated
date field
Note you need to enclose the field named date as [date] and you need all the
quoatation marks
Dim themonth
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
themonth = InputBox("Enter the MONTH/YEAR you wish to
view.","ENTERMONTH/YEAR eg 06/2001")
Set rs = db.OpenRecordset("select * from YourTable where
format$([date],""MM/yyyy"") like ""*" & themonth & "", dbOpenSnapshot)
Cheers to all.
Message #4 by m.noordegraaf@a... on Tue, 17 Jul 2001 14:26:11
|
|
Yes thank you for setting that straight. I was doing it from the top of my
head and wasn't sure. Anyway it can be done in a lot of ways.
yours,
Micha
> Subject: [access] Re: Getting field for a query from a Combo Box and the
> criteria from a text box
> Hi All.
>
> The following sql string sent as a possible answer to original question
from
> Claudia Grosaru will not work because the strings returned from the
combobox
> need to be enclosed in single quoation marks within the sql string..
>
> > Dim strSQL As String
> >
> > strSql = "SELECT * FROM MyTable WHERE " & cmbbxCodeA.Value & " like " &
> > txtCodeB.Value
> >
> > DoCmd.OpenReport Report1, acViewPreview, strSQL
>
>
> The following code will work, it opens a recordset but the syntax for the
> select where statement will be the same.
> Note the position of the single quoate marks.
> The code uses a text box and a combobox to complete the select statement
>
> Dim db As Database
> Dim rs As Recordset
> Set db = CurrentDb()
> Set rs = db.OpenRecordset("select * from YourTable where
FieldInYourTable
> '" & ATextBox & "'" & " and AnotherField = '" ComboboxValue & "'")
>
> The following code may help anyone trying to use part of a date as a sql
> string.
> I tried using hash marks to enclose the part date as with normal full
date
> sql strings but wouldn,t work.
> I use this code to pull records for the month that the user entered in a
> inputbox.
> You need to format the date field in the where clause to the same format
as
> what you expect to get from the inputbox so
> that the LIKE can compare the string from the inputbox with the formated
> date field
> Note you need to enclose the field named date as [date] and you need all
the
> quoatation marks
>
> Dim themonth
> Dim db As Database
> Dim rs As Recordset
> Set db = CurrentDb()
>
> themonth = InputBox("Enter the MONTH/YEAR you wish to
> view.","ENTERMONTH/YEAR eg 06/2001")
> Set rs = db.OpenRecordset("select * from YourTable where
> format$([date],""MM/yyyy"") like ""*" & themonth & "", dbOpenSnapshot)
>
> Cheers to all.
>
>
>
>
|
|
|
View Cart
