SQL & Module

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

Hi All
I know very little about code, any help with this would be greatly
appreciated.

I have a form which is to print labels, the form is filtered using the
module "stEOPWhereClause"

If the "SelectAll" button is pressed I want to update the tick box' listed
on the form with a tick.

I have the following SQL:

SQL2 = "UPDATE [qryDeliveryLabels-2]" & "SET [qryDeliveryLabels-2].Label01 =
'-1'" & "WHERE ((([qryDeliveryLabels-2].Label01)=0))"

The problem with this one is ALL records are updated, not just the visible
ones, so I thought I would try changing the where clause to be the module:

SQL2 = "UPDATE [qryDeliveryLabels-2]" & "SET [qryDeliveryLabels-2].Label01 =
'-1'" & "WHERE (((" & stEOPWhereClause & "))"

For this example stEOPWhereClause returns:

[EnteredDate] Between [Forms]![frmEndOfProm]![StartDate] And
[Forms]![frmEndOfProm]![EndDate] AND [Brochurecode] Like
[Forms]![frmEndOfProm]![SelectBrochureAll]

But this causes an error:

Missing ), ] or Item in query expression '(((
[EnteredDate] Between [Forms]![frmEndOfProm]![StartDate] And
[Forms]![frmEndOfProm]![EndDate] AND [Brochurecode] Like
[Forms]![frmEndOfProm]![SelectBrochureAll]))'.


Thanks in advance,
Anthony.
 
Anthony said:
Hi All
I know very little about code, any help with this would be greatly
appreciated.

I have a form which is to print labels, the form is filtered using the
module "stEOPWhereClause"

If the "SelectAll" button is pressed I want to update the tick box'
listed on the form with a tick.

I have the following SQL:

SQL2 = "UPDATE [qryDeliveryLabels-2]" & "SET
[qryDeliveryLabels-2].Label01 = '-1'" & "WHERE
((([qryDeliveryLabels-2].Label01)=0))"

The problem with this one is ALL records are updated, not just the
visible ones, so I thought I would try changing the where clause to
be the module:

SQL2 = "UPDATE [qryDeliveryLabels-2]" & "SET
[qryDeliveryLabels-2].Label01 = '-1'" & "WHERE (((" &
stEOPWhereClause & "))"

For this example stEOPWhereClause returns:

[EnteredDate] Between [Forms]![frmEndOfProm]![StartDate] And
[Forms]![frmEndOfProm]![EndDate] AND [Brochurecode] Like
[Forms]![frmEndOfProm]![SelectBrochureAll]

But this causes an error:

Missing ), ] or Item in query expression '(((
[EnteredDate] Between [Forms]![frmEndOfProm]![StartDate] And
[Forms]![frmEndOfProm]![EndDate] AND [Brochurecode] Like
[Forms]![frmEndOfProm]![SelectBrochureAll]))'.
Click to expand...

You open your WHERE clause with three parentheses, and close it with
only two. I'm not sure why you need all those parentheses anyway -- I'd
expect at most one opening parenthesis, just for safety's sake, and one
matching closing prenthesis -- but at the very least the numbers have to
match.

I also wonder why you are using a text literal to update your Label01
field. Assuming it's a boolean (yes/no) field, or even a number field,
you shouldn't have '-1' in the single quotes. You also left out spaces
in a couple of places, but that has fortunately not made a difference in
this case because of the way the SQL statement is being parsed. Still,
I'd recommend the following:

SQL2 = _
"UPDATE [qryDeliveryLabels-2] " & _
"SET [qryDeliveryLabels-2].Label01 = -1 " & _
"WHERE (" & stEOPWhereClause & ")"
 
Hi Dirk,

Thankyou for you promt reply,

I had that thought but each time I played around with the Brackets I
received another error,

I have put in you suggestion and it has returned the following error:

"Too few parameters. Expected 3."

Thanks again for you help


Dirk Goldgar said:
Anthony said:
Hi All
I know very little about code, any help with this would be greatly
appreciated.

I have a form which is to print labels, the form is filtered using the
module "stEOPWhereClause"

If the "SelectAll" button is pressed I want to update the tick box'
listed on the form with a tick.

I have the following SQL:

SQL2 = "UPDATE [qryDeliveryLabels-2]" & "SET
[qryDeliveryLabels-2].Label01 = '-1'" & "WHERE
((([qryDeliveryLabels-2].Label01)=0))"

The problem with this one is ALL records are updated, not just the
visible ones, so I thought I would try changing the where clause to
be the module:

