SQL select and listbox

[Sigurd Bruteig]

Can I use a parameter from a listbox in a query. Something like

SELECT tblTable.ID
FROM tblTable WHERE Listbox1 LIKE items.selected

Sigurd
--
_______________ _______________ ______________
KILLSPAM R e m o v e trippleX to reply
to email adress.

[Phil Stanton]

Re: SQL select and listbox

Try
Dim SQLStg as string
SQLStg = " SELECT tblTable.ID FROM tblTable "
SQLStg = SQLStg & "WHERE tblTable.ID = " & Forms!Listbox1

Good luck

Phil
"Sigurd Bruteig" <s-brutexxx@online .no> wrote in message
news:tiuob.3143 $mf2.39880@news 4.e.nsc.no...[color=blue]
> Can I use a parameter from a listbox in a query. Something like
>
> SELECT tblTable.ID
> FROM tblTable WHERE Listbox1 LIKE items.selected
>
> Sigurd
> --
> _______________ _______________ ______________
> KILLSPAM R e m o v e trippleX to reply
> to email adress.
>
>[/color]

[Sigurd Bruteig]

Re: SQL select and listbox

Thanks!
I want this to work with a multiselct listbox, thats the difficult issue.

Sigurd

"Phil Stanton" <phil@stantonfa mily.co.uk> skrev i melding
news:3fa277f0$0 $111$65c69314@m ercury.nildram. net...[color=blue]
> Try
> Dim SQLStg as string
> SQLStg = " SELECT tblTable.ID FROM tblTable "
> SQLStg = SQLStg & "WHERE tblTable.ID = " & Forms!Listbox1
>
> Good luck
>
> Phil
> "Sigurd Bruteig" <s-brutexxx@online .no> wrote in message
> news:tiuob.3143 $mf2.39880@news 4.e.nsc.no...[color=green]
> > Can I use a parameter from a listbox in a query. Something like
> >
> > SELECT tblTable.ID
> > FROM tblTable WHERE Listbox1 LIKE items.selected
> >
> > Sigurd
> > --
> > _______________ _______________ ______________
> > KILLSPAM R e m o v e trippleX to reply
> > to email adress.
> >
> >[/color]
>
>[/color]

[Phil Stanton]

Re: SQL select and listbox

OK, youve changed the rules.

Here is a snippet of code that finds people acording to their smoking habits

Option Compare Database
Option Explicit

Private Sub SmokingIDRelay_ AfterUpdate()

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer, SelectedRows As Integer
Set ctlSource = SmokingIDRelay
Set ctlDest = Me!CtlOutput
For intCurrentRow = 0 To ctlSource.ListC ount - 1
If ctlSource.Selec ted(intCurrentR ow) Then
If SelectedRows >= 1 Then
strItems = strItems & " or SmokingID = "
End If
strItems = strItems & ctlSource.Colum n(1, intCurrentRow)
SelectedRows = SelectedRows + 1
End If
Next intCurrentRow
strItems = strItems & ";"
' Reset destination control's RowSource property.
ctlDest.RowSour ce = "SELECT HostSurName, SmokingID FROM Hosts WHERE
SmokingID = "
ctlDest.RowSour ce = ctlDest.RowSour ce & strItems

End Sub

SmokingIDRelay is a multiselect list box and ctlDest.RowSour ce holds an SQL
with the hosts names that fit the selected smoking habits.

Hope that helps

Phil
"Sigurd Bruteig" <s-brutexxx@online .no> wrote in message
news:Epwob.3547 1$os2.515568@ne ws2.e.nsc.no...[color=blue]
> Thanks!
> I want this to work with a multiselct listbox, thats the difficult issue.
>
> Sigurd
>
> "Phil Stanton" <phil@stantonfa mily.co.uk> skrev i melding
> news:3fa277f0$0 $111$65c69314@m ercury.nildram. net...[color=green]
> > Try
> > Dim SQLStg as string
> > SQLStg = " SELECT tblTable.ID FROM tblTable "
> > SQLStg = SQLStg & "WHERE tblTable.ID = " & Forms!Listbox1
> >
> > Good luck
> >
> > Phil
> > "Sigurd Bruteig" <s-brutexxx@online .no> wrote in message
> > news:tiuob.3143 $mf2.39880@news 4.e.nsc.no...[color=darkred]
> > > Can I use a parameter from a listbox in a query. Something like
> > >
> > > SELECT tblTable.ID
> > > FROM tblTable WHERE Listbox1 LIKE items.selected
> > >
> > > Sigurd
> > > --
> > > _______________ _______________ ______________
> > > KILLSPAM R e m o v e trippleX to reply
> > > to email adress.
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Sigurd Bruteig]

