Using a field on form as a parameter

B

Bonnie

Hi everyone! Using A02 on XP. Not a programmer, but loving learning.

I have a form that imports a .txt file and then pulls few bells and blows a
few whistles to spit out a .xls file to send to a client. Each table is
named the contract number that has been selected on the form. Next I need to
delete records that meet certain criteria ([StatusCode]="T" and [StatusDate]
is less then [PYB]. PYB is an unbound field that you can type any date into.
Here's my code:

Private Sub Command44_Click()

If IsNull(Me.PYB) Then
MsgBox "You must have a Plan Year Beginning date keyed in. Click
the arrow to copy in the data from the calculated field."
Exit Sub
Else
On Error GoTo ErrHandler

Dim sqlStmt As String

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([STATUSCODE] = 'T') AND ([STATUSDATE]
<[Forms]![fCensus1Conversion]![PYB])" & _
" WITH OWNERACCESS OPTION;"

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

CurrentDb().Execute sqlStmt, dbFailOnError

MsgBox "You have deleted records with a Status Code of T and a
Status Date greater than 500 days."
End If
Exit Sub

I get this error: Error in Command44_Click() in fCensus1Conversion form.
Error #3061. Too few parameters. Expected 1.

On the Debug.Print I get this: *DELETE * FROM GP2473Copy WHERE
([STATUSCODE] = 'T') AND ([STATUSDATE] <[Forms]![fCensus1Conversion]![PYB])
WITH OWNERACCESS OPTION;*

I've made sure that my cursor leaves the field but not sure why it's not
working. Any suggestions or advice? Thanks very much in advance for taking
time to read this.
 
K

Klatuu

A couple of problems. All refereces to varialbles or controls have to be
outside the qoutes. If they are inside the quotes, Jet sees them as literal
strings. Also, note that there are rules on delimiting parameters:
Number fields - No delimiter
Text fields - " or '
Date fields - #


sqlStmt = "DELETE * FROM " & [Forms]![fCensus1Conversion]![RunThisOne] _
& "Copy WHERE [STATUSCODE] = 'T' AND ([STATUSDATE] < # " _
& [Forms]![fCensus1Conversion]![PYB]) & _
"# WITH OWNERACCESS OPTION;"
 
T

tina

try

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([STATUSCODE] = 'T') AND ([STATUSDATE]<" & _
[Forms]![fCensus1Conversion]![PYB]) & _
" WITH OWNERACCESS OPTION;"

if [STATUSDATE] is a Date/Time data type, change the syntax to

([STATUSDATE]<#" & _
[Forms]![fCensus1Conversion]![PYB]) & _
"# WITH OWNERACCESS OPTION;"

hth
 
B

Bonnie

I'm getting a 'Compile error: Syntax Error.' message. And a few times I got
'Expected: End of Statement' and the ) after [PYB] is highlighted. Tried to
remove the &_'s but didn't matter. What am I doing wrong?

--
Bonnie


tina said:
try

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([STATUSCODE] = 'T') AND ([STATUSDATE]<" & _
[Forms]![fCensus1Conversion]![PYB]) & _
" WITH OWNERACCESS OPTION;"

if [STATUSDATE] is a Date/Time data type, change the syntax to

([STATUSDATE]<#" & _
[Forms]![fCensus1Conversion]![PYB]) & _
"# WITH OWNERACCESS OPTION;"

hth


Bonnie said:
Hi everyone! Using A02 on XP. Not a programmer, but loving learning.

I have a form that imports a .txt file and then pulls few bells and blows a
few whistles to spit out a .xls file to send to a client. Each table is
named the contract number that has been selected on the form. Next I need to
delete records that meet certain criteria ([StatusCode]="T" and [StatusDate]
is less then [PYB]. PYB is an unbound field that you can type any date into.
Here's my code:

Private Sub Command44_Click()

If IsNull(Me.PYB) Then
MsgBox "You must have a Plan Year Beginning date keyed in. Click
the arrow to copy in the data from the calculated field."
Exit Sub
Else
On Error GoTo ErrHandler

Dim sqlStmt As String

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([STATUSCODE] = 'T') AND ([STATUSDATE]
<[Forms]![fCensus1Conversion]![PYB])" & _
" WITH OWNERACCESS OPTION;"

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

CurrentDb().Execute sqlStmt, dbFailOnError

MsgBox "You have deleted records with a Status Code of T and a
Status Date greater than 500 days."
End If
Exit Sub

I get this error: Error in Command44_Click() in fCensus1Conversion form.
Error #3061. Too few parameters. Expected 1.

On the Debug.Print I get this: *DELETE * FROM GP2473Copy WHERE
([STATUSCODE] = 'T') AND ([STATUSDATE] <[Forms]![fCensus1Conversion]![PYB])
WITH OWNERACCESS OPTION;*

I've made sure that my cursor leaves the field but not sure why it's not
working. Any suggestions or advice? Thanks very much in advance for taking
time to read this.
Click to expand...
Click to expand...
 
T

tina

presumably you've changed your SQL statement to comply with the suggestions
posted by myself and Klatuu. copy/paste your revised code into a post, so we
can look at the "new" SQL statement and help you fix it.

hth


Bonnie said:
I'm getting a 'Compile error: Syntax Error.' message. And a few times I got
'Expected: End of Statement' and the ) after [PYB] is highlighted. Tried to
remove the &_'s but didn't matter. What am I doing wrong?

--
Bonnie


tina said:
try

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([STATUSCODE] = 'T') AND ([STATUSDATE]<" & _
[Forms]![fCensus1Conversion]![PYB]) & _
" WITH OWNERACCESS OPTION;"

if [STATUSDATE] is a Date/Time data type, change the syntax to

([STATUSDATE]<#" & _
[Forms]![fCensus1Conversion]![PYB]) & _
"# WITH OWNERACCESS OPTION;"

hth


Bonnie said:
Hi everyone! Using A02 on XP. Not a programmer, but loving learning.

I have a form that imports a .txt file and then pulls few bells and
Click to expand...
blows
a
few whistles to spit out a .xls file to send to a client. Each table is
named the contract number that has been selected on the form. Next I
Click to expand...
need
to
delete records that meet certain criteria ([StatusCode]="T" and [StatusDate]
is less then [PYB]. PYB is an unbound field that you can type any
Click to expand...
date
into.
Here's my code:

Private Sub Command44_Click()

If IsNull(Me.PYB) Then
MsgBox "You must have a Plan Year Beginning date keyed in. Click
the arrow to copy in the data from the calculated field."
Exit Sub
Else
On Error GoTo ErrHandler

Dim sqlStmt As String

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([STATUSCODE] = 'T') AND ([STATUSDATE]
<[Forms]![fCensus1Conversion]![PYB])" & _
" WITH OWNERACCESS OPTION;"

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

CurrentDb().Execute sqlStmt, dbFailOnError

MsgBox "You have deleted records with a Status Code of T and a
Status Date greater than 500 days."
End If
Exit Sub

I get this error: Error in Command44_Click() in fCensus1Conversion form.
Error #3061. Too few parameters. Expected 1.

On the Debug.Print I get this: *DELETE * FROM GP2473Copy WHERE
([STATUSCODE] = 'T') AND ([STATUSDATE] <[Forms]![fCensus1Conversion]![PYB])
WITH OWNERACCESS OPTION;*

I've made sure that my cursor leaves the field but not sure why it's not
working. Any suggestions or advice? Thanks very much in advance for taking
time to read this.
Click to expand...
Click to expand...
Click to expand...
 
B

Bonnie

Hi Tina,

Thanks very much for helping. I'm getting a compile error when I copy in
your 'if STATUSDATE is a Date field. Here's my code:

Private Sub Command44_Click()

If IsNull(Me.PYB) Then
MsgBox "You must have a Plan Year Beginning date keyed in. Click
the arrow to copy in the data from the calculated field."
Exit Sub
Else
On Error GoTo ErrHandler

Dim sqlStmt As String

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([STATUSCODE] = 'T') AND (([STATUSDATE]<#" & _
[Forms]![fCensus1Conversion]![PYB]) & _
"# WITH OWNERACCESS OPTION;"


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

CurrentDb().Execute sqlStmt, dbFailOnError

MsgBox "You have deleted records with a Status Code of T and a
Status Date greater than 500 days."
End If
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( )

Thanks!
--
Bonnie


tina said:
presumably you've changed your SQL statement to comply with the suggestions
posted by myself and Klatuu. copy/paste your revised code into a post, so we
can look at the "new" SQL statement and help you fix it.

hth


Bonnie said:
I'm getting a 'Compile error: Syntax Error.' message. And a few times I got
'Expected: End of Statement' and the ) after [PYB] is highlighted. Tried to
remove the &_'s but didn't matter. What am I doing wrong?

--
Bonnie


tina said:
try

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([STATUSCODE] = 'T') AND ([STATUSDATE]<" & _
[Forms]![fCensus1Conversion]![PYB]) & _
" WITH OWNERACCESS OPTION;"

if [STATUSDATE] is a Date/Time data type, change the syntax to

([STATUSDATE]<#" & _
[Forms]![fCensus1Conversion]![PYB]) & _
"# WITH OWNERACCESS OPTION;"

hth


Hi everyone! Using A02 on XP. Not a programmer, but loving learning.

I have a form that imports a .txt file and then pulls few bells and blows
a
few whistles to spit out a .xls file to send to a client. Each table is
named the contract number that has been selected on the form. Next I need
to
delete records that meet certain criteria ([StatusCode]="T" and
[StatusDate]
is less then [PYB]. PYB is an unbound field that you can type any date
into.
Here's my code:

Private Sub Command44_Click()

If IsNull(Me.PYB) Then
MsgBox "You must have a Plan Year Beginning date keyed in. Click
the arrow to copy in the data from the calculated field."
Exit Sub
Else
On Error GoTo ErrHandler

Dim sqlStmt As String

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([STATUSCODE] = 'T') AND ([STATUSDATE]
<[Forms]![fCensus1Conversion]![PYB])" & _
" WITH OWNERACCESS OPTION;"

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

CurrentDb().Execute sqlStmt, dbFailOnError

MsgBox "You have deleted records with a Status Code of T and a
Status Date greater than 500 days."
End If
Exit Sub

I get this error: Error in Command44_Click() in fCensus1Conversion form.
Error #3061. Too few parameters. Expected 1.

On the Debug.Print I get this: *DELETE * FROM GP2473Copy WHERE
([STATUSCODE] = 'T') AND ([STATUSDATE]
<[Forms]![fCensus1Conversion]![PYB])
WITH OWNERACCESS OPTION;*

I've made sure that my cursor leaves the field but not sure why it's not
working. Any suggestions or advice? Thanks very much in advance for
taking
time to read this.
Click to expand...
Click to expand...
Click to expand...
 
T

tina

okay. try the following SQL statement, as

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE [STATUSCODE] = 'T' AND [STATUSDATE]<#" & _
[Forms]![fCensus1Conversion]![PYB] & _
"# WITH OWNERACCESS OPTION;"

hth


Bonnie said:
Hi Tina,

Thanks very much for helping. I'm getting a compile error when I copy in
your 'if STATUSDATE is a Date field. Here's my code:

Private Sub Command44_Click()

If IsNull(Me.PYB) Then
MsgBox "You must have a Plan Year Beginning date keyed in. Click
the arrow to copy in the data from the calculated field."
Exit Sub
Else
On Error GoTo ErrHandler

Dim sqlStmt As String

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([STATUSCODE] = 'T') AND (([STATUSDATE]<#" & _
[Forms]![fCensus1Conversion]![PYB]) & _
"# WITH OWNERACCESS OPTION;"


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

CurrentDb().Execute sqlStmt, dbFailOnError

MsgBox "You have deleted records with a Status Code of T and a
Status Date greater than 500 days."
End If
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( )

Thanks!
--
Bonnie


tina said:
presumably you've changed your SQL statement to comply with the suggestions
posted by myself and Klatuu. copy/paste your revised code into a post, so we
can look at the "new" SQL statement and help you fix it.

hth


Bonnie said:
I'm getting a 'Compile error: Syntax Error.' message. And a few
Click to expand...
times I
got
'Expected: End of Statement' and the ) after [PYB] is highlighted.
Click to expand...
Tried
to
remove the &_'s but didn't matter. What am I doing wrong?

--
Bonnie


:

try

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([STATUSCODE] = 'T') AND ([STATUSDATE]<" & _
[Forms]![fCensus1Conversion]![PYB]) & _
" WITH OWNERACCESS OPTION;"

if [STATUSDATE] is a Date/Time data type, change the syntax to

([STATUSDATE]<#" & _
[Forms]![fCensus1Conversion]![PYB]) & _
"# WITH OWNERACCESS OPTION;"

hth


Hi everyone! Using A02 on XP. Not a programmer, but loving learning.

I have a form that imports a .txt file and then pulls few bells
Click to expand...
and
blows
a
few whistles to spit out a .xls file to send to a client. Each
Click to expand...
table
is
named the contract number that has been selected on the form.
Click to expand...
Next I
need
to
delete records that meet certain criteria ([StatusCode]="T" and
[StatusDate]
is less then [PYB]. PYB is an unbound field that you can type any date
into.
Here's my code:

Private Sub Command44_Click()

If IsNull(Me.PYB) Then
MsgBox "You must have a Plan Year Beginning date keyed in. Click
the arrow to copy in the data from the calculated field."
Exit Sub
Else
On Error GoTo ErrHandler

Dim sqlStmt As String

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] &
Click to expand...
"Copy "
& _
"WHERE ([STATUSCODE] = 'T') AND ([STATUSDATE]
<[Forms]![fCensus1Conversion]![PYB])" & _
" WITH OWNERACCESS OPTION;"

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

CurrentDb().Execute sqlStmt, dbFailOnError

MsgBox "You have deleted records with a Status Code of T and a
Status Date greater than 500 days."
End If
Exit Sub

I get this error: Error in Command44_Click() in
Click to expand...
fCensus1Conversion
form.
Error #3061. Too few parameters. Expected 1.

On the Debug.Print I get this: *DELETE * FROM GP2473Copy WHERE
([STATUSCODE] = 'T') AND ([STATUSDATE]
<[Forms]![fCensus1Conversion]![PYB])
WITH OWNERACCESS OPTION;*

I've made sure that my cursor leaves the field but not sure why
Click to expand...
it's
not
working. Any suggestions or advice? Thanks very much in advance for
taking
time to read this.
Click to expand...
Click to expand...
Click to expand...
 
B

Bonnie

Hi Tina! Not sure why I had the parenthesis in there but thank you SOOOO
much for staying with this thread. Now my project is ALMOST complete.

Hope to see your name pop up on my inquiries more often. You were very
helpful.
--
Bonnie


tina said:
okay. try the following SQL statement, as

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE [STATUSCODE] = 'T' AND [STATUSDATE]<#" & _
[Forms]![fCensus1Conversion]![PYB] & _
"# WITH OWNERACCESS OPTION;"

hth


Bonnie said:
Hi Tina,

Thanks very much for helping. I'm getting a compile error when I copy in
your 'if STATUSDATE is a Date field. Here's my code:

Private Sub Command44_Click()

If IsNull(Me.PYB) Then
MsgBox "You must have a Plan Year Beginning date keyed in. Click
the arrow to copy in the data from the calculated field."
Exit Sub
Else
On Error GoTo ErrHandler

Dim sqlStmt As String

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([STATUSCODE] = 'T') AND (([STATUSDATE]<#" & _
[Forms]![fCensus1Conversion]![PYB]) & _
"# WITH OWNERACCESS OPTION;"


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

CurrentDb().Execute sqlStmt, dbFailOnError

MsgBox "You have deleted records with a Status Code of T and a
Status Date greater than 500 days."
End If
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( )

Thanks!
--
Bonnie


tina said:
presumably you've changed your SQL statement to comply with the suggestions
posted by myself and Klatuu. copy/paste your revised code into a post, so we
can look at the "new" SQL statement and help you fix it.

hth


I'm getting a 'Compile error: Syntax Error.' message. And a few times I
got
'Expected: End of Statement' and the ) after [PYB] is highlighted. Tried
to
remove the &_'s but didn't matter. What am I doing wrong?

--
Bonnie


:

try

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([STATUSCODE] = 'T') AND ([STATUSDATE]<" & _
[Forms]![fCensus1Conversion]![PYB]) & _
" WITH OWNERACCESS OPTION;"

if [STATUSDATE] is a Date/Time data type, change the syntax to

([STATUSDATE]<#" & _
[Forms]![fCensus1Conversion]![PYB]) & _
"# WITH OWNERACCESS OPTION;"

hth


Hi everyone! Using A02 on XP. Not a programmer, but loving learning.

I have a form that imports a .txt file and then pulls few bells and
blows
a
few whistles to spit out a .xls file to send to a client. Each table
is
named the contract number that has been selected on the form. Next I
need
to
delete records that meet certain criteria ([StatusCode]="T" and
[StatusDate]
is less then [PYB]. PYB is an unbound field that you can type any
date
into.
Here's my code:

Private Sub Command44_Click()

If IsNull(Me.PYB) Then
MsgBox "You must have a Plan Year Beginning date keyed in.
Click
the arrow to copy in the data from the calculated field."
Exit Sub
Else
On Error GoTo ErrHandler

Dim sqlStmt As String

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy "
& _
"WHERE ([STATUSCODE] = 'T') AND ([STATUSDATE]
<[Forms]![fCensus1Conversion]![PYB])" & _
" WITH OWNERACCESS OPTION;"

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

CurrentDb().Execute sqlStmt, dbFailOnError

MsgBox "You have deleted records with a Status Code of T and a
Status Date greater than 500 days."
End If
Exit Sub

I get this error: Error in Command44_Click() in fCensus1Conversion
form.
Error #3061. Too few parameters. Expected 1.

On the Debug.Print I get this: *DELETE * FROM GP2473Copy WHERE
([STATUSCODE] = 'T') AND ([STATUSDATE]
<[Forms]![fCensus1Conversion]![PYB])
WITH OWNERACCESS OPTION;*

I've made sure that my cursor leaves the field but not sure why it's
not
working. Any suggestions or advice? Thanks very much in advance for
taking
time to read this.
Click to expand...
Click to expand...
Click to expand...
 
T

tina

you're welcome :)
Not sure why I had the parenthesis in there
Click to expand...

if you built the query in the query design grid, and then opened the SQL
pane and copied the SQL statement to paste into your code (i often do this
myself), Access undoubtedly added the parentheses automatically when it
built the SQL statement - it really goes wild with parentheses for some
reason (though on occasion they *are* necessary).


Bonnie said:
Hi Tina! Not sure why I had the parenthesis in there but thank you SOOOO
much for staying with this thread. Now my project is ALMOST complete.

Hope to see your name pop up on my inquiries more often. You were very
helpful.
--
Bonnie


tina said:
okay. try the following SQL statement, as

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE [STATUSCODE] = 'T' AND [STATUSDATE]<#" & _
[Forms]![fCensus1Conversion]![PYB] & _
"# WITH OWNERACCESS OPTION;"

hth


Bonnie said:
Hi Tina,

Thanks very much for helping. I'm getting a compile error when I copy in
your 'if STATUSDATE is a Date field. Here's my code:

Private Sub Command44_Click()

If IsNull(Me.PYB) Then
MsgBox "You must have a Plan Year Beginning date keyed in. Click
the arrow to copy in the data from the calculated field."
Exit Sub
Else
On Error GoTo ErrHandler

Dim sqlStmt As String

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([STATUSCODE] = 'T') AND (([STATUSDATE]<#" & _
[Forms]![fCensus1Conversion]![PYB]) & _
"# WITH OWNERACCESS OPTION;"


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

CurrentDb().Execute sqlStmt, dbFailOnError

MsgBox "You have deleted records with a Status Code of T and a
Status Date greater than 500 days."
End If
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( )

Thanks!
--
Bonnie


:

presumably you've changed your SQL statement to comply with the suggestions
posted by myself and Klatuu. copy/paste your revised code into a
Click to expand...
post,
so we
can look at the "new" SQL statement and help you fix it.

hth


I'm getting a 'Compile error: Syntax Error.' message. And a few times I
got
'Expected: End of Statement' and the ) after [PYB] is
Click to expand...
highlighted.
Tried
to
remove the &_'s but didn't matter. What am I doing wrong?

--
Bonnie


:

try

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy " & _
"WHERE ([STATUSCODE] = 'T') AND ([STATUSDATE]<" & _
[Forms]![fCensus1Conversion]![PYB]) & _
" WITH OWNERACCESS OPTION;"

