Need to chg queried table

B

Bonnie

Hi there! Using A02 on XP. Not a programmer but love learning this. Have a
form with a record showing the contract number in a text field [RunThisOne].
First button imports a file into a table and names it
[Forms]![fCensus1Conversion]![RunThisOne]&"Copy". So my new table would be
named GP0013Copy. A few of the records may have a [Status Code] of "T"
(Terminated). I've created a DELETE query to remove the terminated records
from the table GP0013Copy before exporting again. Now I need to create some
VB to change my source for the query. I may have contract number GP0013 or
GP1992, etc. on the form. I need to delete the [Status Code] = T records in
the table named for the contract number on my form. I've altered some code
given to me for a project something like this. But, I'm getting an error:
Run-time error '3075': Syntax error (missing operator) in query expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013CopyWITH OWNERACCESS OPTION;'

Private Sub Command39_Click()

Dim strSQL As String
Dim dbsCurrent As Database
Dim qryClean As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

strSQL = strSQL & "IIf((" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Copy![Status Code])= 'T', "

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy.[Status
Code], "

strSQL = strSQL & " FROM "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy"
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

'MsgBox strSQL

'the name of your query goes in here
Set qryClean = dbsCurrent.QueryDefs("qClearTermsCensus1")

qryClean.SQL = strSQL

Set dbsCurrent = Nothing
Set qryClean = Nothing

End Sub

It seems like it should work but I'm sure I missed something. I would
appreciate any help or advice. Thanks!
 
6

'69 Camaro

Hi, Bonnie.

It has a typo. Change the following line of code:

strSQL = strSQL & "WITH OWNERACCESS OPTION;"

to:

strSQL = strSQL & " WITH OWNERACCESS OPTION;"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
D

Douglas J Steele

- the IIf statement doesn't have a closing parenthesis
- the comma between [Status Code] and FROM shouldn't be there

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bonnie said:
Hi Gunny,

Fixed my typo and just get the same error but with a space in it. Here it is:

Run-time error '3075': Syntax error (missing operator) in query expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013Copy WITH OWNERACCESS OPTION;'

The debugger highlights the line: qryClean.SQL = strSQL

Any ideas? Thanks in advance for your time. I really appreciate the help.
--
Bonnie


'69 Camaro said:
Hi, Bonnie.

It has a typo. Change the following line of code:

strSQL = strSQL & "WITH OWNERACCESS OPTION;"

to:

strSQL = strSQL & " WITH OWNERACCESS OPTION;"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Bonnie said:
Hi there! Using A02 on XP. Not a programmer but love learning this. Have a
form with a record showing the contract number in a text field [RunThisOne].
First button imports a file into a table and names it
[Forms]![fCensus1Conversion]![RunThisOne]&"Copy". So my new table would be
named GP0013Copy. A few of the records may have a [Status Code] of "T"
(Terminated). I've created a DELETE query to remove the terminated records
from the table GP0013Copy before exporting again. Now I need to create some
VB to change my source for the query. I may have contract number GP0013 or
GP1992, etc. on the form. I need to delete the [Status Code] = T records in
the table named for the contract number on my form. I've altered some code
given to me for a project something like this. But, I'm getting an error:
Run-time error '3075': Syntax error (missing operator) in query expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013CopyWITH OWNERACCESS OPTION;'

Private Sub Command39_Click()

Dim strSQL As String
Dim dbsCurrent As Database
Dim qryClean As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

strSQL = strSQL & "IIf((" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Copy![Status Code])= 'T', "

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy.[Status
Code], "

strSQL = strSQL & " FROM "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy"
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

'MsgBox strSQL

'the name of your query goes in here
Set qryClean = dbsCurrent.QueryDefs("qClearTermsCensus1")

qryClean.SQL = strSQL

Set dbsCurrent = Nothing
Set qryClean = Nothing

End Sub

It seems like it should work but I'm sure I missed something. I would
appreciate any help or advice. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
B

Bonnie

Hi Douglas,

Thanks BUNCHES for the info.

I've removed the comma but can't find the right spot for the closing
parenthesis. Not sure where IIF ends. Normally there's the If This, Then
That, Otherwise...

???

Thanks!
--
Bonnie


Douglas J Steele said:
- the IIf statement doesn't have a closing parenthesis
- the comma between [Status Code] and FROM shouldn't be there

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bonnie said:
Hi Gunny,

Fixed my typo and just get the same error but with a space in it. Here it is:

Run-time error '3075': Syntax error (missing operator) in query expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013Copy WITH OWNERACCESS OPTION;'

The debugger highlights the line: qryClean.SQL = strSQL

Any ideas? Thanks in advance for your time. I really appreciate the help.
--
Bonnie


'69 Camaro said:
Hi, Bonnie.

It has a typo. Change the following line of code:

strSQL = strSQL & "WITH OWNERACCESS OPTION;"

to:

strSQL = strSQL & " WITH OWNERACCESS OPTION;"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