Re: SQL select and listbox

This looks like the right thing. I will try it tomorrow. Thank you again,
and have a nice weekend.

Sigurd

"Phil Stanton" <phil@stantonfa mily.co.uk> skrev i melding
news:3fa2aa3a$0 $108$65c69314@m ercury.nildram. net...[color=blue]
> OK, youve changed the rules.
>
> Here is a snippet of code that finds people acording to their smoking[/color]
habits[color=blue]
>
> Option Compare Database
> Option Explicit
>
> Private Sub SmokingIDRelay_ AfterUpdate()
>
> Dim ctlSource As Control
> Dim ctlDest As Control
> Dim strItems As String
> Dim intCurrentRow As Integer, SelectedRows As Integer
> Set ctlSource = SmokingIDRelay
> Set ctlDest = Me!CtlOutput
> For intCurrentRow = 0 To ctlSource.ListC ount - 1
> If ctlSource.Selec ted(intCurrentR ow) Then
> If SelectedRows >= 1 Then
> strItems = strItems & " or SmokingID = "
> End If
> strItems = strItems & ctlSource.Colum n(1, intCurrentRow)
> SelectedRows = SelectedRows + 1
> End If
> Next intCurrentRow
> strItems = strItems & ";"
> ' Reset destination control's RowSource property.
> ctlDest.RowSour ce = "SELECT HostSurName, SmokingID FROM Hosts WHERE
> SmokingID = "
> ctlDest.RowSour ce = ctlDest.RowSour ce & strItems
>
> End Sub
>
> SmokingIDRelay is a multiselect list box and ctlDest.RowSour ce holds an[/color]
SQL[color=blue]
> with the hosts names that fit the selected smoking habits.
>
> Hope that helps
>
> Phil
> "Sigurd Bruteig" <s-brutexxx@online .no> wrote in message
> news:Epwob.3547 1$os2.515568@ne ws2.e.nsc.no...[color=green]
> > Thanks!
> > I want this to work with a multiselct listbox, thats the difficult[/color][/color]
issue.[color=blue][color=green]
> >
> > Sigurd
> >
> > "Phil Stanton" <phil@stantonfa mily.co.uk> skrev i melding
> > news:3fa277f0$0 $111$65c69314@m ercury.nildram. net...[color=darkred]
> > > Try
> > > Dim SQLStg as string
> > > SQLStg = " SELECT tblTable.ID FROM tblTable "
> > > SQLStg = SQLStg & "WHERE tblTable.ID = " & Forms!Listbox1
> > >
> > > Good luck
> > >
> > > Phil
> > > "Sigurd Bruteig" <s-brutexxx@online .no> wrote in message
> > > news:tiuob.3143 $mf2.39880@news 4.e.nsc.no...
> > > > Can I use a parameter from a listbox in a query. Something like
> > > >
> > > > SELECT tblTable.ID
> > > > FROM tblTable WHERE Listbox1 LIKE items.selected
> > > >
> > > > Sigurd
> > > > --
> > > > _______________ _______________ ______________
> > > > KILLSPAM R e m o v e trippleX to reply
> > > > to email adress.
> > > >
> > > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Pieter Linden]

Re: SQL select and listbox

"Sigurd Bruteig" <s-brutexxx@online .no> wrote in message news:<tiuob.314 3$mf2.39880@new s4.e.nsc.no>...[color=blue]
> Can I use a parameter from a listbox in a query. Something like
>
> SELECT tblTable.ID
> FROM tblTable WHERE Listbox1 LIKE items.selected
>
> Sigurd[/color]

Sigurd,

yes you can. You have to loop through the ItemsSelected collection of
the listbox and build your criteria on the fly... See this link:

http://www.mvps.org/access/forms/frm0007.htm