What am I doing wrong?

  • Thread starter Thread starter Gina Whipp
  • Start date Start date
G

Gina Whipp

Posted earlier in queries forum hoping I get an answer here...

This is my third StrSQL and I can't for the life of me figure out why this
won't run. Any help would be appreciated...

RecDetect = DLookup("[AssociateID]", "tblAssociateActivities",
"[AssociateID] = " & Me![cboAssociateID] & " AND [ActivityDate] = #" &
Format(TDate, "mm/dd/yy") & "#")


If IsNull(RecDetect) Then
StrSQL = "INSERT INTO tblAssociateActivities ( AssociateID,
ActivityDate, ActivityID, TimeDecimal ) " _
& "SELECT " & Me![cboAssociateID] & " AS F1, #" _
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3 " _
& 8 " AS TimeDecimal;"
DoCmd.RunSQL StrSQL
Else
StrSQL = "UPDATE tblAssociateActivities SET
tblAssociateActivities.ActivityID = " _
& TypeAttend & tblAssociateActivities.TimeDecimal = 8 _
& " WHERE (((tblAssociateActivities.AssociateID)=" & Me![cboAssociateID]
& ") AND" _
& "((tblAssociateActivities.ActivityDate)=#" & Format(TDate, "mm/dd/yy")
& "#));"
DoCmd.RunSQL StrSQL
End If

As always THANKS!
 
I'm not surprised you got no answer!

What do you mean, "it won't run"?

Are you getting an error message?

If so, what is the message?

What line does it occur on?

TC
 
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3 " _
& 8 " AS TimeDecimal;"
Click to expand...