Hi there! Using A02 on XP. Not a programmer but love learning this. Have a
form with a record showing the contract number in a text field [RunThisOne].
First button imports a file into a table and names it
[Forms]![fCensus1Conversion]![RunThisOne]&"Copy". So my new table would be
named GP0013Copy. A few of the records may have a [Status Code] of "T"
(Terminated). I've created a DELETE query to remove the terminated records
from the table GP0013Copy before exporting again. Now I need to create some
VB to change my source for the query. I may have contract number GP0013 or
GP1992, etc. on the form. I need to delete the [Status Code] = T records in
the table named for the contract number on my form. I've altered some code
given to me for a project something like this. But, I'm getting an error:
Run-time error '3075': Syntax error (missing operator) in query expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013CopyWITH OWNERACCESS OPTION;'

Private Sub Command39_Click()

Dim strSQL As String
Dim dbsCurrent As Database
Dim qryClean As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

strSQL = strSQL & "IIf((" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Copy![Status Code])= 'T', "

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy.[Status
Code], "

strSQL = strSQL & " FROM "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy"
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

'MsgBox strSQL

'the name of your query goes in here
Set qryClean = dbsCurrent.QueryDefs("qClearTermsCensus1")

qryClean.SQL = strSQL

Set dbsCurrent = Nothing
Set qryClean = Nothing

End Sub

It seems like it should work but I'm sure I missed something. I would
appreciate any help or advice. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J Steele

You're correct: IIf should have 3 parts. What value do you expect to see if
the Status Code isn't T?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bonnie said:
Hi Douglas,

Thanks BUNCHES for the info.

I've removed the comma but can't find the right spot for the closing
parenthesis. Not sure where IIF ends. Normally there's the If This, Then
That, Otherwise...

???

Thanks!
--
Bonnie


Douglas J Steele said:
- the IIf statement doesn't have a closing parenthesis
- the comma between [Status Code] and FROM shouldn't be there

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bonnie said:
Hi Gunny,

Fixed my typo and just get the same error but with a space in it.
Click to expand...
Here it
is:
Run-time error '3075': Syntax error (missing operator) in query expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013Copy WITH OWNERACCESS OPTION;'

The debugger highlights the line: qryClean.SQL = strSQL

Any ideas? Thanks in advance for your time. I really appreciate the help.
--
Bonnie


:

Hi, Bonnie.

It has a typo. Change the following line of code:

strSQL = strSQL & "WITH OWNERACCESS OPTION;"

to:

strSQL = strSQL & " WITH OWNERACCESS OPTION;"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for
Click to expand...
contact
info.
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers.
Click to expand...
Remember
that
questions answered the quickest are often from those who have a
Click to expand...
history
of
rewarding the contributors who have taken the time to answer questions
correctly.


:

Hi there! Using A02 on XP. Not a programmer but love learning
Click to expand...
this.
Have a
form with a record showing the contract number in a text field [RunThisOne].
First button imports a file into a table and names it
[Forms]![fCensus1Conversion]![RunThisOne]&"Copy". So my new table would be
named GP0013Copy. A few of the records may have a [Status Code]
Click to expand...
of
"T"
(Terminated). I've created a DELETE query to remove the
Click to expand...
terminated
records
from the table GP0013Copy before exporting again. Now I need to create some
VB to change my source for the query. I may have contract number GP0013 or
GP1992, etc. on the form. I need to delete the [Status Code] = T records in
the table named for the contract number on my form. I've altered
Click to expand...
some
code
given to me for a project something like this. But, I'm getting
Click to expand...
an
error:
Run-time error '3075': Syntax error (missing operator) in query expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013CopyWITH OWNERACCESS OPTION;'

Private Sub Command39_Click()

Dim strSQL As String
Dim dbsCurrent As Database
Dim qryClean As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

strSQL = strSQL & "IIf((" &
Click to expand...
[Forms]![fCensus1Conversion]![RunThisOne]
&
"Copy![Status Code])= 'T', "

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy.[Status
Code], "

strSQL = strSQL & " FROM "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy"
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

'MsgBox strSQL

'the name of your query goes in here
Set qryClean = dbsCurrent.QueryDefs("qClearTermsCensus1")

qryClean.SQL = strSQL

Set dbsCurrent = Nothing
Set qryClean = Nothing

End Sub

It seems like it should work but I'm sure I missed something. I would
appreciate any help or advice. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
B

Bonnie

Hi Douglas,

If not T, then nothing. I just want the records where [StatusCode]=T. The
query I'm running at the bottom is a delete query.

Thx!
--
Bonnie


Douglas J Steele said:
You're correct: IIf should have 3 parts. What value do you expect to see if
the Status Code isn't T?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bonnie said:
Hi Douglas,

Thanks BUNCHES for the info.

I've removed the comma but can't find the right spot for the closing
parenthesis. Not sure where IIF ends. Normally there's the If This, Then
That, Otherwise...

???

Thanks!
--
Bonnie


Douglas J Steele said:
- the IIf statement doesn't have a closing parenthesis
- the comma between [Status Code] and FROM shouldn't be there

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Gunny,

Fixed my typo and just get the same error but with a space in it. Here it
is:

Run-time error '3075': Syntax error (missing operator) in query
expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013Copy WITH OWNERACCESS OPTION;'

The debugger highlights the line: qryClean.SQL = strSQL

