error en sintaxis

[yohannita]

hola alguien m puede ayudar...... m marca 1 error de sintaxis cerca de la palabra and (negrita), no se q falte he estado checando parentesis y todo el show pero no se q pueda ser !!! alguien m puede echar la mano con la sintaxis???


If AdminTool.rd_comppending.Value = True Then
strSQL = " SELECT distinct(signature), department.name dept " & _
" From personnel, Department " & _
" Where dept_id = Department.id " & _
" AND department.name like '" & AdminTool.txt_trpdept.Text & "%' " & _
" AND active = 1 " & _
" AND signature not in ( SELECT distinct(signature) " & _
" From current_daily_entry, Department " & _
" Where user_dept_id = Department.id " & _
" AND week_id = " & txt_week.Text & " " & _
" aNd department.name like '" & AdminTool.txt_trpdept.Text & "%' " & _
" and completed = 1 ) " & _
" ORDER BY signature "



gracias

[GeoAvila]

Iniciado por yohannita

hola alguien m puede ayudar...... m marca 1 error de sintaxis cerca de la palabra and (negrita), no se q falte he estado checando parentesis y todo el show pero no se q pueda ser !!! alguien m puede echar la mano con la sintaxis???


If AdminTool.rd_comppending.Value = True Then
strSQL = " SELECT distinct(signature), department.name dept " & _
" From personnel, Department " & _
" Where dept_id = Department.id " & _
" AND department.name like '" & AdminTool.txt_trpdept.Text & "%' " & _
" AND active = 1 " & _
" AND signature not in ( SELECT distinct(signature) " & _
" From current_daily_entry, Department " & _
" Where user_dept_id = Department.id " & _
" AND week_id = " & txt_week.Text & " " & _
" aNd department.name like '" & AdminTool.txt_trpdept.Text & "%' " & _
" and completed = 1 ) " & _
" ORDER BY signature "



gracias

creo que debería ser asi.. en la linea anterior al error..

" AND week_id = " & txt_week.Text & _

y en la linea siguiente agregar un espacio antes del AND


nos vemos..

[yohannita]

no.... ya lo intente !!!!

[GeoAvila]

me corrió perfectamente... con estos parámetros

Código:

Dim Admintool As String
Dim txt_week As String
Admintool = "hola"
txt_week = "hola2"
Me.TextBox1.Text = " SELECT distinct(signature), department.name dept " & _
" From personnel, Department " & _
" Where dept_id = Department.id " & _
" AND department.name like '" & Admintool & "%' " & _
" AND active = 1 " & _
" AND signature not in ( SELECT distinct(signature) " & _
" From current_daily_entry, Department " & _
" Where user_dept_id = Department.id " & _
" AND week_id = " & txt_week & " " & _
" aNd department.name like '" & Admintool & "%' " & _
" and completed = 1 ) " & _
" ORDER BY signature " 

[yohannita]

este es parte del codigo..... m marca error de sintaxis cerca de la palabra and (negrita) y pone el error en la linea donde se conecta a la bd...


'limpiar lista de employees
lst_signatures.ColumnHeaders.clear
lst_signatures.ListItems.clear

lst_signatures.View = lvwReport

If AdminTool.rd_comppending.Value = True Then
strSQL = " SELECT distinct(signature), department.name dept " & _
" From personnel, Department " & _
" Where dept_id = Department.id " & _
" and department.name like '" & AdminTool.txt_trpdept.Text & "%' " & _
" and active = 1 " & _
" and signature not in ( SELECT distinct(signature) " & _
" From current_daily_entry, Department " & _
" Where user_dept_id = Department.id " & _
" and week_id = " & txt_week.Text & " " & _
" and department.name like '" & AdminTool.txt_trpdept.Text & "%' " & _
" and completed = 1 ) " & _
" ORDER BY signature "
Else


strSQL = " SELECT distinct(signature), department.name dept " & _
" FROM current_daily_entry, department " & _
" WHERE user_dept_id = department.id " & _
" and week_id = " & txt_week.Text & " " & _
" and department.name like '" & AdminTool.txt_trpdept.Text & "%' " & _
" and((completed = 1) and (approved = 0)) " & _
" ORDER BY signature "
End If

' Get Employee signature that have fill time report
Set rst = New ADODB.Recordset

rst.Open strSQL, de_TimeReportDB.cnn_TimeReportDB, adOpenStatic, adLockReadOnly, adCmdText

lst_signatures.ColumnHeaders.Add , , "Signature", lst_signatures.Width / 2
lst_signatures.ColumnHeaders.Add , , "Department", lst_signatures.Width / 2