Try Me.TypeAttend (I'm guessing this is a control on the form). Also, there is no comma
between F3 and 8.
tblAssociateActivities.ActivityID = " _
& TypeAttend & tblAssociateActivities.TimeDecimal = 8 _
Click to expand...

Are you trying to update 2 fields here? What you currently have is a concatenation of
TypeAttend with either True or False.
& ") AND" _
& "((tblAssociateActivities.ActivityDate)=#" & Format(TDate, "mm/dd/yy")
Click to expand...

There is no space between the word AND and ((tblAsso.......

--
Wayne Morgan
Microsoft Access MVP


Gina Whipp said:
Posted earlier in queries forum hoping I get an answer here...

This is my third StrSQL and I can't for the life of me figure out why this
won't run. Any help would be appreciated...

RecDetect = DLookup("[AssociateID]", "tblAssociateActivities",
"[AssociateID] = " & Me![cboAssociateID] & " AND [ActivityDate] = #" &
Format(TDate, "mm/dd/yy") & "#")


If IsNull(RecDetect) Then
StrSQL = "INSERT INTO tblAssociateActivities ( AssociateID,
ActivityDate, ActivityID, TimeDecimal ) " _
& "SELECT " & Me![cboAssociateID] & " AS F1, #" _
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3 " _
& 8 " AS TimeDecimal;"
DoCmd.RunSQL StrSQL
Else
StrSQL = "UPDATE tblAssociateActivities SET
tblAssociateActivities.ActivityID = " _
& TypeAttend & tblAssociateActivities.TimeDecimal = 8 _
& " WHERE (((tblAssociateActivities.AssociateID)=" & Me![cboAssociateID]
& ") AND" _
& "((tblAssociateActivities.ActivityDate)=#" & Format(TDate, "mm/dd/yy")
& "#));"
DoCmd.RunSQL StrSQL
End If

As always THANKS!
Click to expand...
 
Ok TC sorry about that I'm a little slow sometimes... I'm trying to update
4 fields in a table and it worked fine till I added the fourth field then
the code turns red and errors out won't even let me test it. I'm going to
try the comments from Wayne and see if that's my problem.
 
Wayne added your comments but the below is two fields.

& TypeAttend & tblAssociateActivities.TimeDecimal = 8 _

Also TypeAttend is not on the form and actually works, its calling a
DLookUp... I didn't want to print the whole code, it's kind of long...


Wayne Morgan said:
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3 " _
& 8 " AS TimeDecimal;"
Click to expand...

Try Me.TypeAttend (I'm guessing this is a control on the form). Also, there is no comma
between F3 and 8.
tblAssociateActivities.ActivityID = " _
& TypeAttend & tblAssociateActivities.TimeDecimal = 8 _
Click to expand...

Are you trying to update 2 fields here? What you currently have is a concatenation of
TypeAttend with either True or False.
& ") AND" _
& "((tblAssociateActivities.ActivityDate)=#" & Format(TDate,
Click to expand...
"mm/dd/yy")

There is no space between the word AND and ((tblAsso.......

--
Wayne Morgan
Microsoft Access MVP


Gina Whipp said:
Posted earlier in queries forum hoping I get an answer here...

This is my third StrSQL and I can't for the life of me figure out why this
won't run. Any help would be appreciated...

RecDetect = DLookup("[AssociateID]", "tblAssociateActivities",
"[AssociateID] = " & Me![cboAssociateID] & " AND [ActivityDate] = #" &
Format(TDate, "mm/dd/yy") & "#")


If IsNull(RecDetect) Then
StrSQL = "INSERT INTO tblAssociateActivities ( AssociateID,
ActivityDate, ActivityID, TimeDecimal ) " _
& "SELECT " & Me![cboAssociateID] & " AS F1, #" _
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3 " _
& 8 " AS TimeDecimal;"
DoCmd.RunSQL StrSQL
Else
StrSQL = "UPDATE tblAssociateActivities SET
tblAssociateActivities.ActivityID = " _
& TypeAttend & tblAssociateActivities.TimeDecimal = 8 _
& " WHERE (((tblAssociateActivities.AssociateID)=" & Me![cboAssociateID]
& ") AND" _
& "((tblAssociateActivities.ActivityDate)=#" & Format(TDate, "mm/dd/yy")
& "#));"
DoCmd.RunSQL StrSQL
End If

As always THANKS!
Click to expand...
Click to expand...
 
Okie Dokie I got he first part to run but the second part after 'Else' is
erroring out on Can't find tblAssociateActivites...

&tblAssociateActivities.TimeDecimal = " & 8" _


If IsNull(RecDetect) Then
StrSQL = "INSERT INTO tblAssociateActivities ( AssociateID,
ActivityDate, ActivityID ) " _
& "SELECT " & Me![cboAssociateID] & " AS F1, #" _
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3, " & 8
& " AS F4;"
DoCmd.RunSQL StrSQL
Else
StrSQL = "UPDATE tblAssociateActivities SET
tblAssociateActivities.ActivityID = " & TypeAttend _
& tblAssociateActivites.TimeDecimal = " & 8" _
& " WHERE (((tblAssociateActivities.AssociateID)=" & Me![cboAssociateID]
& ") AND" _
& "((tblAssociateActivities.ActivityDate)=#" & Format(TDate, "mm/dd/yy")
& "#));"
DoCmd.RunSQL StrSQL
 
Try using Debug.print strSQL or MsgBox strSQL in key places to check
your syntax.

Nancy L



Okie Dokie I got he first part to run but the second part after 'Else' is
erroring out on Can't find tblAssociateActivites...

&tblAssociateActivities.TimeDecimal = " & 8" _


If IsNull(RecDetect) Then
StrSQL = "INSERT INTO tblAssociateActivities ( AssociateID,
ActivityDate, ActivityID ) " _
& "SELECT " & Me![cboAssociateID] & " AS F1, #" _
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3, " & 8
& " AS F4;"
DoCmd.RunSQL StrSQL
Else
StrSQL = "UPDATE tblAssociateActivities SET
tblAssociateActivities.ActivityID = " & TypeAttend _
& tblAssociateActivites.TimeDecimal = " & 8" _
& " WHERE (((tblAssociateActivities.AssociateID)=" & Me![cboAssociateID]
& ") AND" _
& "((tblAssociateActivities.ActivityDate)=#" & Format(TDate, "mm/dd/yy")
& "#));"
DoCmd.RunSQL StrSQL

Gina Whipp said:
Posted earlier in queries forum hoping I get an answer here...

This is my third StrSQL and I can't for the life of me figure out why this
won't run. Any help would be appreciated...

RecDetect = DLookup("[AssociateID]", "tblAssociateActivities",
"[AssociateID] = " & Me![cboAssociateID] & " AND [ActivityDate] = #" &
Format(TDate, "mm/dd/yy") & "#")


If IsNull(RecDetect) Then
StrSQL = "INSERT INTO tblAssociateActivities ( AssociateID,
ActivityDate, ActivityID, TimeDecimal ) " _
& "SELECT " & Me![cboAssociateID] & " AS F1, #" _
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3 " _
& 8 " AS TimeDecimal;"
DoCmd.RunSQL StrSQL
Else
StrSQL = "UPDATE tblAssociateActivities SET
tblAssociateActivities.ActivityID = " _
& TypeAttend & tblAssociateActivities.TimeDecimal = 8 _
& " WHERE (((tblAssociateActivities.AssociateID)=" & Me![cboAssociateID]
& ") AND" _
& "((tblAssociateActivities.ActivityDate)=#" & Format(TDate, "mm/dd/yy")
& "#));"
DoCmd.RunSQL StrSQL
End If

As always THANKS!
Click to expand...
Click to expand...
 
One thing you might try is to either use Debug.Print strSQL or MsgBox
strSQL at key points so you can see if you have all the correct
spaces, comma's , quote marks, etc.

Nancy L.

Okie Dokie I got he first part to run but the second part after 'Else' is
erroring out on Can't find tblAssociateActivites...

&tblAssociateActivities.TimeDecimal = " & 8" _


If IsNull(RecDetect) Then
StrSQL = "INSERT INTO tblAssociateActivities ( AssociateID,
ActivityDate, ActivityID ) " _
& "SELECT " & Me![cboAssociateID] & " AS F1, #" _
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3, " & 8
& " AS F4;"
DoCmd.RunSQL StrSQL
Else
StrSQL = "UPDATE tblAssociateActivities SET
tblAssociateActivities.ActivityID = " & TypeAttend _
& tblAssociateActivites.TimeDecimal = " & 8" _
& " WHERE (((tblAssociateActivities.AssociateID)=" & Me![cboAssociateID]
& ") AND" _
& "((tblAssociateActivities.ActivityDate)=#" & Format(TDate, "mm/dd/yy")
& "#));"
DoCmd.RunSQL StrSQL

