syntax for select case conditional statement

[pke8jt]

Problem : I get red colored on

Select qry.Parameters("QuarterBeginDate").Value

Question: what kind of statement should I use for correct syntax?

Code:

qry.Parameters("QuarterBeginDate").Value = [Forms]![Run]![QuarterBeginOrderDate]
qry.Parameters("QuarterEndDate").Value = [Forms]![Run]![QuarterEndOrderDate]

Dim c As Integer

Select qry.Parameters("QuarterBeginDate").Value
Case Is < 4
c = 7
Case Is = 5, 6, 7
c = 8
Case Is = 8, 9, 10
c = 9
Case Is = 11, 12, 13
c = 10
End Select

 

[isladogs]

Add date delimiters in the first 2 lines of code

Problem : I get red colored on

Select qry.Parameters("QuarterBeginDate").Value

Question: what kind of statement should I use for correct syntax?

Code:

qry.Parameters("QuarterBeginDate").Value = "#" & [Forms]![Run]![QuarterBeginOrderDate] & "#"
qry.Parameters("QuarterEndDate").Value = "#" & [Forms]![Run]![QuarterEndOrderDate] & "#"
Dim c As Integer

Select qry.Parameters("QuarterBeginDate").Value
Case Is < 4
c = 7
Case Is = 5, 6, 7
c = 8
Case Is = 8, 9, 10
c = 9
Case Is = 11, 12, 13
c = 10
End Select

 

[pke8jt]

Hi Collin,
could you explain what do you mean by date delimiter?

 

[Mark_]

The "#" at the beginning and end tell Access you are dealing with a DATE instead of another type of data.

 

[isladogs]

Look at the changes I made to your code.
Needed so Access can read the date values correctly

 

[pke8jt]

I see what you mean! will run and let you know

 

[pke8jt]

well I did and I still have red line on

Select qry.Parameters("QuarterBeginDate").Value

Thank you for your time to looking into this

 

[pke8jt]

Well I tried

if Month(qry.Parameters("BeginDate")) + 1 = 2 Then c = 1
if Month(qry.Parameters("BeginDate")) + 1 = 3 Then c = 1
if Month(qry.Parameters("BeginDate")) + 1 = 4 Then c = 1
if Month(qry.Parameters("BeginDate")) + 1 = 5 Then c = 2
if Month(qry.Parameters("BeginDate")) + 1 = 6 Then c = 2
if Month(qry.Parameters("BeginDate")) + 1 = 7 Then c = 2
if Month(qry.Parameters("BeginDate")) + 1 = 8 Then c = 3
if Month(qry.Parameters("BeginDate")) + 1 = 9 Then c = 3
if Month(qry.Parameters("BeginDate")) + 1 = 10 Then c = 3
if Month(qry.Parameters("BeginDate")) + 1 = 11 Then c = 4
if Month(qry.Parameters("BeginDate")) + 1 = 12 Then c = 4
if Month(qry.Parameters("BeginDate")) + 1 = 13 Then c = 4

instead then I get 'Item not found in this collection' when the user input '8' for August, on line
If Month(qry.Parameters("BeginDate")) + 1 = 2


How can I prevent the error message???

 

[Mark_]

Try

Debug.Print "BeginDate Param = " & Qry.Parameters!BeginDate and see if that works.

I looked here
https://msdn.microsoft.com/en-us/library/office/ff193967.aspx
for a sample of how to reference.

 

[isladogs]

Item not found in this collection is error 3265

So you COULD suppress the error message using an exception in error handling code

Sub MyProcedureName()

On Error GoTo Err_Handler

... your code goes here

Exit_Handler:
     End Sub

Err_Handler:
     If err=3265 Then Resume Next
     MsgBox "Error " & err.Number & " " & err.Description
     Resume Exit_Handler:

End Sub

However I would strongly recommend that you identify WHY the error occurred then FIX it rather than SUPPRESS the error