Quotes are still killing me 3

[annie52]

I just cannot seem to grasp the proper way to use quotes. Now, I want to filter a report and display results based on a timeframe chosen by the user.

=====================================
stDocName = "rptFollowup"
stSelectTech = "Tech='" & Forms![frmTechnician]![cboTechnician] & "'"

Select Case Frame10
Case 1
stTimeframe = "<" & Date
Case 2
stTimeframe = Date
Case 3
stTimeframe = ">=" & Date And "< " & DateAdd(d, 8, Date)
Case 4
stTimeframe = ">=" & Date And "<" & DateAdd(m, 1, Date)
End Select

Me.Visible = False
If stSelectTech = "Tech='All Technicians'" Then
DoCmd.OpenReport stDocName, acViewPreview
Else
DoCmd.OpenReport stDocName, acViewPreview, stSelectTech, "[Followup] = " & stTimeframe
End If
==============================

In Case 1, I get error 3075 (syntax/missing operator).
In Case 2, I don't get an error but that might be because there's no data fitting that selection.
In Cases 3 and 4, I get error 5 (invalid procedure call or argument).

I hope I didn't bite off more than I can chew. I'll appreciate any advice you can give me.

 

[Andrzejek]

Do yourself a favor and add this to your code:
[tt]
Select Case Frame10
Case 1
stTimeframe = "<" & Date
Case 2
stTimeframe = Date
Case 3
stTimeframe = ">=" & Date And "< " & DateAdd(d, 8, Date)
Case 4
stTimeframe = ">=" & Date And "<" & DateAdd(m, 1, Date)
End Select[blue]
Desbug.Print stTimeframe[/blue]
[/tt]
Put the breake just after the BLUE line and see its value in Immediate Window.

Have fun.

---- Andy

 

[traingamer]

This code:

"[Followup] [red][b]=[/b][/red] " & stTimeframe

is probably the source of some of your problems as it would introduce an extraneous equal sign into your SQL for cases 1, 3 and 4.

Additionally, case 3 and 4 should use the Between keyword like [Followup] between 06/24/2010 and 06/25/2010.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[Andrzejek]

I did this:

Dim Frame10 As Integer
Dim stTimeframe As String

For Frame10 = 1 To 4

    Select Case Frame10
        Case 1
            stTimeframe = "<" & Date
        Case 2
            stTimeframe = Date
        Case 3
            stTimeframe = ">=" & Date & " And <" & DateAdd("d", 8, Date)
        Case 4
            stTimeframe = ">=" & Date & " And <" & DateAdd("m", 1, Date)
    End Select

    Debug.Print stTimeframe
Next

And I got this as the output:
[tt]
<6/24/2010
6/24/2010
>=6/24/2010 And <7/2/2010
>=6/24/2010 And <7/24/2010
[/tt]

Is that what you wanted?

Have fun.

---- Andy

 

[Andrzejek]

Between would be the way to go, but the OP wants to have the range of dates '>=' AND just '<' NOT '<='

Between is invlusive, so it will include both the start date AND end date.

Have fun.

---- Andy

 

[annie52]

Hi Andy. I placed my quotes as you show above and now I get error 424 (object required) in each of the four cases. Ideas?

 

[dhookom]

Try

    stDocName = "rptFollowup"
    stSelectTech = "Tech='" & Forms![frmTechnician]![cboTechnician] & "'"
    
    Select Case Frame10
        Case 1
            stTimeframe = " < #" & Date & "#"
        Case 2
            stTimeframe = " = #" & Date & "#"
        Case 3
            stTimeframe = " Between #" & Date & "# And #" & _ 
               DateAdd(d, 8, Date) & "#"
        Case 4
            stTimeframe = " Between #" & Date & # And #" & _
               DateAdd(m, 1, Date) & "#"
    End Select
            
    Me.Visible = False
    If stSelectTech = "Tech='All Technicians'" Then
        DoCmd.OpenReport stDocName, acViewPreview
    Else
        DoCmd.OpenReport stDocName, acViewPreview, , _
           stSelectTech & " AND [Followup] " & stTimeframe
    End If

Duane
Hook'D on Access
MS Access MVP

 

[annie52]

Hi Duane. Well, we're making progress. Now, the first two cases work properly but I get error #5 (invalid procedure call or argument) when I run case 3 or 4. Here's my entire procedure. I hope you can see what I'm missing or doing wrong.
=======================================================
Private Sub cmdTechnician_Click()
On Error GoTo Err_cmdTechnician_Click

Dim stDocName As String
Dim stSelectTech As String
Dim stTimeframe As String

stDocName = "rptFollowup"
stSelectTech = "Tech='" & Forms![frmTechnician]![cboTechnician] & "'"

Select Case Frame10
Case 1
stTimeframe = " < #" & Date & "#"
Case 2
stTimeframe = " = #" & Date & "#"
Case 3
stTimeframe = " Between #" & Date & "# And #" & DateAdd(d, 8, Date) & "#"
Case 4
stTimeframe = " Between #" & Date & "# And #" & DateAdd(m, 1, Date) & "#"
End Select

Me.Visible = False
If stSelectTech = "Tech='All Technicians'" Then
DoCmd.OpenReport stDocName, acViewPreview
Else
DoCmd.OpenReport stDocName, acViewPreview, , _
stSelectTech & " AND [Followup] " & stTimeframe
End If

Exit_cmdTechnician_Click:
Exit Sub

Err_cmdTechnician_Click:
Call RPPErrorHandler
Resume Exit_cmdTechnician_Click

End Sub

 

[annie52]

I don't know if this helps, but --

When I run through Case 3, the Immediate window shows:
? stSelectTech
Tech='Jeff Moore'
? stTimeframe
>=6/24/2010 And <7/2/2010

When I run through Case 4, the Immediate window shows:
? stSelectTech
Tech='Jeff Moore'
? stTimeframe
>=6/24/2010 And <7/24/2010

 

[PHV]

Case 3
stTimeframe = " Between #" & Date & "# And #" & DateAdd([!]"[/!]d[!]"[/!], 8, Date) & "#"
Case 4
stTimeframe = " Between #" & Date & "# And #" & DateAdd([!]"[/!]m[!]"[/!], 1, Date) & "#"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[annie52]

I appreciate all of you. Stars for Duane and PH for actually getting me up and running.

 

[Andrzejek]

That's what happens when you don't read carefully the answers given to you. I had the "" around "d" and "w" long time ago (in my second post).

Have fun.

---- Andy

 

[annie52]

Hi Andy. You're absolutely correct. Sorry that I overlooked it!