Gina Whipp said:
Posted earlier in queries forum hoping I get an answer here...

This is my third StrSQL and I can't for the life of me figure out why this
won't run. Any help would be appreciated...

RecDetect = DLookup("[AssociateID]", "tblAssociateActivities",
"[AssociateID] = " & Me![cboAssociateID] & " AND [ActivityDate] = #" &
Format(TDate, "mm/dd/yy") & "#")


If IsNull(RecDetect) Then
StrSQL = "INSERT INTO tblAssociateActivities ( AssociateID,
ActivityDate, ActivityID, TimeDecimal ) " _
& "SELECT " & Me![cboAssociateID] & " AS F1, #" _
& Format(TDate, "mm/dd/yy") & "# AS F2, " & TypeAttend & " AS F3 " _
& 8 " AS TimeDecimal;"
DoCmd.RunSQL StrSQL
Else
StrSQL = "UPDATE tblAssociateActivities SET
tblAssociateActivities.ActivityID = " _
& TypeAttend & tblAssociateActivities.TimeDecimal = 8 _
& " WHERE (((tblAssociateActivities.AssociateID)=" & Me![cboAssociateID]
& ") AND" _
& "((tblAssociateActivities.ActivityDate)=#" & Format(TDate, "mm/dd/yy")
& "#));"
DoCmd.RunSQL StrSQL
End If

As always THANKS!
Click to expand...
Click to expand...
 
Back
Top