| re: Looking for the right CRITERIA!
Geir Baardsen wrote:
[color=blue]
> Hi!
> I need to know where I go wrong:
>
> On a frmItems I have a listbox that shows all items in tblItems.
> Now the items is keys from different producers.
> So I can have three identical keys:
>
> OR-303 s,
> WA-303 s,
> FL-303 s
> and so on...
>
> My problem is that when I open the frmItems and have say...OR-303 s focused,
> I have a button I can click and which is supposed to show me similar keys,
> that is: 303 s, in the lstItemsInfo.
>
> So, this is the code in the form's OnLoad event:
>
> With Me.lstItemsInfo
> .RowSource = ""
> .ColumnCount = 4
> .ColumnWidths = "0cm;10cm;2cm;2cm"
> .ColumnHeads = True
> .BoundColumn = 1
> .RowSource = UCase("SELECT DISTINCTROW ItemID,ItemName,UnitPrice, EnvFee " _
> & "FROM tblItems " _
> & "ORDER BY ItemName;")
> End With
>
> And believe me, it works just fine! But...Yes I say BUT:
>
> I'll try to write the following sql that hopefully will
> change the listbox with new values when click on btnSIMILAR:
>
> With Me.lstItemsInfo
> .RowSource = ""
> .ColumnCount = 4
> .ColumnWidths = "0cm;10cm;2cm;2cm"
> .ColumnHeads = True
> .BoundColumn = 1
> .RowSource = UCase("SELECT DISTINCTROW ItemID,ItemName,UnitPrice, EnvFee " _
> & "FROM tblItems " _
>
> 'Now try to find similar keys that has 303 in the name
>
> & "WHERE (((ItemName) LIKE Left(Forms!frmItems!txtItemName.Text,4))) " _
>
> 'What is the correct criteria?
>
> & "ORDER BY ItemName;")
> End With
>
> So U see? I miss something in the criteria. U know what?[/color]
You want all the 303s?
"Where ItemName Like '??-" & Mid(txtItemName,4,3) & "*'"
[color=blue]
> Hopefully Yours,
>
> Me.Name[/color]
;-)
[color=blue]
> Private Sub Form_Close
> With Me.lstItemsInfo
> .Rowsource = ""
> End With
> end sub[/color]
I assume you have the prompt for parameter when closing the form then? :-)
| 