Any ideas? Thanks in advance for your time. I really appreciate the
help.
--
Bonnie


:

Hi, Bonnie.

It has a typo. Change the following line of code:

strSQL = strSQL & "WITH OWNERACCESS OPTION;"

to:

strSQL = strSQL & " WITH OWNERACCESS OPTION;"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message,
which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.


:

Hi there! Using A02 on XP. Not a programmer but love learning this.
Have a
form with a record showing the contract number in a text field
[RunThisOne].
First button imports a file into a table and names it
[Forms]![fCensus1Conversion]![RunThisOne]&"Copy". So my new table
would be
named GP0013Copy. A few of the records may have a [Status Code] of
"T"
(Terminated). I've created a DELETE query to remove the terminated
records
from the table GP0013Copy before exporting again. Now I need to
create some
VB to change my source for the query. I may have contract number
GP0013 or
GP1992, etc. on the form. I need to delete the [Status Code] = T
records in
the table named for the contract number on my form. I've altered some
code
given to me for a project something like this. But, I'm getting an
error:
Run-time error '3075': Syntax error (missing operator) in query
expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013CopyWITH OWNERACCESS OPTION;'

Private Sub Command39_Click()

Dim strSQL As String
Dim dbsCurrent As Database
Dim qryClean As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

strSQL = strSQL & "IIf((" & [Forms]![fCensus1Conversion]![RunThisOne]
&
"Copy![Status Code])= 'T', "

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Copy.[Status
Code], "

strSQL = strSQL & " FROM "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy"
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

'MsgBox strSQL

'the name of your query goes in here
Set qryClean = dbsCurrent.QueryDefs("qClearTermsCensus1")

qryClean.SQL = strSQL

Set dbsCurrent = Nothing
Set qryClean = Nothing

End Sub

It seems like it should work but I'm sure I missed something. I would
appreciate any help or advice. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
6

'69 Camaro

Hi, Bonnie.

What you're trying to build is difficult to create and a nightmare to
maintain. If all you need to do is delete records in the new table where the
Status code is "T," then try:

Private Sub Command39_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;", dbFailOnError

ErrHandler:

MsgBox "Error in Command39_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub ' Command39_Click( )

.. . . where the fCensus1Conversion form is open and there's a value in the
RunThisOne text box that matches the name of a table in the database. And I
would advise changing the name of this button to something more meaningful.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Bonnie said:
Hi Gunny,

Fixed my typo and just get the same error but with a space in it. Here it is:

Run-time error '3075': Syntax error (missing operator) in query expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013Copy WITH OWNERACCESS OPTION;'

The debugger highlights the line: qryClean.SQL = strSQL

Any ideas? Thanks in advance for your time. I really appreciate the help.
--
Bonnie


'69 Camaro said:
Hi, Bonnie.

It has a typo. Change the following line of code:

strSQL = strSQL & "WITH OWNERACCESS OPTION;"

to:

strSQL = strSQL & " WITH OWNERACCESS OPTION;"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Bonnie said:
Hi there! Using A02 on XP. Not a programmer but love learning this. Have a
form with a record showing the contract number in a text field [RunThisOne].
First button imports a file into a table and names it
[Forms]![fCensus1Conversion]![RunThisOne]&"Copy". So my new table would be
named GP0013Copy. A few of the records may have a [Status Code] of "T"
(Terminated). I've created a DELETE query to remove the terminated records
from the table GP0013Copy before exporting again. Now I need to create some
VB to change my source for the query. I may have contract number GP0013 or
GP1992, etc. on the form. I need to delete the [Status Code] = T records in
the table named for the contract number on my form. I've altered some code
given to me for a project something like this. But, I'm getting an error:
Run-time error '3075': Syntax error (missing operator) in query expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013CopyWITH OWNERACCESS OPTION;'

Private Sub Command39_Click()

Dim strSQL As String
Dim dbsCurrent As Database
Dim qryClean As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

strSQL = strSQL & "IIf((" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Copy![Status Code])= 'T', "

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy.[Status
Code], "

strSQL = strSQL & " FROM "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy"
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

'MsgBox strSQL

'the name of your query goes in here
Set qryClean = dbsCurrent.QueryDefs("qClearTermsCensus1")

qryClean.SQL = strSQL

Set dbsCurrent = Nothing
Set qryClean = Nothing

End Sub

It seems like it should work but I'm sure I missed something. I would
appreciate any help or advice. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
B

Bonnie

Hi Gunny! Thanks for trying again. You are correct and your offering below
would be GREAT. BUT, getting an error.

I put this in my OnClick event:

Private Sub Command44_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;", dbFailOnError

ErrHandler:

MsgBox "Error in Command44_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

I removed the space after "Copy " and put it before "WHERE" and still got
the same error. The error message box reads: Error in Command39_OnClick()
in fCensus1Conversion form. Error #3061. Too few parameters. Expected 1.

The parameter should be satisfied with equals T. That's the one parameter.
I'm sure I'm doing something wrong. Any suggestions?
--
Bonnie


'69 Camaro said:
Hi, Bonnie.

What you're trying to build is difficult to create and a nightmare to
maintain. If all you need to do is delete records in the new table where the
Status code is "T," then try:

