Please Help with syntax

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
 
K

Ken Snell \(MVP\)

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

Guest

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

Guest

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

Ken Snell \(MVP\)

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

Guest

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

Guest

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

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

Please help with sql. 2
Syntax (Missing Operator) in Query Expr .... 2
Help with syntax error 1
AVB Code - Please help with SQL syntax 1
Need help w/ syntax! 3
Can't get sql to work. Please help. 2
Help with syntax 4
SQL Syntax help 6

Top