something better than Like (cbo) & "*" 2

[simon551]

Hi,
I'm using a like(cboName)& "*" criteria in my query to filter data in a subform. I like the way it works, where you get all results when blank and then it filters after the selection (afterevent, requery). The problem I find in using this though is that, with the autonumber field, you have duplicates.

Example: Like(1) returns 1,10,11,12,13, etc.

Is there some kind of "Like Distinct"? expression to use? or a better way to do this?


Thanks in advance,
-s

 

[ThomasLafferty]

If you don't want dupes, then you want to use = not like.

In the above scenario, on the criteria line, you would enter 1, not = 1, or like 1, just 1.


Tom

Live once die twice; live twice die once.

 

[simon551]

I like the "Like" functionality, though, used in the form.

 

[ThomasLafferty]

You could try:

Len([cboName].[Value]) = 1 And Like(1)

Hope this helps...
Tom

Live once die twice; live twice die once.

 

[traingamer]

instead of

...like (cboName)& "*"

try

...like iif(not is null (cboname), (cboname), "*")

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[simon551]

Is this what you meant, Mr. Lafferty:

Len([Forms]![frmContacts_Lists]![cboSponsorCo])=([Forms]![frmContacts_Lists]![cboSponsorCo] And Like ([Forms]![frmContacts_Lists]![cboSponsorCo]))

?
This is working for now, although it's cumbersome:


Private Sub cboSponsorCo_AfterUpdate()
If Not Trim(Me.cboSponsorCo & "") = "" Then
If Trim(Me.cboSponsorCo & "") < 10 Then
[subformContactsList].Form.RecordSource = "qryContacts_frmContactsList2"
Else
Me.subformContactsList.Requery
End If
End If

End Sub


I'm trying your suggestion, Greg. Will let you know.

 

[ThomasLafferty]

Well, in all honesty, I didn't have all of your field names, so I can't really take credit for your work around. I think I will lurk and see what happens with this one.

Tom

Live once die twice; live twice die once.

 

[simon551]

Like (IIf((Not Trim([Forms]![frmContacts_Lists]![cboSponsorCo] & "")=""),([Forms]![frmContacts_Lists]![cboSponsorCo]),"*"))

Works!

Thanks, Greg!