Private Sub Command39_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;", dbFailOnError

ErrHandler:

MsgBox "Error in Command39_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub ' Command39_Click( )

. . . where the fCensus1Conversion form is open and there's a value in the
RunThisOne text box that matches the name of a table in the database. And I
would advise changing the name of this button to something more meaningful.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Bonnie said:
Hi Gunny,

Fixed my typo and just get the same error but with a space in it. Here it is:

Run-time error '3075': Syntax error (missing operator) in query expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013Copy WITH OWNERACCESS OPTION;'

The debugger highlights the line: qryClean.SQL = strSQL

Any ideas? Thanks in advance for your time. I really appreciate the help.
--
Bonnie


'69 Camaro said:
Hi, Bonnie.

It has a typo. Change the following line of code:

strSQL = strSQL & "WITH OWNERACCESS OPTION;"

to:

strSQL = strSQL & " WITH OWNERACCESS OPTION;"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

Hi there! Using A02 on XP. Not a programmer but love learning this. Have a
form with a record showing the contract number in a text field [RunThisOne].
First button imports a file into a table and names it
[Forms]![fCensus1Conversion]![RunThisOne]&"Copy". So my new table would be
named GP0013Copy. A few of the records may have a [Status Code] of "T"
(Terminated). I've created a DELETE query to remove the terminated records
from the table GP0013Copy before exporting again. Now I need to create some
VB to change my source for the query. I may have contract number GP0013 or
GP1992, etc. on the form. I need to delete the [Status Code] = T records in
the table named for the contract number on my form. I've altered some code
given to me for a project something like this. But, I'm getting an error:
Run-time error '3075': Syntax error (missing operator) in query expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013CopyWITH OWNERACCESS OPTION;'

Private Sub Command39_Click()

Dim strSQL As String
Dim dbsCurrent As Database
Dim qryClean As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

strSQL = strSQL & "IIf((" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Copy![Status Code])= 'T', "

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy.[Status
Code], "

strSQL = strSQL & " FROM "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy"
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

'MsgBox strSQL

'the name of your query goes in here
Set qryClean = dbsCurrent.QueryDefs("qClearTermsCensus1")

qryClean.SQL = strSQL

Set dbsCurrent = Nothing
Set qryClean = Nothing

End Sub

It seems like it should work but I'm sure I missed something. I would
appreciate any help or advice. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
6

'69 Camaro

Hi, Bonnie.

Sorry. I did a terrible job of pasting the code into the window, because I
forgot a line, Exit Sub, so the code will automatically execute the error
handler even when there's no error. But apparently, there's a real error
that the error handler is showing you. We can take a look what the problem
is by using the debugger.

Frist, press <CTRL><G> to open the Immediate Window. Paste the following
into your module for your button's OnClick( ) event:

Private Sub Command44_Click()

On Error GoTo ErrHandler

Dim sqlStmt As String

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & _
"Copy " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;"

Debug.Print "*" & sqlStmt & "*"

CurrentDb().Execute sqlStmt, dbFailOnError

Exit Sub

ErrHandler:

MsgBox "Error in Command44_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub ' Command44_Click( )

Now, select your button to run the procedure. You'll get the error message
in a message box, but you'll also get the SQL statement that's passed to Jet
in the Immediate Window. Copy and paste that string into your next post so
that we can see what value [Forms]![fCensus1Conversion]![RunThisOne] actually
gives you. It _should_ be something like:

*DELETE * FROM GP0013Copy WHERE ([Status Code] = 'T') WITH OWNERACCESS
OPTION;*

Of course, the new table, GP0013Copy, must already exist before this query
is run.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Bonnie said:
Hi Gunny! Thanks for trying again. You are correct and your offering below
would be GREAT. BUT, getting an error.

I put this in my OnClick event:

Private Sub Command44_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;", dbFailOnError

ErrHandler:

MsgBox "Error in Command44_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

I removed the space after "Copy " and put it before "WHERE" and still got
the same error. The error message box reads: Error in Command39_OnClick()
in fCensus1Conversion form. Error #3061. Too few parameters. Expected 1.

The parameter should be satisfied with equals T. That's the one parameter.
I'm sure I'm doing something wrong. Any suggestions?
--
Bonnie


'69 Camaro said:
Hi, Bonnie.

What you're trying to build is difficult to create and a nightmare to
maintain. If all you need to do is delete records in the new table where the
Status code is "T," then try:

Private Sub Command39_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;", dbFailOnError

ErrHandler:

MsgBox "Error in Command39_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub ' Command39_Click( )

. . . where the fCensus1Conversion form is open and there's a value in the
RunThisOne text box that matches the name of a table in the database. And I
would advise changing the name of this button to something more meaningful.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Bonnie said:
Hi Gunny,

Fixed my typo and just get the same error but with a space in it. Here it is:

Run-time error '3075': Syntax error (missing operator) in query expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013Copy WITH OWNERACCESS OPTION;'

The debugger highlights the line: qryClean.SQL = strSQL

Any ideas? Thanks in advance for your time. I really appreciate the help.
--
Bonnie


:

Hi, Bonnie.

