Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2014
    Posts
    1

    Unanswered: Runtime Error 3075 MS Access VBA when running query

    I,m trrying to run this query via VBA code and im getting "Run -Time 3075 error" all the time. I tried to reorganize in diferent ways but its not working out. Its runs fine as a query, but when i try to run with a VBA code i ge a error msg. Maybe your fresh eyes might help me to structure this bad boy. Thanks in advance.

    Here is the original query:

    SELECT DISTINCT TB_Vendor.Vend_name, TB_POLine.ord_no, IIf(IsNull([cus_name]),StrConv([bill_to_name],3),StrConv([cus_name],3)) AS Customer, TB_POLine.user_def_fld_2, IIf(IsNumeric([CS_CLIENTOENTRY].[request_dt]) And IsNull([CS_CLIENTOENTRY1].[ord_dt]),DateSerial(Left$([CS_CLIENTOENTRY].[request_dt],4),Mid$([CS_CLIENTOENTRY].[request_dt],5,2),Right$([CS_CLIENTOENTRY].[request_dt],2)),IIf(IsNumeric([CS_CLIENTOENTRY1].[ord_dt]) And IsNull([CS_CLIENTOENTRY].[request_dt]),DateSerial(Left$([CS_CLIENTOENTRY1].[ord_dt],4),Mid$([CS_CLIENTOENTRY1].[ord_dt],5,2),Right$([CS_CLIENTOENTRY1].[ord_dt],2)),Null)) AS [Request Date], DateSerial(Left$([TB_POHeader].[ord_dt],4),Mid$([TB_POHeader].[ord_dt],5,2),Right$([TB_POHeader].[ord_dt],2)) AS [Order Date], IIf([TB_POLine].[promise_dt]<>0,DateSerial(Left$([TB_POLine].[promise_dt],4),Mid$([TB_POLine].[promise_dt],5,2),Right$([TB_POLine].[promise_dt],2)),Null) AS [Promise Date], TB_POHeader.user_name, TB_POHeader.ord_status, TB_POLine.move_to_loc, IIf([vchr_no]=0,"Received",IIf([vchr_no]>0,"Vouchered",IIf(DateDiff("d",Date(),[TB_POLine Query].[Pro Date])<=0,"Late","On Time"))) AS Status, TB_POHeader.cmt_2, TB_POHeader.cmt_3, TB_POHeader.cmt_1 FROM [TB_POLine Query] RIGHT JOIN (CS_Receivings RIGHT JOIN (CS_CLIENTOENTRY1 RIGHT JOIN (CS_CLIENTOENTRY RIGHT JOIN (TB_POHeader RIGHT JOIN (TB_Vendor RIGHT JOIN TB_POLine ON TB_Vendor.vend_no = TB_POLine.vend_no) ON TB_POHeader.ord_no = TB_POLine.ord_no) ON CS_CLIENTOENTRY.[Ticket #] = TB_POLine.user_def_fld_2) ON CS_CLIENTOENTRY1.[Ticket #] = TB_POLine.user_def_fld_2) ON CS_Receivings.ord_no = TB_POLine.ord_no) ON [TB_POLine Query].ord_no = TB_POLine.ord_no GROUP BY TB_Vendor.Vend_name, TB_POLine.ord_no, IIf(IsNull([cus_name]),StrConv([bill_to_name],3),StrConv([cus_name],3)), TB_POLine.user_def_fld_2, IIf(IsNumeric([CS_CLIENTOENTRY].[request_dt]) And IsNull([CS_CLIENTOENTRY1].[ord_dt]),DateSerial(Left$([CS_CLIENTOENTRY].[request_dt],4),Mid$([CS_CLIENTOENTRY].[request_dt],5,2),Right$([CS_CLIENTOENTRY].[request_dt],2)),IIf(IsNumeric([CS_CLIENTOENTRY1].[ord_dt]) And IsNull([CS_CLIENTOENTRY].[request_dt]),DateSerial(Left$([CS_CLIENTOENTRY1].[ord_dt],4),Mid$([CS_CLIENTOENTRY1].[ord_dt],5,2),Right$([CS_CLIENTOENTRY1].[ord_dt],2)),Null)), DateSerial(Left$([TB_POHeader].[ord_dt],4),Mid$([TB_POHeader].[ord_dt],5,2),Right$([TB_POHeader].[ord_dt],2)), IIf([TB_POLine].[promise_dt]<>0,DateSerial(Left$([TB_POLine].[promise_dt],4),Mid$([TB_POLine].[promise_dt],5,2),Right$([TB_POLine].[promise_dt],2)),Null), TB_POHeader.user_name, TB_POHeader.ord_status, TB_POLine.move_to_loc, IIf([vchr_no]=0,"Received",IIf([vchr_no]>0,"Vouchered",IIf(DateDiff("d",Date(),[TB_POLine Query].[Pro Date])<=0,"Late","On Time"))), TB_POHeader.cmt_2, TB_POHeader.cmt_3, TB_POHeader.cmt_1 HAVING (((DateSerial(Left$([TB_POHeader].[ord_dt],4),Mid$([TB_POHeader].[ord_dt],5,2),Right$([TB_POHeader].[ord_dt],2)))>#1/1/2014#) AND ((TB_POHeader.ord_status)<>"C")) ORDER BY TB_POLine.ord_no DESC;

    And VBA Code I'm using

    Private Sub Comando10_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim StrSql As String
    Dim cns As QueryDef
    Dim teste As String
    Dim teste2 As String

    Dim RstSql As QueryDef

    'Set RstSql = Application.CurrentDb.QueryDefs("Consulta1")
    Set db = CurrentDb()


    StrSql = "SELECT DISTINCT TB_Vendor.Vend_name, TB_POLine.ord_no, IIf(IsNull([cus_name]),StrConv([bill_to_name],3),StrConv([cus_name],3)) AS Customer, TB_POLine.user_def_fld_2, IIf(IsNumeric([CS_CLIENTOENTRY].[request_dt]) And, " & _
    "IsNull([CS_CLIENTOENTRY1].[ord_dt]),DateSerial(Left$([CS_CLIENTOENTRY].[request_dt],4),Mid$([CS_CLIENTOENTRY].[request_dt],5,2),Right$([CS_CLIENTOENTRY].[request_dt],2)),IIf(IsNumeric([CS_CLIENTOENTRY1].[ord_dt]) And, " & _
    "IsNull([CS_CLIENTOENTRY].[request_dt]),DateSerial(Left$([CS_CLIENTOENTRY1].[ord_dt],4),Mid$([CS_CLIENTOENTRY1].[ord_dt],5,2),Right$([CS_CLIENTOENTRY1].[ord_dt],2)),Null)), " & _
    "AS [Request Date], DateSerial(Left$([TB_POHeader].[ord_dt],4),Mid$([TB_POHeader].[ord_dt],5,2),Right$([TB_POHeader].[ord_dt],2)) AS [Order Date], " & _
    "IIf([TB_POLine].[promise_dt]<>0,DateSerial(Left$([TB_POLine].[promise_dt],4),Mid$([TB_POLine].[promise_dt],5,2),Right$([TB_POLine].[promise_dt],2)),Null) AS [Promise Date], TB_POHeader.user_name, TB_POHeader.ord_status, TB_POLine.move_to_loc, TB_POHeader.cmt_2, TB_POHeader.cmt_3, TB_POHeader.cmt_1, " & _
    "FROM TB_POLine Query] RIGHT JOIN (CS_Receivings RIGHT JOIN (CS_CLIENTOENTRY1 RIGHT JOIN (CS_CLIENTOENTRY RIGHT JOIN (TB_POHeader RIGHT JOIN (TB_Vendor RIGHT JOIN TB_POLine ON TB_Vendor.vend_no = TB_POLine.vend_no) ON TB_POHeader.ord_no = TB_POLine.ord_no) ON CS_CLIENTOENTRY.[Ticket #] = TB_POLine.user_def_fld_2) ON CS_CLIENTOENTRY1.[Ticket #] = TB_POLine.user_def_fld_2) ON CS_Receivings.ord_no = TB_POLine.ord_no) ON [TB_POLine Query].ord_no = TB_POLine.ord_no "


    If Not IsNull(Comb1.Value) Then
    teste = teste & " [CS_POStatus].[user_name] = '" & Comb1.Value & "'"
    End If


    If teste = " WHERE " Then
    teste = " WHERE TB_CS_POStatus.[ser_name]='Null';"
    Else
    teste = teste '& ";"

    End If

    Set rs = db.OpenRecordset(StrSql & teste)

    Set RstSql = Application.CurrentDb.QueryDefs("CS_POStatus")
    RstSql.SQL = StrSql & teste & " ORDER BY CS_POStatus.ord_no;"


    If teste = ";" Then
    MsgBox ("Please select at least one search criteria.")
    Else
    If rs.RecordCount = 0 Then
    MsgBox ("There are no results for this search")
    End If

    Lista8.Requery



    Me.Refresh


    End If

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    3075 is missing operator
    Immediate suspect I'd the comma after the and as in Ord_dt] and,
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Also comma before 'as'


    ...which suggest to me you arent entirely famillar with SQL syntax

    in these circumstances id suggest writing your sql line by line eg
    select
    acolumn as this,
    anothercolumn as that,
    yetanothercolumn
    from atable


    at least till youve shaken out your puctuation issues
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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