Using the asterisk to select all revisited

[Gina Maylone]

I am using this to fill a text box with a delimited string.

Private Sub BidStatus_Click()

    Dim Items   As Variant
    Dim Item    As Variant
    ' NB: Make this a Private variable of the code module.
    Dim filter1 As String
       If Me.BidStatus.Column(0) = "*" Then


Dim j


With Me.BidStatus
    For j = 0 To .ListCount - 0
         .Selected(j) = True
    Next
End With


End If


 
End Sub

It results in the following: IN ('*','Budget/Engineer','Design Build','Lost','Not Tracking','Omit','Open/Quote','Option','Plan & Spec.','REBID','Won')

I don't want the asterisk included in that statement. I've tried replacing (using Replace) it with nothing, to no avail (Error "Argument not optional").

  Dim str As String

    str = Me.filter1


    Replace InStr(1, str, "*", vbTextCompare)

Any other ideas that I can try? Thank you for your time!

[pbaldy]

I'm curious if it actually hurts anything, but you could start your loop at 1 instead of 0 if it's the first item.

[Welshgasman]

I do not think your Replace function is correct?

tt="IN ('*','Budget/Engineer','Design Build','Lost','Not Tracking','Omit','Open/Quote','Option','Plan & Spec.','REBID','Won')"
tt1 = replace(tt,"'*',","")
? tt1
IN ('Budget/Engineer','Design Build','Lost','Not Tracking','Omit','Open/Quote','Option','Plan & Spec.','REBID','Won')

[ranman256]

in vb, 0 is the 1st item of lists.

[Gina Maylone]

And BOOM! We have a winner! Thanks again Paul! You're a rockstar!!

[pbaldy]

And BOOM! We have a winner! Thanks again Paul! You're a rockstar!!

Happy to help!

[Gina Maylone]

Paul,
Should this

IN ('Budget/Engineer','Design Build','Lost','Not Tracking')

work as query criteria? When I copy and paste the actual output text, it works as it should, but when I reference the form field, I get nothing.

SELECT DISTINCTROW ProjectItems.ItemBid, MFGs.MFG, Projects.BidDate, Projects.ProjectName, BidStatus.BidStatusFROM MFGs INNER JOIN (BidStatus INNER JOIN (Projects INNER JOIN ProjectItems ON Projects.ProjectID = ProjectItems.ProjectID) ON BidStatus.BidStatusID = ProjectItems.BidStatusID) ON MFGs.MFGID = ProjectItems.MFGID
GROUP BY ProjectItems.ItemBid, MFGs.MFG, Projects.BidDate, Projects.ProjectName, BidStatus.BidStatus, ProjectItems.MFGID
HAVING (((MFGs.MFG)=[Forms]![CompareSales]![filter2]) AND ((Projects.BidDate) Between [Forms]![CompareSales]![firstbegindate_beginr] And [Forms]![CompareSales]![firstbegindate_endr]) AND ((BidStatus.BidStatus)=[Forms]![CompareSales]![filter1]));

TIA!

[pbaldy]

It works as a criteria but not from a form reference (to the best of my knowledge). In essence you're saying "WHERE FieldName = In(...)" which is invalid SQL with both = and In().

If you're opening a form or report, I'd use it as a wherecondition.

[Gina Maylone]

Thank you Welshgasman! It's perfect! Happy holidays to you!