It has a typo. Change the following line of code:

strSQL = strSQL & "WITH OWNERACCESS OPTION;"

to:

strSQL = strSQL & " WITH OWNERACCESS OPTION;"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

Hi there! Using A02 on XP. Not a programmer but love learning this. Have a
form with a record showing the contract number in a text field [RunThisOne].
First button imports a file into a table and names it
[Forms]![fCensus1Conversion]![RunThisOne]&"Copy". So my new table would be
named GP0013Copy. A few of the records may have a [Status Code] of "T"
(Terminated). I've created a DELETE query to remove the terminated records
from the table GP0013Copy before exporting again. Now I need to create some
VB to change my source for the query. I may have contract number GP0013 or
GP1992, etc. on the form. I need to delete the [Status Code] = T records in
the table named for the contract number on my form. I've altered some code
given to me for a project something like this. But, I'm getting an error:
Run-time error '3075': Syntax error (missing operator) in query expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013CopyWITH OWNERACCESS OPTION;'

Private Sub Command39_Click()

Dim strSQL As String
Dim dbsCurrent As Database
Dim qryClean As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

strSQL = strSQL & "IIf((" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Copy![Status Code])= 'T', "

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy.[Status
Code], "

strSQL = strSQL & " FROM "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy"
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

'MsgBox strSQL

'the name of your query goes in here
Set qryClean = dbsCurrent.QueryDefs("qClearTermsCensus1")

qryClean.SQL = strSQL

Set dbsCurrent = Nothing
Set qryClean = Nothing

End Sub

It seems like it should work but I'm sure I missed something. I would
appreciate any help or advice. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

What's the value in [Forms]![fCensus1Conversion]![RunThisOne]? If it
includes spaces, you need to use:

CurrentDb().Execute "DELETE * " & _
"FROM [" & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy] " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;", dbFailOnError

Also, are you sure that whatever table you're getting from the field does
have a field named Status Code (complete with space) in it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bonnie said:
Hi Gunny! Thanks for trying again. You are correct and your offering
below
would be GREAT. BUT, getting an error.

I put this in my OnClick event:

Private Sub Command44_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;", dbFailOnError

ErrHandler:

MsgBox "Error in Command44_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

I removed the space after "Copy " and put it before "WHERE" and still got
the same error. The error message box reads: Error in
Command39_OnClick()
in fCensus1Conversion form. Error #3061. Too few parameters. Expected
1.

The parameter should be satisfied with equals T. That's the one
parameter.
I'm sure I'm doing something wrong. Any suggestions?
--
Bonnie


'69 Camaro said:
Hi, Bonnie.

What you're trying to build is difficult to create and a nightmare to
maintain. If all you need to do is delete records in the new table where
the
Status code is "T," then try:

Private Sub Command39_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;", dbFailOnError

ErrHandler:

MsgBox "Error in Command39_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub ' Command39_Click( )

. . . where the fCensus1Conversion form is open and there's a value in
the
RunThisOne text box that matches the name of a table in the database.
And I
would advise changing the name of this button to something more
meaningful.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.


Bonnie said:
Hi Gunny,

Fixed my typo and just get the same error but with a space in it. Here
it is:

Run-time error '3075': Syntax error (missing operator) in query
expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013Copy WITH OWNERACCESS OPTION;'

The debugger highlights the line: qryClean.SQL = strSQL

Any ideas? Thanks in advance for your time. I really appreciate the
help.
--
Bonnie


:

Hi, Bonnie.

It has a typo. Change the following line of code:

strSQL = strSQL & "WITH OWNERACCESS OPTION;"

to:

strSQL = strSQL & " WITH OWNERACCESS OPTION;"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for
contact info.

- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message,
which
adds your question and the answers to the database of answers.
Remember that
questions answered the quickest are often from those who have a
history of
rewarding the contributors who have taken the time to answer
questions
correctly.


:

Hi there! Using A02 on XP. Not a programmer but love learning
this. Have a
form with a record showing the contract number in a text field
[RunThisOne].
First button imports a file into a table and names it
[Forms]![fCensus1Conversion]![RunThisOne]&"Copy". So my new table
would be
named GP0013Copy. A few of the records may have a [Status Code] of
"T"
(Terminated). I've created a DELETE query to remove the terminated
records
from the table GP0013Copy before exporting again. Now I need to
create some
VB to change my source for the query. I may have contract number
GP0013 or
GP1992, etc. on the form. I need to delete the [Status Code] = T
records in
the table named for the contract number on my form. I've altered
some code
given to me for a project something like this. But, I'm getting an
error:
Run-time error '3075': Syntax error (missing operator) in query
expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013CopyWITH OWNERACCESS OPTION;'

Private Sub Command39_Click()

Dim strSQL As String
Dim dbsCurrent As Database
Dim qryClean As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

strSQL = strSQL & "IIf((" &
[Forms]![fCensus1Conversion]![RunThisOne] &
"Copy![Status Code])= 'T', "

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Copy.[Status
Code], "

strSQL = strSQL & " FROM "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Copy"
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

'MsgBox strSQL

'the name of your query goes in here
Set qryClean = dbsCurrent.QueryDefs("qClearTermsCensus1")

