Please Help with syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to run the following sql from the vba behind a button on a form:

sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,
ActivityMeasure, BD, IntakeTargetDate ) " & _
"SELECT " & Me.PaperReqID & ", RequestTypeID, Activities, BD, " &
DateAdd("d", [tblkpActivity].[BD], Date) & "" & _
" FROM tblkpActivity" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"
db.Execute sql, dbFailOnError
The syntax problem I am having is with the dateadd expression. I get the
error message that jet engine can not find the field [!], I am thinking it is
[BD].

However, the following syntax works from the append query:
INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID, ActivityMeasure,
BD, IntakeTargetDate )
SELECT [Forms]![frmScorecardMain]![PaperReqID] AS PaperReqID,
tblkpActivity.RequestTypeID, tblkpActivity.Activities, tblkpActivity.BD,
DateAdd("d",[BD],Date()) AS IntakeTargetDate
FROM tblkpActivity;

I have tried tblkpActivity.BD on the form but if I write the expression as
follows I get type mismatch:
DateAdd("d", " & [BD] & ", Date) & ""

can some one show me the right syntax?
thanks
Al
 
Try this:

sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,
ActivityMeasure, BD, IntakeTargetDate ) " & _
"SELECT " & Me.PaperReqID & ", RequestTypeID, Activities, BD, " &
"DateAdd(""d"", [tblkpActivity].[BD], Date()) " & _
" FROM tblkpActivity" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"

If this also chokes, and you don't have any text strings being inserted via
the controls/fields/variables, then you may need to calculate the DateAdd
function's result and insert it instead of using the DateAdd function in the
SQL string.
 
Yes, it did choke. when I tried to chang it back to how I had it before and
it worked, it gave me error message "missing operator in query expression"
here is how I have it now

sql = "INSERT INTO tblScorecardActivity ( ReqTypeID, ActivityMeasure, BD,
PaperReqID ) " & _
"SELECT RequestTypeID, Activities, BD, " & Me.PaperReqID & "" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"

Ken Snell (MVP) said:
Try this:

sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,
ActivityMeasure, BD, IntakeTargetDate ) " & _
"SELECT " & Me.PaperReqID & ", RequestTypeID, Activities, BD, " &
"DateAdd(""d"", [tblkpActivity].[BD], Date()) " & _
" FROM tblkpActivity" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"

If this also chokes, and you don't have any text strings being inserted via
the controls/fields/variables, then you may need to calculate the DateAdd
function's result and insert it instead of using the DateAdd function in the
SQL string.
--

Ken Snell
<MS ACCESS MVP>



Al said:
I am trying to run the following sql from the vba behind a button on a
form:

sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,
ActivityMeasure, BD, IntakeTargetDate ) " & _
"SELECT " & Me.PaperReqID & ", RequestTypeID, Activities, BD, " &
DateAdd("d", [tblkpActivity].[BD], Date) & "" & _
" FROM tblkpActivity" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"
db.Execute sql, dbFailOnError
The syntax problem I am having is with the dateadd expression. I get the
error message that jet engine can not find the field [!], I am thinking it
is
[BD].

However, the following syntax works from the append query:
INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID, ActivityMeasure,
BD, IntakeTargetDate )
SELECT [Forms]![frmScorecardMain]![PaperReqID] AS PaperReqID,
tblkpActivity.RequestTypeID, tblkpActivity.Activities, tblkpActivity.BD,
DateAdd("d",[BD],Date()) AS IntakeTargetDate
FROM tblkpActivity;

I have tried tblkpActivity.BD on the form but if I write the expression as
follows I get type mismatch:
DateAdd("d", " & [BD] & ", Date) & ""

can some one show me the right syntax?
thanks
Al
Click to expand...
Click to expand...
 
"SELECT RequestTypeID, Activities, BD, " & Me.PaperReqID

What's this??: BD, " & Me.PaperReqID

Shouldn't that be: BD = " & Me.PaperReqID ??



Al said:
Yes, it did choke. when I tried to chang it back to how I had it before and
it worked, it gave me error message "missing operator in query expression"
here is how I have it now

sql = "INSERT INTO tblScorecardActivity ( ReqTypeID, ActivityMeasure, BD,
PaperReqID ) " & _
"SELECT RequestTypeID, Activities, BD, " & Me.PaperReqID & "" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"

Ken Snell (MVP) said:
Try this:

sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,
ActivityMeasure, BD, IntakeTargetDate ) " & _
"SELECT " & Me.PaperReqID & ", RequestTypeID, Activities, BD, " &
"DateAdd(""d"", [tblkpActivity].[BD], Date()) " & _
" FROM tblkpActivity" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"

If this also chokes, and you don't have any text strings being inserted via
the controls/fields/variables, then you may need to calculate the DateAdd
function's result and insert it instead of using the DateAdd function in the
SQL string.
--