SQL2 = "UPDATE [qryDeliveryLabels-2]" & "SET
[qryDeliveryLabels-2].Label01 = '-1'" & "WHERE (((" &
stEOPWhereClause & "))"

For this example stEOPWhereClause returns:

[EnteredDate] Between [Forms]![frmEndOfProm]![StartDate] And
[Forms]![frmEndOfProm]![EndDate] AND [Brochurecode] Like
[Forms]![frmEndOfProm]![SelectBrochureAll]

But this causes an error:

Missing ), ] or Item in query expression '(((
[EnteredDate] Between [Forms]![frmEndOfProm]![StartDate] And
[Forms]![frmEndOfProm]![EndDate] AND [Brochurecode] Like
[Forms]![frmEndOfProm]![SelectBrochureAll]))'.
Click to expand...

You open your WHERE clause with three parentheses, and close it with
only two. I'm not sure why you need all those parentheses anyway -- I'd
expect at most one opening parenthesis, just for safety's sake, and one
matching closing prenthesis -- but at the very least the numbers have to
match.

I also wonder why you are using a text literal to update your Label01
field. Assuming it's a boolean (yes/no) field, or even a number field,
you shouldn't have '-1' in the single quotes. You also left out spaces
in a couple of places, but that has fortunately not made a difference in
this case because of the way the SQL statement is being parsed. Still,
I'd recommend the following:

SQL2 = _
"UPDATE [qryDeliveryLabels-2] " & _
"SET [qryDeliveryLabels-2].Label01 = -1 " & _
"WHERE (" & stEOPWhereClause & ")"


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Anthony said:
Hi Dirk,

Thankyou for you promt reply,

I had that thought but each time I played around with the Brackets I
received another error,

I have put in you suggestion and it has returned the following error:

"Too few parameters. Expected 3."

Thanks again for you help


Dirk Goldgar said:
Anthony said:
Hi All
I know very little about code, any help with this would be greatly
appreciated.

I have a form which is to print labels, the form is filtered using
the module "stEOPWhereClause"

If the "SelectAll" button is pressed I want to update the tick box'
listed on the form with a tick.

I have the following SQL:

SQL2 = "UPDATE [qryDeliveryLabels-2]" & "SET
[qryDeliveryLabels-2].Label01 = '-1'" & "WHERE
((([qryDeliveryLabels-2].Label01)=0))"

The problem with this one is ALL records are updated, not just the
visible ones, so I thought I would try changing the where clause to
be the module:

SQL2 = "UPDATE [qryDeliveryLabels-2]" & "SET
[qryDeliveryLabels-2].Label01 = '-1'" & "WHERE (((" &
stEOPWhereClause & "))"

For this example stEOPWhereClause returns:

[EnteredDate] Between [Forms]![frmEndOfProm]![StartDate] And
[Forms]![frmEndOfProm]![EndDate] AND [Brochurecode] Like
[Forms]![frmEndOfProm]![SelectBrochureAll]

But this causes an error:

Missing ), ] or Item in query expression '(((
[EnteredDate] Between [Forms]![frmEndOfProm]![StartDate] And
[Forms]![frmEndOfProm]![EndDate] AND [Brochurecode] Like
[Forms]![frmEndOfProm]![SelectBrochureAll]))'.
Click to expand...

You open your WHERE clause with three parentheses, and close it with
only two. I'm not sure why you need all those parentheses anyway --
I'd expect at most one opening parenthesis, just for safety's sake,
and one matching closing prenthesis -- but at the very least the
numbers have to match.

I also wonder why you are using a text literal to update your Label01
field. Assuming it's a boolean (yes/no) field, or even a number
field, you shouldn't have '-1' in the single quotes. You also left
out spaces in a couple of places, but that has fortunately not made
a difference in this case because of the way the SQL statement is
being parsed. Still, I'd recommend the following:

SQL2 = _
"UPDATE [qryDeliveryLabels-2] " & _
"SET [qryDeliveryLabels-2].Label01 = -1 " & _
"WHERE (" & stEOPWhereClause & ")"
Click to expand...
Click to expand...

Ah, then I suspect you are using the DAO Execute method to run this
update query. Is that the case? If so, you'll need to resolve the form
control references yourself. DoCmd.RunSQL, an Access method,
understands control references like your
"[Forms]![frmEndOfProm]![StartDate]", but DAO doesn't, and view them as
parameters that must be supplied.

Here's a handy way to do it:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

' ... other code up to this point ...

SQL2 = _
"UPDATE [qryDeliveryLabels-2] " & _
"SET [qryDeliveryLabels-2].Label01 = -1 " & _
"WHERE (" & stEOPWhereClause & ")"

Set db = CurrentDb
Set qdf = db.CreateQueryDef( "", SQL2)

For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

qdf.Execute dbFailOnError

Set df = Nothing
Set db = Nothing
 
Back
Top