qryClean.SQL = strSQL

Set dbsCurrent = Nothing
Set qryClean = Nothing

End Sub

It seems like it should work but I'm sure I missed something. I
would
appreciate any help or advice. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
B

Bonnie

Hi Gunny!!! Guess what!?!? It works now!!! And, of course, it was
something simple that I thought I had checked more than once. In my Copy
table, the field [Status Date] was [STATUSDATE]. It doesn't become [Status
Date] until it is exported to Excel so the header row looks better to the
client. Thank you SO very much for all your help on this. LUV U GUYS!!!
--
Bonnie


'69 Camaro said:
Hi, Bonnie.

Sorry. I did a terrible job of pasting the code into the window, because I
forgot a line, Exit Sub, so the code will automatically execute the error
handler even when there's no error. But apparently, there's a real error
that the error handler is showing you. We can take a look what the problem
is by using the debugger.

Frist, press <CTRL><G> to open the Immediate Window. Paste the following
into your module for your button's OnClick( ) event:

Private Sub Command44_Click()

On Error GoTo ErrHandler

Dim sqlStmt As String

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & _
"Copy " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;"

Debug.Print "*" & sqlStmt & "*"

CurrentDb().Execute sqlStmt, dbFailOnError

Exit Sub

ErrHandler:

MsgBox "Error in Command44_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub ' Command44_Click( )

Now, select your button to run the procedure. You'll get the error message
in a message box, but you'll also get the SQL statement that's passed to Jet
in the Immediate Window. Copy and paste that string into your next post so
that we can see what value [Forms]![fCensus1Conversion]![RunThisOne] actually
gives you. It _should_ be something like:

*DELETE * FROM GP0013Copy WHERE ([Status Code] = 'T') WITH OWNERACCESS
OPTION;*

Of course, the new table, GP0013Copy, must already exist before this query
is run.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


Bonnie said:
Hi Gunny! Thanks for trying again. You are correct and your offering below
would be GREAT. BUT, getting an error.

I put this in my OnClick event:

Private Sub Command44_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;", dbFailOnError

ErrHandler:

MsgBox "Error in Command44_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

I removed the space after "Copy " and put it before "WHERE" and still got
the same error. The error message box reads: Error in Command39_OnClick()
in fCensus1Conversion form. Error #3061. Too few parameters. Expected 1.

The parameter should be satisfied with equals T. That's the one parameter.
I'm sure I'm doing something wrong. Any suggestions?
--
Bonnie


'69 Camaro said:
Hi, Bonnie.

What you're trying to build is difficult to create and a nightmare to
maintain. If all you need to do is delete records in the new table where the
Status code is "T," then try:

Private Sub Command39_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;", dbFailOnError

ErrHandler:

MsgBox "Error in Command39_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub ' Command39_Click( )

. . . where the fCensus1Conversion form is open and there's a value in the
RunThisOne text box that matches the name of a table in the database. And I
would advise changing the name of this button to something more meaningful.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

Hi Gunny,

Fixed my typo and just get the same error but with a space in it. Here it is:

Run-time error '3075': Syntax error (missing operator) in query expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013Copy WITH OWNERACCESS OPTION;'

The debugger highlights the line: qryClean.SQL = strSQL

Any ideas? Thanks in advance for your time. I really appreciate the help.
--
Bonnie


:

Hi, Bonnie.

It has a typo. Change the following line of code:

strSQL = strSQL & "WITH OWNERACCESS OPTION;"

to:

strSQL = strSQL & " WITH OWNERACCESS OPTION;"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


:

Hi there! Using A02 on XP. Not a programmer but love learning this. Have a
form with a record showing the contract number in a text field [RunThisOne].
First button imports a file into a table and names it
[Forms]![fCensus1Conversion]![RunThisOne]&"Copy". So my new table would be
named GP0013Copy. A few of the records may have a [Status Code] of "T"
(Terminated). I've created a DELETE query to remove the terminated records
from the table GP0013Copy before exporting again. Now I need to create some
VB to change my source for the query. I may have contract number GP0013 or
GP1992, etc. on the form. I need to delete the [Status Code] = T records in
the table named for the contract number on my form. I've altered some code
given to me for a project something like this. But, I'm getting an error:
Run-time error '3075': Syntax error (missing operator) in query expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013CopyWITH OWNERACCESS OPTION;'

Private Sub Command39_Click()

Dim strSQL As String
Dim dbsCurrent As Database
Dim qryClean As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

strSQL = strSQL & "IIf((" & [Forms]![fCensus1Conversion]![RunThisOne] &
"Copy![Status Code])= 'T', "

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy.[Status
Code], "

strSQL = strSQL & " FROM "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy"
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

'MsgBox strSQL

'the name of your query goes in here
Set qryClean = dbsCurrent.QueryDefs("qClearTermsCensus1")

qryClean.SQL = strSQL

Set dbsCurrent = Nothing
Set qryClean = Nothing

End Sub

It seems like it should work but I'm sure I missed something. I would
appreciate any help or advice. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
B

Bonnie

