Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: case statments in vba

    quick question I have a bit of code that basiacly creates a query dinamiacly and then passes it to my SQL server. it works fine untill i have to pass a case statment to the server. the vba errors out saying that im missing an operator but when I run the qurey manualy in access and in SQL query analyzer it works fine. Has any one ever seen this????
    I realy need a bit of help here
    Jim

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: case statments in vba

    Originally posted by JDionne
    quick question I have a bit of code that basiacly creates a query dinamiacly and then passes it to my SQL server. it works fine untill i have to pass a case statment to the server. the vba errors out saying that im missing an operator but when I run the qurey manualy in access and in SQL query analyzer it works fine. Has any one ever seen this????
    I realy need a bit of help here
    Jim
    This is the code if anyone is interested


    Dim Measuerment As String
    Dim ctlSource As Control
    Dim strItems As String
    Dim intCurrentRow As Integer
    Set ctlSource = Customer_List

    For intCurrentRow = 0 To ctlSource.ListCount - 1
    If ctlSource.Selected(intCurrentRow) Then
    strItems = strItems & ", '" & ctlSource.Column(0, intCurrentRow) & "' "
    End If
    Next intCurrentRow



    'Set Week Variables
    If Week_From_Combo = "Please Select Week" Or Week_To_Combo = "Please Select Week" Then
    TimeBox = MsgBox("Please Select a Time Range", vbOKOnly, "Unknown Time Period")
    End
    Else
    From_week = [Forms]![Form:_Multi_Customer_Trend_Graph]![Week_From_Combo]
    to_week = " and " & [Forms]![Form:_Multi_Customer_Trend_Graph]![Week_To_Combo]
    From_week_Value = "From Week " & [Forms]![Form:_Multi_Customer_Trend_Graph]![Week_From_Combo]
    to_week_Value = ", To Week " & [Forms]![Form:_Multi_Customer_Trend_Graph]![Week_To_Combo]
    End If

    'Set Direction Variable
    If Direction_Combo <> "*" Then
    Direction = " and [Direction] like " & "'" & [Forms]![Form:_Multi_Customer_Trend_Graph]![Direction_Combo] & "'"
    Direction_Value = ", Direction: " & [Forms]![Form:_Multi_Customer_Trend_Graph]![Direction_Combo]
    Else
    Direction = ""
    Direction_Value = ""
    End If

    'Set String Variable
    If String_Combo <> "*" Then
    String_Var = " and [String] like " & "'" & [Forms]![Form:_Multi_Customer_Trend_Graph]![String_Combo] & "'"
    String_Value = ", String: " & [Forms]![Form:_Multi_Customer_Trend_Graph]![String_Combo]
    Else
    String_Var = ""
    String_Value = ""
    End If


    If Measures = 1 Then
    Measuerment = ", Sum(FFE)"
    ElseIf Measures = 2 Then
    Measuerment = ", Sum(containers)"
    ElseIf Measures = 3 Then
    Measuerment = ", Sum(freight)"
    ElseIf Measures = 4 Then
    Measuerment = ", case when sum(ffe) <> 0 then sum(freight)/sum(ffe) else 0 end as Metric "
    ElseIf Measures = 5 Then
    Measuerment = ", sum(freight)/Sum(containers)"
    End If
    test.Value = Measuerment
    Dim graph_title_text As String

    If Measures = 1 Then
    graph_title_text = "FFEs By Week"
    ElseIf Measures = 2 Then
    graph_title_text = "Containers By Week"
    ElseIf Measures = 3 Then
    graph_title_text = "Revenue By Week"
    ElseIf Measures = 4 Then
    graph_title_text = "Revenue/FFEs By Week"
    ElseIf Measures = 5 Then
    graph_title_text = "Revenue/Containers By Week"
    End If


    Dim Cur_DB As DAO.Database
    Dim Multi_Graph_RS As DAO.QueryDef
    Set Cur_DB = CurrentDb()

    'On Error GoTo NOCUSTOMERSELECTED

    ' delete qrydef if it exists
    For Each qdf In Cur_DB.QueryDefs
    If qdf.Name = "Multi_Graph" Then
    Cur_DB.QueryDefs.Delete "Multi_Graph"
    Exit For
    End If
    Next qdf
    DoCmd.SetWarnings True

    Set Multi_Graph_RS = Cur_DB.CreateQueryDef("Multi_Graph", "select account , Week as [time] " & Measuerment & " as [Metric] " & _
    " From DM_GLOBAL_ACCOUNTS_AGGR_TBL" & _
    " Where account is not null" & _
    " and week between " & From_week & to_week & _
    " and account in (" & Right(strItems, Len(strItems) - 2) & ")" & _
    Direction & String_Var & _
    " Group by account, Week " & _
    " Order by Week asc, Account asc")
    Multi_Graph_RS.Connect = "ODBC;Description=DataWarehouse;DRIVER=SQL Server;SERVER=scnc044db;UID=globalsales;PWD=global ;Network=DBMSSOCN;"

    Multi_Graph_Chart.RowSourceType = "table/query"
    Multi_Graph_Chart.RowSource = "TRANSFORM Sum(Multi_Graph.Metric) AS [Metric] SELECT mid(Multi_Graph.[Time],1,4)& '_' & mid(Multi_Graph.[Time],5,2) AS [Time] FROM Multi_Graph GROUP BY mid(Multi_Graph.[Time],1,4)& '_' & mid(Multi_Graph.[Time],5,2) PIVOT Multi_Graph.account;"


  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Jim,

    i'm still not happy with your error handler... better handling might not fix your current problem but it might tell you what that problem is.

    stick onError in the procedure _as_early_as_possible_ to grab ALL errors (not just your err.number = 5)

    assuming your current is a sub (see mine of yesterday for simple func)



    private sub mySub()
    on error goto err_mySub 'traps ALL errors from now to exitSub

    'here is your complex code


    exit_err_mySub: 'start of your exit routine

    'any tidy-up code to execute with or without error triggered
    ' e.g. set recs=nothing


    exit sub 'you exit here _always_

    err_mySub: 'you only get here if an error is triggered

    'first handle any anticipated errors (with if, or selectCase):

    if err.number = 5 then 'your situ from yesterday
    msgbox "Please select a customer", vbokonly, "Operator Error"

    else 'then treat all remaining unanticipated errors generically
    'this can be real helpful tracking down development errors once you
    'have switched off generic error msgs with your onError statement

    msgbox "Error #" & err.number & "(" & err.description & ")", vbcritical _
    & vbokonly, "Call Jim on extension 123"
    endif

    resume exit_err_mySub 'then go back to your exit routine

    'yes you can forget the resume and just fall off the end of the code but
    'you then cant run tidy-up code... risk is that your stuff ends up in
    'a different state depending on whether you had an error or not


    end sub



    izy

  4. #4
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by izyrider
    Jim,

    i'm still not happy with your error handler... better handling might not fix your current problem but it might tell you what that problem is.

    stick onError in the procedure _as_early_as_possible_ to grab ALL errors (not just your err.number = 5)

    assuming your current is a sub (see mine of yesterday for simple func)



    private sub mySub()
    on error goto err_mySub 'traps ALL errors from now to exitSub

    'here is your complex code


    exit_err_mySub: 'start of your exit routine

    'any tidy-up code to execute with or without error triggered
    ' e.g. set recs=nothing


    exit sub 'you exit here _always_

    err_mySub: 'you only get here if an error is triggered

    'first handle any anticipated errors (with if, or selectCase):

    if err.number = 5 then 'your situ from yesterday
    msgbox "Please select a customer", vbokonly, "Operator Error"

    else 'then treat all remaining unanticipated errors generically
    'this can be real helpful tracking down development errors once you
    'have switched off generic error msgs with your onError statement

    msgbox "Error #" & err.number & "(" & err.description & ")", vbcritical _
    & vbokonly, "Call Jim on extension 123"
    endif

    resume exit_err_mySub 'then go back to your exit routine

    'yes you can forget the resume and just fall off the end of the code but
    'you then cant run tidy-up code... risk is that your stuff ends up in
    'a different state depending on whether you had an error or not


    end sub



    izy
    Ill go ahead and move my on error statment to the begining, but I did remark it out here because no mater what kind of msg box i put in i cant put in a debug button, and that button helps me see exatly where my error is happening. Thanx for the suggestion.

    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •