Run query based on multiselect list box and specific date range 1

[EDGE99]

I am having some problems when using a forms criteria to run a query. I need to be able to select 1 or N names from the listbox and then select a beginning date and an ending date. The query should then be executed with the selection criteria listed above. Can anyone tell me what I am doing wrong here.

Here is what I have going:

1. Multi-select list box
2. Combo Box Beginning Month Range
3. Combo Box Ending Month Range
4. Control Button that run query based on the criteria selected (1-3)

Whats Working:
I can select 1 or N customer names from the listbox and it will run the query for all customers selected.

What are the problems

1. If I add the code to append the beginning date only
(will find records that are >= Beginning Month) and only
Select one customer name from the listbox the output
will be correct.

sDate = "((DateSold >=" & Me!cmbMonthStart & ")"
sWhere = "(" & sWhere & ")" & " And " & sDate & ")"

DoCmd.OpenQuery "qry_GetRevenue2"
DoCmd.ApplyFilter , sWhere

This one is successful:
Where = ((CustName = "Starbucks")) And ((posMonth >= 200602))

However if I select multiple customer names and the beginning date
the date range part of the query is ignored.

This one fails:
Where = ((CustName = "Starbucks") OR (CustName = "Sears")) And ((posMonth >= 200602))

2. If I select 1 or N customers from the listbox and select a beginning date and ending date. The date criteria seems to be ignored and all dates are listed.

sDate = "((DateSold >=" & Me!cmbMonthStart & ")"
eDate = "(DateSold <=" & Me!cmbMonthEnd & "))"

sWhere = "(" & sWhere & ")" & " And " & sDate & " Or " & eDate

DoCmd.OpenQuery "qry_GetRevenue2"
DoCmd.ApplyFilter , sWhere

this fails
Where = ((CustName = "Starbucks")) And ((posMonth >= 200602) OR (posMonth <= 200701))

The complete code for the Command Button is listed below.

Dim sWhere As String ' Where condition
Dim lst As ListBox ' multiselect list box
Dim vItem As Variant ' items in listbox
Dim iLen As Integer ' length of string.
Dim sDate As String ' Beginning Month of Where Condition
Dim eDate As String ' Ending Month of Where Condition


Set lst = Me!List0

'loop through all items in listbox
For Each vItem In lst.ItemsSelected
If Not IsNull(vItem) Then
    sWhere = sWhere & "(custNAME = """ & lst.ItemData(vItem) & """) OR "
End If
Next

iLen = Len(sWhere) - 4 ' Without trailing " OR ".
If iLen = 0 Then
  sWhere = "(" & Left$(sWhere, iLen) & ")"
End If

sWhere = Left(sWhere, Len(sWhere) - 3)

sDate = "((DateSold >=" & Me!cmbMonthStart & ")"
eDate = "(DateSold <=" & Me!cmbMonthEnd & "))"

sWhere = "(" & sWhere & ")" & " And " & sDate & " Or " & eDate

MsgBox (sWhere)

DoCmd.OpenQuery "qry_GetRevenue2"
DoCmd.ApplyFilter , sWhere

Set lst = Nothing

 

[TheAceMan1]

How are ya ScottG9905 . . .

The following should give you a better idea on handling the names:

[blue]   Dim Cri, LBx As ListBox, itm, Build As String, DQ As String
   
   Set LBx = Me!ListboxName
   DQ = """"
   
   For Each itm In LBx.ItemsSelected
      If Build <> "" Then
         Build = Build & ", " & DQ & LBx.Column(1, itm) & DQ
      Else
         Build = DQ & LBx.Column(1, itm) & DQ
      End If
   Next
   
   Cri = "WHERE ([CustName] IN (" & Build & "))"[/blue]

However I'm quite hungup on your dates which you show/label as months Can you expand on this and its datatype?

If the datatype is numeric (as far as months go) what about year end crossover?

[blue]Your Thoughts ? . . .[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

Perhaps this:

[blue]   Dim Cri, LBx As ListBox, itm, Build As String, DQ As String
   Dim sDate As String, eDate As String
   
   Set LBx = Me!ListboxName
   DQ = """"
   
   For Each itm In LBx.ItemsSelected
      If Build <> "" Then
         Build = Build & ", " & DQ & LBx.Column(1, itm) & DQ
      Else
         Build = DQ & LBx.Column(1, itm) & DQ
      End If
   Next
   
   sDate = Format(Me!cmbMonthStart, "mm-dd-yyyy")
   eDate = Format(Me!cmbMonthEnd, "mm-dd-yyyy")
   Cri = "WHERE ([CustName] IN (" & Build & ")) AND " & _
               "(([DateSold]>= #" & sDate & "#) AND " & _
                "([DateSold]<= #" & eDate & "#))'"
   DoCmd.OpenQuery "qry_GetRevenue2"
   DoCmd.ApplyFilter , Cri

Set LBx = Nothing[/blue]

See Ya! . . . . . .

 

[EDGE99]

Hey Ace,

The date values are actually numeric and not actual date values. I do really care about actual dates just need to be able to pull information based on month.

The table looks like this:
200501
200512
...
200703

I am not if you can pass that statement into the ApplyFilter for a query. I will try and modify my dummy database to reflect what you have provided to see if I can get this working.

I am thinking that I am close to getting with the way that I have but there seems to be some issues with the way you pass items into a query via the ApplyFilter method. Or I could just be smoking crack!!!

Thanks for the post

Scott

 

[TheAceMan1]

ScottG9905 . . .

Are you saying [blue]DateSold[/blue], [blue]cmbMonthStart[/blue], [blue]cmbMonthEnd[/blue], all comprise this numeric format?

See Ya! . . . . . .

 

[EDGE99]

Ace,

I figured out what the problem was. I was putting an "OR" between my date ranges.

sWhere = "(" & sWhere & ")" & " And " & sDate & " Or " & eDate

So it would show everything >= sDate or everything <= eDate! So changing the Or to an And cleared up the issue.

sWhere = "(" & sWhere & ")" & " And " & sDate & " And " & eDate

 

[EDGE99]

Ace,

Yes both combo box's and DateSold contain that same number format.

 

[TheAceMan1]

ScottG9905 . . .

Great! My Call reduces to:

[blue]   Dim Cri, LBx As ListBox, itm, Build As String, DQ As String
   
   Set LBx = Me!ListboxName
   DQ = """"
   
   For Each itm In LBx.ItemsSelected
      If Build <> "" Then
         Build = Build & ", " & DQ & LBx.Column(1, itm) & DQ
      Else
         Build = DQ & LBx.Column(1, itm) & DQ
      End If
   Next
   
   Cri = "WHERE ([CustName] IN (" & Build & ")) AND " & _
               "(([DateSold]>= " & Me!cmbMonthStart & ") AND " & _
                "([DateSold]<= " & Me!cmbMonthEnd & "))'"
   DoCmd.OpenQuery "qry_GetRevenue2"
   DoCmd.ApplyFilter , Cri

Set LBx = Nothing[/blue]

See Ya! . . . . . .