Douglas, THANK YOU so much! I decided to check ONE MORE TIME on the EXACT
field name and guess what?...It was [STATUSCODE] before it became [Status
Code]. I really appreciate your patience and perserverence in helping me.
--
Bonnie


Douglas J. Steele said:
What's the value in [Forms]![fCensus1Conversion]![RunThisOne]? If it
includes spaces, you need to use:

CurrentDb().Execute "DELETE * " & _
"FROM [" & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy] " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;", dbFailOnError

Also, are you sure that whatever table you're getting from the field does
have a field named Status Code (complete with space) in it?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bonnie said:
Hi Gunny! Thanks for trying again. You are correct and your offering
below
would be GREAT. BUT, getting an error.

I put this in my OnClick event:

Private Sub Command44_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;", dbFailOnError

ErrHandler:

MsgBox "Error in Command44_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

I removed the space after "Copy " and put it before "WHERE" and still got
the same error. The error message box reads: Error in
Command39_OnClick()
in fCensus1Conversion form. Error #3061. Too few parameters. Expected
1.

The parameter should be satisfied with equals T. That's the one
parameter.
I'm sure I'm doing something wrong. Any suggestions?
--
Bonnie


'69 Camaro said:
Hi, Bonnie.

What you're trying to build is difficult to create and a nightmare to
maintain. If all you need to do is delete records in the new table where
the
Status code is "T," then try:

Private Sub Command39_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;", dbFailOnError

ErrHandler:

MsgBox "Error in Command39_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub ' Command39_Click( )

. . . where the fCensus1Conversion form is open and there's a value in
the
RunThisOne text box that matches the name of a table in the database.
And I
would advise changing the name of this button to something more
meaningful.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.


:

Hi Gunny,

Fixed my typo and just get the same error but with a space in it. Here
it is:

Run-time error '3075': Syntax error (missing operator) in query
expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013Copy WITH OWNERACCESS OPTION;'

The debugger highlights the line: qryClean.SQL = strSQL

Any ideas? Thanks in advance for your time. I really appreciate the
help.
--
Bonnie


:

Hi, Bonnie.

It has a typo. Change the following line of code:

strSQL = strSQL & "WITH OWNERACCESS OPTION;"

to:

strSQL = strSQL & " WITH OWNERACCESS OPTION;"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for
contact info.

- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message,
which
adds your question and the answers to the database of answers.
Remember that
questions answered the quickest are often from those who have a
history of
rewarding the contributors who have taken the time to answer
questions
correctly.


:

Hi there! Using A02 on XP. Not a programmer but love learning
this. Have a
form with a record showing the contract number in a text field
[RunThisOne].
First button imports a file into a table and names it
[Forms]![fCensus1Conversion]![RunThisOne]&"Copy". So my new table
would be
named GP0013Copy. A few of the records may have a [Status Code] of
"T"
(Terminated). I've created a DELETE query to remove the terminated
records
from the table GP0013Copy before exporting again. Now I need to
create some
VB to change my source for the query. I may have contract number
GP0013 or
GP1992, etc. on the form. I need to delete the [Status Code] = T
records in
the table named for the contract number on my form. I've altered
some code
given to me for a project something like this. But, I'm getting an
error:
Run-time error '3075': Syntax error (missing operator) in query
expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013CopyWITH OWNERACCESS OPTION;'

Private Sub Command39_Click()

Dim strSQL As String
Dim dbsCurrent As Database
Dim qryClean As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

strSQL = strSQL & "IIf((" &
[Forms]![fCensus1Conversion]![RunThisOne] &
"Copy![Status Code])= 'T', "

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Copy.[Status
Code], "

strSQL = strSQL & " FROM "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Copy"
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

'MsgBox strSQL

'the name of your query goes in here
Set qryClean = dbsCurrent.QueryDefs("qClearTermsCensus1")

qryClean.SQL = strSQL

Set dbsCurrent = Nothing
Set qryClean = Nothing

End Sub

It seems like it should work but I'm sure I missed something. I
would
appreciate any help or advice. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
6

'69 Camaro

You're welcome! Glad you got it sorted out.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


Bonnie said:
Hi Gunny!!! Guess what!?!? It works now!!! And, of course, it was
something simple that I thought I had checked more than once. In my Copy
table, the field [Status Date] was [STATUSDATE]. It doesn't become
[Status
Date] until it is exported to Excel so the header row looks better to the
client. Thank you SO very much for all your help on this. LUV U GUYS!!!
--
Bonnie


'69 Camaro said:
Hi, Bonnie.

Sorry. I did a terrible job of pasting the code into the window, because
I
forgot a line, Exit Sub, so the code will automatically execute the error
handler even when there's no error. But apparently, there's a real error
that the error handler is showing you. We can take a look what the
problem
is by using the debugger.

Frist, press <CTRL><G> to open the Immediate Window. Paste the following
into your module for your button's OnClick( ) event:

Private Sub Command44_Click()

On Error GoTo ErrHandler

Dim sqlStmt As String

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & _
"Copy " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;"

Debug.Print "*" & sqlStmt & "*"

CurrentDb().Execute sqlStmt, dbFailOnError

Exit Sub

ErrHandler:

MsgBox "Error in Command44_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub ' Command44_Click( )