if [STATUSDATE] is a Date/Time data type, change the syntax to

([STATUSDATE]<#" & _
[Forms]![fCensus1Conversion]![PYB]) & _
"# WITH OWNERACCESS OPTION;"

hth


Hi everyone! Using A02 on XP. Not a programmer, but loving learning.

I have a form that imports a .txt file and then pulls few
Click to expand...
bells
and
blows
a
few whistles to spit out a .xls file to send to a client.
Click to expand...
Each
table
is
named the contract number that has been selected on the form. Next I
need
to
delete records that meet certain criteria ([StatusCode]="T" and
[StatusDate]
is less then [PYB]. PYB is an unbound field that you can type any
date
into.
Here's my code:

Private Sub Command44_Click()

If IsNull(Me.PYB) Then
MsgBox "You must have a Plan Year Beginning date keyed in.
Click
the arrow to copy in the data from the calculated field."
Exit Sub
Else
On Error GoTo ErrHandler

Dim sqlStmt As String

sqlStmt = "DELETE * " & _
"FROM " & [Forms]![fCensus1Conversion]![RunThisOne] & "Copy "
& _
"WHERE ([STATUSCODE] = 'T') AND ([STATUSDATE]
<[Forms]![fCensus1Conversion]![PYB])" & _
" WITH OWNERACCESS OPTION;"

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

CurrentDb().Execute sqlStmt, dbFailOnError

MsgBox "You have deleted records with a Status Code of
Click to expand...
T
and a
Status Date greater than 500 days."
End If
Exit Sub

I get this error: Error in Command44_Click() in fCensus1Conversion
form.
Error #3061. Too few parameters. Expected 1.

On the Debug.Print I get this: *DELETE * FROM GP2473Copy WHERE
([STATUSCODE] = 'T') AND ([STATUSDATE]
<[Forms]![fCensus1Conversion]![PYB])
WITH OWNERACCESS OPTION;*

I've made sure that my cursor leaves the field but not sure
Click to expand...
why
it's
not
working. Any suggestions or advice? Thanks very much in
Click to expand...
advance
for
taking
time to read this.
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

Need to chg queried table 12
Date Format problem 0
Code chgs table in query 1
Permissions on SQL 2
How to automate this...? 3
VBA - exporting serial letters to single documents 0
Use a form's control value as a table name 2
Form flashing 5

Top