What am I doing wrong?

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!
 
T

TC

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
 
W

Wayne Morgan

& 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...
 
G

Gina Whipp

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.
 
G

Gina Whipp

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...
 
G

Gina Whipp

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
 
N

Nancy Lytle

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...
 
N

Nancy Lytle

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

What am I doing wrong 1
Annual Calendar 1
conversion to SQL backend 13
What am I doing wrong? 5
What am I missing here 2
What am I doing wrong??? I'm getting a headache! 8
Help With Macro - Cannot figure out what I am doing wrong 6
I am using a Sub Mail_ActiveSheet_Outlook() code in VB 2

Top