Query with Date variable almost works..

[mafhobb]

Hi,

I have a combo box "cmbtimeframe" with the following code in the AfterUpdate event:

Dim HowLong As Integer
Dim TimeFrame As String

    cmbTimeFrame.SetFocus
    TimeFrame = cmbTimeFrame.Value

If TimeFrame = "One Week" Then
    HowLong = 7
ElseIf TimeFrame = "Two Weeks" Then
    HowLong = 15
ElseIf TimeFrame = "One Month" Then
    HowLong = 30
ElseIf TimeFrame = "Three Months" Then
    HowLong = 90
ElseIf TimeFrame = "Six Months" Then
    HowLong = 180
ElseIf TimeFrame = "One Year" Then
    HowLong = 365
ElseIf TimeFrame = "Forever" Then
    HowLong = 20000
End If

'SQL Query
Me.lstsearch.RowSource = "Select [SubCallID], [CallID], [SubCallDate], [SKU], [WhoPickedUp], [IssueType], [StatusAfterCall], [ResolutionDetails], [CustomerID] " & _
                        "From [EmployeeSearch] " & _
                        "Where ([subcalldate]>((Date())-[HowLong])) " & _
                        "And [WhoPickedUp] like '*" & Me.txtsearch & "*'"

Me.lstsearch.Requery

The idea is that the user can pick a value in the combo box (like One Month) and the SQL statement will return results for the last 30 days.

The problem is that the SQL statement is not picking up the value of the "HowLong" variable and it is instead displaying a box asking for that value. Once I enter is (7, for example), the query works fine.

What do I need to do so the HowLong variable is read into the SQL statement?

Thanks

mafhobb

 

[namliam]

You need to substitute in the HowLong variable exactly like your doing with the form control Me.TxtSearch.

Only difference being.... No need for the '' to identify it as a text
And the [] around HowLong you should drop as well.

 

[mafhobb]

You mean...

              "Where ([subcalldate]>((Date())-& HowLong &)) " & _

??

 

[mafhobb]

This:

                        "Where ([subcalldate]>((Date())- & HowLong & )) " & _

returns no errors, but no results either

mafhobb

 

[namliam]

You mean...

              "Where ([subcalldate]>((Date())-& HowLong &)) " & _

??

Almost, try again...

"And [WhoPickedUp] like '*" & Me.txtsearch & "*'"

the bolded part you got right, now find the other differences.

 

[mafhobb]

                        "Where [subcalldate] > (((Date())- '*" & HowLong & "*" & _

That does not work

                        "Where [subcalldate] > (((Date())- '*" & HowLong & _

This does not either



Hint? What are you referring to?

mafhobb

 

[boblarson]

I would rewrite it like this:

Dim HowLong As Integer
 
 
Select Case cmbTimeFrame
    Case "One Week"
       HowLong = 7
    Case "Two Weeks"
       HowLong = 15
    Case "One Month"
       HowLong = 30
    Case "Three Months"
       HowLong = 90
    Case "Six Months" 
       HowLong = 180
    Case "One Year" 
       HowLong = 365
    Case "Forever"
       HowLong = 20000
End Select
 
'SQL Query
Me.lstsearch.RowSource = "Select [SubCallID], [CallID], [SubCallDate], [SKU], [WhoPickedUp], [IssueType], [StatusAfterCall], [ResolutionDetails], [CustomerID] " & _
                        "From [EmployeeSearch] " & _
                        "Where ([subcalldate]>([COLOR=red][B]#[/B][/COLOR]" & [COLOR=blue]DateAdd("d", -(HowLong), Date())[/COLOR] & "[COLOR=red][B]#[/B][/COLOR]" & _
                        "And [WhoPickedUp] like '*" & Me.txtsearch & "*'"

 

[boblarson]

now find the other differences.

And sorry to ruin the surprise - but I thought streamlining the code to get rid of those nasty ElseIf's would be good too.

 

[namliam]

And sorry to ruin the surprise - but I thought streamlining the code to get rid of those nasty ElseIf's would be good too.

Elseif isnt nasty.... just another way of doing a case, though a case perhaps is a bit more applicable here.

If you want to streamline the code, then you want to change the combobox to have a bind value that actually contains the value that this code is creating.

Also for "forever" you simply want NO where on the Select not a semi meaningless where

Now first the way to fix your codeMafHobb as shown by bob in a way except with some explanation...
You have to substitute in the variable, you dont just do that with the & & you also have to take it out of the textstring...

"Where ([subcalldate]>((Date())-[HowLong])) "

Should then be:

"Where ([subcalldate]>((Date())-" & HowLong & ")) "

Which turns out to be the same as bob's solution, except the way you did it.

now how I would solve it, make the combo box with a (hidden) bound column, which contains the 7,15,30... and make the last value -1 instead of 20000.

Then... use this (air)code:

Dim mySQL as string
mysql = ""
mySQL = mySQL & " Select [SubCallID], [CallID], [SubCallDate], [SKU], [WhoPickedUp], [IssueType], [StatusAfterCall], [ResolutionDetails], [CustomerID] "
mySQL = mySQL & " From   [EmployeeSearch] " 
mySQL = mySQL & " WHERE  [WhoPickedUp] like '*" & Me.txtsearch & "*' "
if me.cmbTimeFrame >= 0 then
    mySQL = mySQL & " and [subcalldate]>Date()-" & HowLong & " "