Now, select your button to run the procedure. You'll get the error
message
in a message box, but you'll also get the SQL statement that's passed to
Jet
in the Immediate Window. Copy and paste that string into your next post
so
that we can see what value [Forms]![fCensus1Conversion]![RunThisOne]
actually
gives you. It _should_ be something like:

*DELETE * FROM GP0013Copy WHERE ([Status Code] = 'T') WITH OWNERACCESS
OPTION;*

Of course, the new table, GP0013Copy, must already exist before this
query
is run.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that
questions answered the quickest are often from those who have a history
of
rewarding the contributors who have taken the time to answer questions
correctly.


Bonnie said:
Hi Gunny! Thanks for trying again. You are correct and your offering
below
would be GREAT. BUT, getting an error.

I put this in my OnClick event:

Private Sub Command44_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;", dbFailOnError

ErrHandler:

MsgBox "Error in Command44_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

I removed the space after "Copy " and put it before "WHERE" and still
got
the same error. The error message box reads: Error in
Command39_OnClick()
in fCensus1Conversion form. Error #3061. Too few parameters.
Expected 1.

The parameter should be satisfied with equals T. That's the one
parameter.
I'm sure I'm doing something wrong. Any suggestions?
--
Bonnie


:

Hi, Bonnie.

What you're trying to build is difficult to create and a nightmare to
maintain. If all you need to do is delete records in the new table
where the
Status code is "T," then try:

Private Sub Command39_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy "
& _
"WHERE ([Status Code] = 'T') " & _
"WITH OWNERACCESS OPTION;", dbFailOnError

ErrHandler:

MsgBox "Error in Command39_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub ' Command39_Click( )

. . . where the fCensus1Conversion form is open and there's a value
in the
RunThisOne text box that matches the name of a table in the database.
And I
would advise changing the name of this button to something more
meaningful.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for
contact info.

- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the message,
which
adds your question and the answers to the database of answers.
Remember that
questions answered the quickest are often from those who have a
history of
rewarding the contributors who have taken the time to answer
questions
correctly.


:

Hi Gunny,

Fixed my typo and just get the same error but with a space in it.
Here it is:

Run-time error '3075': Syntax error (missing operator) in query
expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code], FROM
GP0013Copy WITH OWNERACCESS OPTION;'

The debugger highlights the line: qryClean.SQL = strSQL

Any ideas? Thanks in advance for your time. I really appreciate
the help.
--
Bonnie


:

Hi, Bonnie.

It has a typo. Change the following line of code:

strSQL = strSQL & "WITH OWNERACCESS OPTION;"

to:

strSQL = strSQL & " WITH OWNERACCESS OPTION;"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and
tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for
contact info.

- - -
If my answer has helped you, please sign in and answer yes to the
question
"Did this post answer your question?" at the bottom of the
message, which
adds your question and the answers to the database of answers.
Remember that
questions answered the quickest are often from those who have a
history of
rewarding the contributors who have taken the time to answer
questions
correctly.


:

Hi there! Using A02 on XP. Not a programmer but love learning
this. Have a
form with a record showing the contract number in a text field
[RunThisOne].
First button imports a file into a table and names it
[Forms]![fCensus1Conversion]![RunThisOne]&"Copy". So my new
table would be
named GP0013Copy. A few of the records may have a [Status
Code] of "T"
(Terminated). I've created a DELETE query to remove the
terminated records
from the table GP0013Copy before exporting again. Now I need
to create some
VB to change my source for the query. I may have contract
number GP0013 or
GP1992, etc. on the form. I need to delete the [Status Code] =
T records in
the table named for the contract number on my form. I've
altered some code
given to me for a project something like this. But, I'm
getting an error:
Run-time error '3075': Syntax error (missing operator) in
query expression
'IIf((GP0013Copy![Status Code])='T', GP0013Copy.[Status Code],
FROM
GP0013CopyWITH OWNERACCESS OPTION;'

Private Sub Command39_Click()

Dim strSQL As String
Dim dbsCurrent As Database
Dim qryClean As QueryDef

Set dbsCurrent = CurrentDb

strSQL = "SELECT "

strSQL = strSQL & "IIf((" &
[Forms]![fCensus1Conversion]![RunThisOne] &
"Copy![Status Code])= 'T', "

strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Copy.[Status
Code], "

strSQL = strSQL & " FROM "
strSQL = strSQL & [Forms]![fCensus1Conversion]![RunThisOne] &
"Copy"
strSQL = strSQL & "WITH OWNERACCESS OPTION;"

'MsgBox strSQL

'the name of your query goes in here
Set qryClean = dbsCurrent.QueryDefs("qClearTermsCensus1")

qryClean.SQL = strSQL

Set dbsCurrent = Nothing
Set qryClean = Nothing

End Sub

It seems like it should work but I'm sure I missed something.
I would
appreciate any help or advice. Thanks!
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

Code chgs table in query 1
Permissions on SQL 2
Date Format problem 0
Using a field on form as a parameter 8
How to automate this...? 3
Duplicate record in Form and Subform 0
Need to name range/transfer spreadsheet 4
Record validation on form based on criteria on 2 tables 0

Top