SQL Insert Into Error

[shifty shaker]

I've modified sql text and now i'm getting a syntax error that I cannot
figure out....anyone?
error occurs at the Insert Into line but the entire function is given in
case you need it.
---
Function ThisIs()
Dim TDate As Date, C1 As Integer, StrSQL As String, TypeAttend, RecDetect
If Not IsNull(Me.scrStudent) Then
C1 = 1: TDate = Me![scr1Date]

Do Until C1 = CInt(Mid(ActiveControl.Name, 3, 2))
TDate = DateAdd("d", 1, TDate)
C1 = C1 + 1
Loop

TypeAttend = DLookup("AttType", "Attend", "[AttStudent] = " &
Me![scrStudent] & " AND [AttDate] = #" & Format(TDate, "mm/dd/yy") & "#")
If IsNull(TypeAttend) Then
TypeAttend = 0
End If
TypeAttend = TypeAttend + 1
If TypeAttend > 4 Then
TypeAttend = 0
End If
If TypeAttend = 3 Then
Dim TradeName
Me.cboTradeWith.Visible = True
TradeName = Me.cboTradeWith
End If
DoCmd.SetWarnings False
RecDetect = DLookup("[scrStudent]", "Attend", "[AttStudent] = " &
Me![scrStudent] & " AND [AttDate] = #" & Format(TDate, "mm/dd/yy") & "#")
If IsNull(RecDetect) Then
StrSQL = "INSERT INTO Attend ( AttStudent, AttDate, AttType, tradewith )
" _
& "SELECT " & Me![scrStudent] & " AS F1, #" _
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3, " &
TradeName & " AS F4; "
DoCmd.RunSQL StrSQL
Else
StrSQL = "UPDATE Attend SET Attend.AttType = " & TypeAttend _
& " WHERE (((Attend.AttStudent)=" & Me![scrStudent] & ") AND" _
& "((Attend.AttDate)=#" & Format(TDate, "mm/dd/yy") & "#));"
DoCmd.RunSQL StrSQL
End If
DoCmd.SetWarnings True
Call RefDates
End If
End Function

[shifty shaker]

Anyone good with SQL..I'm still stuck. I've been at it for 6 hours this
morning
"shifty shaker" <SS***@pioneers.net> wrote in message
news:cb****************@news02.roc.ny...

I've modified sql text and now i'm getting a syntax error that I cannot
figure out....anyone?
error occurs at the Insert Into line but the entire function is given in
case you need it.
---
Function ThisIs()
Dim TDate As Date, C1 As Integer, StrSQL As String, TypeAttend, RecDetect
If Not IsNull(Me.scrStudent) Then
C1 = 1: TDate = Me![scr1Date]

Do Until C1 = CInt(Mid(ActiveControl.Name, 3, 2))
TDate = DateAdd("d", 1, TDate)
C1 = C1 + 1
Loop

TypeAttend = DLookup("AttType", "Attend", "[AttStudent] = " &
Me![scrStudent] & " AND [AttDate] = #" & Format(TDate, "mm/dd/yy") & "#")
If IsNull(TypeAttend) Then
TypeAttend = 0
End If
TypeAttend = TypeAttend + 1
If TypeAttend > 4 Then
TypeAttend = 0
End If
If TypeAttend = 3 Then
Dim TradeName
Me.cboTradeWith.Visible = True
TradeName = Me.cboTradeWith
End If
DoCmd.SetWarnings False
RecDetect = DLookup("[scrStudent]", "Attend", "[AttStudent] = " &
Me![scrStudent] & " AND [AttDate] = #" & Format(TDate, "mm/dd/yy") & "#")
If IsNull(RecDetect) Then
StrSQL = "INSERT INTO Attend ( AttStudent, AttDate, AttType,
tradewith ) " _
& "SELECT " & Me![scrStudent] & " AS F1, #" _
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3, " &
TradeName & " AS F4; "
DoCmd.RunSQL StrSQL
Else
StrSQL = "UPDATE Attend SET Attend.AttType = " & TypeAttend _
& " WHERE (((Attend.AttStudent)=" & Me![scrStudent] & ") AND" _
& "((Attend.AttDate)=#" & Format(TDate, "mm/dd/yy") & "#));"
DoCmd.RunSQL StrSQL
End If
DoCmd.SetWarnings True
Call RefDates
End If
End Function