end if
Me.lstsearch.RowSource = mysql
Me.lstsearch.Requery

 

[mafhobb]

All right, I have gone step by step here to try to understand how this works.

First I modified my select sentence by adding

"Where ([subcalldate]>((Date())-" & HowLong & ")) "

and that worked with one small problem. When filtering for "Two Weeks" or "One Year" I got no results. I tried several different things, among them check and see if the numeric value was being assigned to the variable (hence the checkbox) and that was working. Then I started to change the variable's value when selecting "Two Weeks" through the code and I found that if the value was 0 to 10, it would return results. Then if the assigned value was 11 through 17 would return no results. Higher than 17, and I would get results again...Not sure what is happening but it sure feels strange...I have also found that other numbers (like 200, 220, 300) work, but 350, 364 and 365 do not.

Second, I tried most of Boblarson code, basically replacing all the If..ElseIf statements with Case statements and I got the same results as above.

Here is what the code looks like now:

Private Sub cmbTimeFrame_AfterUpdate()

Dim HowLong As Integer
    
    HowLong = 0
    
   cmbTimeFrame.SetFocus
   TimeFrame = cmbTimeFrame.Value
   
Select Case cmbTimeFrame
    Case "One Week"
       HowLong = 7
    Case "Two Weeks"
      HowLong = 14
    Case "One Month"
       HowLong = 30
    Case "Three Months"
       HowLong = 90
    Case "Six Months"
       HowLong = 180
    Case "One Year"
       HowLong = 365
    Case "Forever"
       HowLong = 2000
End Select

MsgBox HowLong
                        
Me.lstsearch.RowSource = "Select [SubCallID], [CallID], [SubCallDate], [SKU], [WhoPickedUp], [IssueType], [StatusAfterCall], [ResolutionDetails], [CustomerID] " & _
                        "From [EmployeeSearch] " & _
                        "Where ([subcalldate]>((Date())-" & HowLong & ")) " & _
                        "And [WhoPickedUp] like '*" & Me.txtsearch & "*'"

Me.lstsearch.Requery
End Sub

Since I am having the problem mentioned above, I'll now replace the entire code with Boblarson's code and try. Stay posted

mafhobb

 

[mafhobb]

Ok. Now I have used Boblarson's code exactly as posted in post #7 and while I get no errors, I get no results either. The case method works and the variable HowLong gets assigned the appropriate value, but no results are returned by the SQL statement at all.

I'll now try MamLiam method.

mafhobb

 

[mafhobb]

Well, I have now tested mamliam's code

Dim mySQL As String
mySQL = ""
mySQL = mySQL & " Select [SubCallID], [CallID], [SubCallDate], [SKU], [WhoPickedUp], [IssueType], [StatusAfterCall], [ResolutionDetails], [CustomerID] "
mySQL = mySQL & " From   [EmployeeSearch] "
mySQL = mySQL & " WHERE  [WhoPickedUp] like '*" & Me.txtsearch & "*' "
If Me.cmbTimeFrame >= 0 Then
MsgBox Me.cmbTimeFrame
    mySQL = mySQL & "and [subcalldate]>Date()-" & HowLong & " "
End If
Me.lstsearch.RowSource = mySQL
Me.lstsearch.Requery

with similar results to Boblarson's...it does not return any results...I have added a msgbox to make sure that cmbtimeframe had the correct values and it does. But still...no results...

Any ideas?

mafhobb

 

[namliam]

Is your SubCallDate an actually datetime field or is it a text field.

Problems like this usually indicate it being a text field.

 

[namliam]

Also you can put:
...
End If
Debug.Print mySQL
Me.lstsearch.RowSource = mySQL
...

So you can see the full sql generated in the debug window, which you can get by pressing ALT + F11

 

[mafhobb]

Yes!...The date was a text field. That is why it behaved inconsistently.

Thank YOU!!!

mafhobb

 

[boblarson]

Elseif isnt nasty.... just another way of doing a case, though a case perhaps is a bit more applicable here.

Another "messy" way, is what I do call it. Anything more than one or two ElseIf's and it really should be turned into a Select Case statement as they are much easier to read.

If you want to streamline the code, then you want to change the combobox to have a bind value that actually contains the value that this code is creating.

True, but I was streamlining the existing structure, not redesigning.

 

[Sybilla]

I have a basic question from what you're working with - is that the correct syntax to query weekly reports?

WHERE (((WorkoutEntry.[Workout Date])=DateAdd("d",-(7),Date())))

 

[namliam]

WHERE (((WorkoutEntry.[Workout Date])=DateAdd("d",-(7),Date())))

This will pick up the results for the last 7 days.... this works just as well as:
Somewhat cleaned up
WHERE WorkoutEntry.[Workout Date]=DateAdd("d",-7,Date())
Or something a little different:
WHERE WorkoutEntry.[Workout Date]=Date() - 7

FYI, no prefix for the table name and a space in column names, bad bad bad boy.
Read up on naming conventions