Ken Snell
<MS ACCESS MVP>



Al said:
I am trying to run the following sql from the vba behind a button on a
form:

sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,
ActivityMeasure, BD, IntakeTargetDate ) " & _
"SELECT " & Me.PaperReqID & ", RequestTypeID, Activities, BD, " &
DateAdd("d", [tblkpActivity].[BD], Date) & "" & _
" FROM tblkpActivity" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"
db.Execute sql, dbFailOnError
The syntax problem I am having is with the dateadd expression. I get the
error message that jet engine can not find the field [!], I am thinking it
is
[BD].

However, the following syntax works from the append query:
INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID, ActivityMeasure,
BD, IntakeTargetDate )
SELECT [Forms]![frmScorecardMain]![PaperReqID] AS PaperReqID,
tblkpActivity.RequestTypeID, tblkpActivity.Activities, tblkpActivity.BD,
DateAdd("d",[BD],Date()) AS IntakeTargetDate
FROM tblkpActivity;

I have tried tblkpActivity.BD on the form but if I write the expression as
follows I get type mismatch:
DateAdd("d", " & [BD] & ", Date) & ""

can some one show me the right syntax?
thanks
Al
Click to expand...
Click to expand...
Click to expand...
 
OK - then we'll need to get the value of BD from your source table:

sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,
ActivityMeasure, BD, IntakeTargetDate ) " & _
"SELECT " & Me.PaperReqID & ", RequestTypeID, Activities, BD, #" & _
Format(DateAdd("d", DLookup("BD", "tblkpActivity", _
ReqTypeID ='" & Me.ReqTypeID & "'"), Date()), "mm\/dd\/yyyy") & "# & _
" FROM tblkpActivity" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"
--

Ken Snell
<MS ACCESS MVP>


Al said:
Yes, it did choke. when I tried to chang it back to how I had it before
and
it worked, it gave me error message "missing operator in query expression"
here is how I have it now

sql = "INSERT INTO tblScorecardActivity ( ReqTypeID, ActivityMeasure, BD,
PaperReqID ) " & _
"SELECT RequestTypeID, Activities, BD, " & Me.PaperReqID & "" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"

Ken Snell (MVP) said:
Try this:

sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,
ActivityMeasure, BD, IntakeTargetDate ) " & _
"SELECT " & Me.PaperReqID & ", RequestTypeID, Activities, BD, " &
"DateAdd(""d"", [tblkpActivity].[BD], Date()) " & _
" FROM tblkpActivity" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"

If this also chokes, and you don't have any text strings being inserted
via
the controls/fields/variables, then you may need to calculate the DateAdd
function's result and insert it instead of using the DateAdd function in
the
SQL string.
--

Ken Snell
<MS ACCESS MVP>



Al said:
I am trying to run the following sql from the vba behind a button on a
form:

sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,
ActivityMeasure, BD, IntakeTargetDate ) " & _
"SELECT " & Me.PaperReqID & ", RequestTypeID, Activities, BD, " &
DateAdd("d", [tblkpActivity].[BD], Date) & "" & _
" FROM tblkpActivity" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"
db.Execute sql, dbFailOnError
The syntax problem I am having is with the dateadd expression. I get
the
error message that jet engine can not find the field [!], I am thinking
it
is
[BD].

However, the following syntax works from the append query:
INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,
ActivityMeasure,
BD, IntakeTargetDate )
SELECT [Forms]![frmScorecardMain]![PaperReqID] AS PaperReqID,
tblkpActivity.RequestTypeID, tblkpActivity.Activities,
tblkpActivity.BD,
DateAdd("d",[BD],Date()) AS IntakeTargetDate
FROM tblkpActivity;

I have tried tblkpActivity.BD on the form but if I write the expression
as
follows I get type mismatch:
DateAdd("d", " & [BD] & ", Date) & ""

can some one show me the right syntax?
thanks
Al
Click to expand...
Click to expand...
Click to expand...
 
Ken,
thank you very much the previous one you sent worked here it is:
sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID," & _
"ActivityMeasure, BD, IntakeTargetDate ) " & _
"SELECT " & Me.PaperReqID & ", RequestTypeID, Activities, BD, " & _
"DateAdd(""d"", [tblkpActivity].[BD], Date()) " & _
" FROM tblkpActivity" & _
" WHERE RequestTypeID ='" & Me.ReqTypeID & "'"

Al
Ken Snell (MVP) said:
OK - then we'll need to get the value of BD from your source table:

sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,
ActivityMeasure, BD, IntakeTargetDate ) " & _
"SELECT " & Me.PaperReqID & ", RequestTypeID, Activities, BD, #" & _
Format(DateAdd("d", DLookup("BD", "tblkpActivity", _
ReqTypeID ='" & Me.ReqTypeID & "'"), Date()), "mm\/dd\/yyyy") & "# & _
" FROM tblkpActivity" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"
--

Ken Snell
<MS ACCESS MVP>


Al said:
Yes, it did choke. when I tried to chang it back to how I had it before
and
it worked, it gave me error message "missing operator in query expression"
here is how I have it now

sql = "INSERT INTO tblScorecardActivity ( ReqTypeID, ActivityMeasure, BD,
PaperReqID ) " & _
"SELECT RequestTypeID, Activities, BD, " & Me.PaperReqID & "" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"

Ken Snell (MVP) said:
Try this:

sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,
ActivityMeasure, BD, IntakeTargetDate ) " & _
"SELECT " & Me.PaperReqID & ", RequestTypeID, Activities, BD, " &
"DateAdd(""d"", [tblkpActivity].[BD], Date()) " & _
" FROM tblkpActivity" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"

If this also chokes, and you don't have any text strings being inserted
via
the controls/fields/variables, then you may need to calculate the DateAdd
function's result and insert it instead of using the DateAdd function in
the
SQL string.
--

Ken Snell
<MS ACCESS MVP>



I am trying to run the following sql from the vba behind a button on a
form:

sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,
ActivityMeasure, BD, IntakeTargetDate ) " & _
"SELECT " & Me.PaperReqID & ", RequestTypeID, Activities, BD, " &
DateAdd("d", [tblkpActivity].[BD], Date) & "" & _
" FROM tblkpActivity" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"
db.Execute sql, dbFailOnError
The syntax problem I am having is with the dateadd expression. I get
the
error message that jet engine can not find the field [!], I am thinking
it
is
[BD].

However, the following syntax works from the append query:
INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,
ActivityMeasure,
BD, IntakeTargetDate )
SELECT [Forms]![frmScorecardMain]![PaperReqID] AS PaperReqID,
tblkpActivity.RequestTypeID, tblkpActivity.Activities,
tblkpActivity.BD,
DateAdd("d",[BD],Date()) AS IntakeTargetDate
FROM tblkpActivity;

I have tried tblkpActivity.BD on the form but if I write the expression
as
follows I get type mismatch:
DateAdd("d", " & [BD] & ", Date) & ""

can some one show me the right syntax?
thanks
Al
Click to expand...
Click to expand...
Click to expand...
 
Hi Dennis,
this is a part of the SELECT statment and will be parsed as the field name.
This is not a where somthing = somthing.
Al

Dennis said:
"SELECT RequestTypeID, Activities, BD, " & Me.PaperReqID

What's this??: BD, " & Me.PaperReqID

Shouldn't that be: BD = " & Me.PaperReqID ??



Al said:
Yes, it did choke. when I tried to chang it back to how I had it before and
it worked, it gave me error message "missing operator in query expression"
here is how I have it now

sql = "INSERT INTO tblScorecardActivity ( ReqTypeID, ActivityMeasure, BD,
PaperReqID ) " & _
"SELECT RequestTypeID, Activities, BD, " & Me.PaperReqID & "" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"

Ken Snell (MVP) said:
Try this:

sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,
ActivityMeasure, BD, IntakeTargetDate ) " & _
"SELECT " & Me.PaperReqID & ", RequestTypeID, Activities, BD, " &
"DateAdd(""d"", [tblkpActivity].[BD], Date()) " & _
" FROM tblkpActivity" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"

If this also chokes, and you don't have any text strings being inserted via
the controls/fields/variables, then you may need to calculate the DateAdd
function's result and insert it instead of using the DateAdd function in the
SQL string.
--

Ken Snell
<MS ACCESS MVP>



I am trying to run the following sql from the vba behind a button on a
form:

sql = "INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID,
ActivityMeasure, BD, IntakeTargetDate ) " & _
"SELECT " & Me.PaperReqID & ", RequestTypeID, Activities, BD, " &
DateAdd("d", [tblkpActivity].[BD], Date) & "" & _
" FROM tblkpActivity" & _
" WHERE ReqTypeID ='" & Me.ReqTypeID & "'"
db.Execute sql, dbFailOnError
The syntax problem I am having is with the dateadd expression. I get the
error message that jet engine can not find the field [!], I am thinking it
is
[BD].

However, the following syntax works from the append query:
INSERT INTO tblScorecardActivity ( PaperReqID, ReqTypeID, ActivityMeasure,
BD, IntakeTargetDate )
SELECT [Forms]![frmScorecardMain]![PaperReqID] AS PaperReqID,
tblkpActivity.RequestTypeID, tblkpActivity.Activities, tblkpActivity.BD,
DateAdd("d",[BD],Date()) AS IntakeTargetDate
FROM tblkpActivity;

I have tried tblkpActivity.BD on the form but if I write the expression as
follows I get type mismatch:
DateAdd("d", " & [BD] & ", Date) & ""

can some one show me the right syntax?
thanks
Al
Click to expand...
Click to expand...
Click to expand...
 
Back
Top