[Lyle Fairfield]

My screen shows
)SELECT

) no space SELECT
rather than
) space SELECT

Is this the problem? I don't know. You could check.

I always do a debug.print of dynamic SQL during devlopment so that I
can see that what I intended to say is not actually what I said. If
it''s not doing what I want I paste it into the query wizard and mess
with it there until I get it the way I want it. Then I bring that back
to VBA as a model and mess with my code until it gives me the model.

I also never use object values (read Form Controls) default values
directly in an SQL string. I create variables of the correct type, fill
them with values from the Forms Controls, run some checking of the
values or what ifs (e.g, what if the control is empty, what if we check
treat a combo box column as a number and actually its null etc).

[Mike Preston]

On Sun, 18 Dec 2005 17:43:20 GMT, "shifty shaker" <SS***@pioneers.net>
wrote:

Anyone good with SQL..I'm still stuck. I've been at it for 6 hours this
morning
"shifty shaker" <SS***@pioneers.net> wrote in message
news:cb****************@news02.roc.ny...

I've modified sql text and now i'm getting a syntax error that I cannot
figure out....anyone?
error occurs at the Insert Into line but the entire function is given in
case you need it.
---
Function ThisIs()
Dim TDate As Date, C1 As Integer, StrSQL As String, TypeAttend, RecDetect
TypeAttend and RecDetect are being dimensioned as variants. Is that
what you want? If not, make them what you want them to be.
If Not IsNull(Me.scrStudent) Then
C1 = 1: TDate = Me![scr1Date]

Do Until C1 = CInt(Mid(ActiveControl.Name, 3, 2))
TDate = DateAdd("d", 1, TDate)
C1 = C1 + 1
Loop

TypeAttend = DLookup("AttType", "Attend", "[AttStudent] = " &
Me![scrStudent] & " AND [AttDate] = #" & Format(TDate, "mm/dd/yy") & "#")
If IsNull(TypeAttend) Then
TypeAttend = 0
End If
TypeAttend = TypeAttend + 1
If TypeAttend > 4 Then
TypeAttend = 0
End If
If TypeAttend = 3 Then
Dim TradeName
The above dimensions TradeName as a variant. I think it is better
practice to:

Dim Tradename as string
Me.cboTradeWith.Visible = True
TradeName = Me.cboTradeWith
End If
DoCmd.SetWarnings False
RecDetect = DLookup("[scrStudent]", "Attend", "[AttStudent] = " &
Me![scrStudent] & " AND [AttDate] = #" & Format(TDate, "mm/dd/yy") & "#")
If IsNull(RecDetect) Then
StrSQL = "INSERT INTO Attend ( AttStudent, AttDate, AttType,
tradewith ) " _
& "SELECT " & Me![scrStudent] & " AS F1, #" _
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3, " &
TradeName & " AS F4; "
Sure does look to me like TradeName isn't a number. As such,
shouldn't it be enlosed within quotes?

It also looks like the only time you actually set TradeName is when
TypeAttend = 3 (see above).

Could that be the problem?

Do what Lyle says. Familiarize yourself with the debugging window.

mike
DoCmd.RunSQL StrSQL
Else
StrSQL = "UPDATE Attend SET Attend.AttType = " & TypeAttend _
& " WHERE (((Attend.AttStudent)=" & Me![scrStudent] & ") AND" _
& "((Attend.AttDate)=#" & Format(TDate, "mm/dd/yy") & "#));"
DoCmd.RunSQL StrSQL
End If
DoCmd.SetWarnings True
Call RefDates
End If
End Function