Too few paramters

[GLT]

Can anyone advise why I am getting a 'Too few parameters - Expected 2' with
these two SQL statement?

strSQL1 = "UPDATE tbl01_PermSrvcsIgnore, tbl01_Services SET
tbl01_Services.Valid = 0 " & _
"WHERE (((tbl01_Services.Server)=[tbl01_PermSrvcIgnore]![Server]) AND
((tbl01_Services.Service)=[Service Name]) =
((tbl01_PermSrvcsIgnore.Type)=[forms]![ProcessErr]![cmbShutType]));"

strSQL2 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & _
"WHERE (((tbl01_PermSrvcsIgnore.Server) Like ""ALL"") AND
((tbl01_Services.Type) = [forms]![ProcessErr]![cmbShutType]) AND
((tbl01_Services.Service) = [Service Name]) AND ((tbl01_Services.ImpDate) =
[forms]![ProcessErr]![cmbImpDate]));"

Any assistance would be greatly appreciated...

Cheers,
GT.

 

[Jerry Whittle]

You are attempting to update two tables at the same time. It's very unlikely
that you can do this. Instead you need to update one table then the other.

Also you won't be able to use a direct joing on the two tables in the Where
clause. You'll probably need a subquery using an In or Exists clause.

 

[GLT]

Hi Jerry,

Thanks for your reply, maybe I should be doing this in VBA. What I am
trying to acheive is this:

I have one table (tbl01_Services) with a list of services that are imported
from a text file, and an unrelated table (tbl01_PermSrvcsIgnore) that
contains a list of services that can be ignored (ie. set the [valid] field in
tbl01_services to 0 if it finds a match in tbl01_PermSrvcsIgnore). In the
tbl01_PermSrvcsIgnore, the field 'Server' can have an individual Server name,
or 'ALL'.

I run these two queries when data is first imported and they work fine:

' In the Services Table, set all valid to 0 for entries marked 'ALL' in the
AddPermNoMon table
strSQL4 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & vbCrLf & _
"WHERE (((tbl01_PermSrvcsIgnore.Server) Like ""ALL"") AND
((tbl01_Services.Service) Like [Service Name]) AND ((tbl01_Services.ImpDate)
Is Null));"

' In the Services Table, set all valid to 0 for entries marked for specific
Servers.
' in the AddPermNoMon table
strSQL5 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & vbclrf & _
"WHERE (((tbl01_Services.Server) Like [tbl01_PermSrvcsIgnore]![Server]) AND
((tbl01_Services.Service) Like [tbl01_PermSrvcsIgnore]![Service Name]) AND
((tbl01_Services.ImpDate) Is Null));"

Of the two SQL statements above, one updates services found with server
status of 'ALL', and the other updates individual server names, and both
queries work with any records that have not yet been datestamped (ie.
((tbl01_Services.ImpDate) Is Null));

Now what I am trying to acheive is to do the same thing as above (after
records have been imported) and after the records have been datestamped.

I am also trying to factor in which type of records I want to update (ie the
SQL below i have included the ImpDate and the Type).

The ImpDate is the datestamp, and the Type field can be PROD or DEV.

The ImpDate and Type do not matter in the above SQL because I can compare
all records with no date stamp. However they do matter below.

I thought it would be a simple case (it was 03:00 am ) of just changing:
(tbl01_Services.ImpDate) Is Null to (tbl01_Services.ImpDate) =
Forms![ProcessErr]![cmbImpDate]

And adding:

((tbl01_PermSrvcsIgnore.Type)=[forms]![ProcessErr]![cmbShutType]))

But when I do this, I get the two few parameters error.

Why does it work fine in the first SQL, but bomb in the second?

You are attempting to update two tables at the same time. It's very unlikely
that you can do this. Instead you need to update one table then the other.

Also you won't be able to use a direct joing on the two tables in the Where
clause. You'll probably need a subquery using an In or Exists clause.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Can anyone advise why I am getting a 'Too few parameters - Expected 2' with
these two SQL statement?

strSQL1 = "UPDATE tbl01_PermSrvcsIgnore, tbl01_Services SET
tbl01_Services.Valid = 0 " & _
"WHERE (((tbl01_Services.Server)=[tbl01_PermSrvcIgnore]![Server]) AND
((tbl01_Services.Service)=[Service Name]) =
((tbl01_PermSrvcsIgnore.Type)=[forms]![ProcessErr]![cmbShutType]));"

strSQL2 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & _
"WHERE (((tbl01_PermSrvcsIgnore.Server) Like ""ALL"") AND
((tbl01_Services.Type) = [forms]![ProcessErr]![cmbShutType]) AND
((tbl01_Services.Service) = [Service Name]) AND ((tbl01_Services.ImpDate) =
[forms]![ProcessErr]![cmbImpDate]));"

Any assistance would be greatly appreciated...

Cheers,
GT.

 

[hennie]

Hi,

I am not sure if you are running the second sql based on a query. I find
every time I do it and try to run an sql or query with code I get the same
the same message. I know there is a more correct way of doing it but I find
running a make table query and then run a query based on the resulting table
solves the problem.

Hennie

Hi Jerry,

Thanks for your reply, maybe I should be doing this in VBA. What I am
trying to acheive is this:

I have one table (tbl01_Services) with a list of services that are
imported
from a text file, and an unrelated table (tbl01_PermSrvcsIgnore) that
contains a list of services that can be ignored (ie. set the [valid] field
in
tbl01_services to 0 if it finds a match in tbl01_PermSrvcsIgnore). In
the
tbl01_PermSrvcsIgnore, the field 'Server' can have an individual Server
name,
or 'ALL'.

I run these two queries when data is first imported and they work fine:

' In the Services Table, set all valid to 0 for entries marked 'ALL' in
the
AddPermNoMon table
strSQL4 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & vbCrLf & _
"WHERE (((tbl01_PermSrvcsIgnore.Server) Like ""ALL"") AND
((tbl01_Services.Service) Like [Service Name]) AND
((tbl01_Services.ImpDate)
Is Null));"

' In the Services Table, set all valid to 0 for entries marked for
specific
Servers.
' in the AddPermNoMon table
strSQL5 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & vbclrf & _
"WHERE (((tbl01_Services.Server) Like [tbl01_PermSrvcsIgnore]![Server])
AND
((tbl01_Services.Service) Like [tbl01_PermSrvcsIgnore]![Service Name]) AND
((tbl01_Services.ImpDate) Is Null));"

Of the two SQL statements above, one updates services found with server
status of 'ALL', and the other updates individual server names, and both
queries work with any records that have not yet been datestamped (ie.
((tbl01_Services.ImpDate) Is Null));

Now what I am trying to acheive is to do the same thing as above (after
records have been imported) and after the records have been datestamped.

I am also trying to factor in which type of records I want to update (ie
the
SQL below i have included the ImpDate and the Type).

The ImpDate is the datestamp, and the Type field can be PROD or DEV.

The ImpDate and Type do not matter in the above SQL because I can compare
all records with no date stamp. However they do matter below.

I thought it would be a simple case (it was 03:00 am ) of just changing:
(tbl01_Services.ImpDate) Is Null to (tbl01_Services.ImpDate) =
Forms![ProcessErr]![cmbImpDate]

And adding:

((tbl01_PermSrvcsIgnore.Type)=[forms]![ProcessErr]![cmbShutType]))

But when I do this, I get the two few parameters error.

Why does it work fine in the first SQL, but bomb in the second?

You are attempting to update two tables at the same time. It's very
unlikely
that you can do this. Instead you need to update one table then the
other.

Also you won't be able to use a direct joing on the two tables in the
Where
clause. You'll probably need a subquery using an In or Exists clause.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Can anyone advise why I am getting a 'Too few parameters - Expected 2'
with
these two SQL statement?

strSQL1 = "UPDATE tbl01_PermSrvcsIgnore, tbl01_Services SET
tbl01_Services.Valid = 0 " & _
"WHERE (((tbl01_Services.Server)=[tbl01_PermSrvcIgnore]![Server]) AND
((tbl01_Services.Service)=[Service Name]) =
((tbl01_PermSrvcsIgnore.Type)=[forms]![ProcessErr]![cmbShutType]));"

strSQL2 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & _
"WHERE (((tbl01_PermSrvcsIgnore.Server) Like ""ALL"") AND
((tbl01_Services.Type) = [forms]![ProcessErr]![cmbShutType]) AND
((tbl01_Services.Service) = [Service Name]) AND
((tbl01_Services.ImpDate) =
[forms]![ProcessErr]![cmbImpDate]));"

Any assistance would be greatly appreciated...

Cheers,
GT.

 

[GLT]

Hi Hennie,

Thanks for your response - sorry Im a bit lost, are you talking about the
1st SQL that I posted or the second? What data do you copy into the make
table query?

Cheers

Hi,

I am not sure if you are running the second sql based on a query. I find
every time I do it and try to run an sql or query with code I get the same
the same message. I know there is a more correct way of doing it but I find
running a make table query and then run a query based on the resulting table
solves the problem.

Hennie

Hi Jerry,

Thanks for your reply, maybe I should be doing this in VBA. What I am
trying to acheive is this:

I have one table (tbl01_Services) with a list of services that are
imported
from a text file, and an unrelated table (tbl01_PermSrvcsIgnore) that
contains a list of services that can be ignored (ie. set the [valid] field
in
tbl01_services to 0 if it finds a match in tbl01_PermSrvcsIgnore). In
the
tbl01_PermSrvcsIgnore, the field 'Server' can have an individual Server
name,
or 'ALL'.

I run these two queries when data is first imported and they work fine:

' In the Services Table, set all valid to 0 for entries marked 'ALL' in
the
AddPermNoMon table
strSQL4 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & vbCrLf & _
"WHERE (((tbl01_PermSrvcsIgnore.Server) Like ""ALL"") AND
((tbl01_Services.Service) Like [Service Name]) AND
((tbl01_Services.ImpDate)
Is Null));"

' In the Services Table, set all valid to 0 for entries marked for
specific
Servers.
' in the AddPermNoMon table
strSQL5 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & vbclrf & _
"WHERE (((tbl01_Services.Server) Like [tbl01_PermSrvcsIgnore]![Server])
AND
((tbl01_Services.Service) Like [tbl01_PermSrvcsIgnore]![Service Name]) AND
((tbl01_Services.ImpDate) Is Null));"

Of the two SQL statements above, one updates services found with server
status of 'ALL', and the other updates individual server names, and both
queries work with any records that have not yet been datestamped (ie.
((tbl01_Services.ImpDate) Is Null));

Now what I am trying to acheive is to do the same thing as above (after
records have been imported) and after the records have been datestamped.

I am also trying to factor in which type of records I want to update (ie
the
SQL below i have included the ImpDate and the Type).

The ImpDate is the datestamp, and the Type field can be PROD or DEV.

The ImpDate and Type do not matter in the above SQL because I can compare
all records with no date stamp. However they do matter below.

I thought it would be a simple case (it was 03:00 am ) of just changing:
(tbl01_Services.ImpDate) Is Null to (tbl01_Services.ImpDate) =
Forms![ProcessErr]![cmbImpDate]

And adding:

((tbl01_PermSrvcsIgnore.Type)=[forms]![ProcessErr]![cmbShutType]))

But when I do this, I get the two few parameters error.

Why does it work fine in the first SQL, but bomb in the second?

You are attempting to update two tables at the same time. It's very
unlikely
that you can do this. Instead you need to update one table then the
other.

Also you won't be able to use a direct joing on the two tables in the
Where
clause. You'll probably need a subquery using an In or Exists clause.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Can anyone advise why I am getting a 'Too few parameters - Expected 2'
with
these two SQL statement?

strSQL1 = "UPDATE tbl01_PermSrvcsIgnore, tbl01_Services SET
tbl01_Services.Valid = 0 " & _
"WHERE (((tbl01_Services.Server)=[tbl01_PermSrvcIgnore]![Server]) AND
((tbl01_Services.Service)=[Service Name]) =
((tbl01_PermSrvcsIgnore.Type)=[forms]![ProcessErr]![cmbShutType]));"

strSQL2 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & _
"WHERE (((tbl01_PermSrvcsIgnore.Server) Like ""ALL"") AND
((tbl01_Services.Type) = [forms]![ProcessErr]![cmbShutType]) AND
((tbl01_Services.Service) = [Service Name]) AND
((tbl01_Services.ImpDate) =
[forms]![ProcessErr]![cmbImpDate]));"

Any assistance would be greatly appreciated...

Cheers,
GT.

.

 

[hennie]

I'm referring to the second one. Is your sql based on a query. In other
words are you running a query from a query. Is the second one depended on
the result of the first query.

If that is the case then change your first query from a select query to a
make table query. Then use that created table as the bases of your second
query.

I find this the easier way to over come that problem as that error does not
seem to happen when based on two tables.

Hennie

Hi Hennie,

Thanks for your response - sorry Im a bit lost, are you talking about the
1st SQL that I posted or the second? What data do you copy into the make
table query?

Cheers

Hi,

I am not sure if you are running the second sql based on a query. I find
every time I do it and try to run an sql or query with code I get the
same
the same message. I know there is a more correct way of doing it but I
find
running a make table query and then run a query based on the resulting
table
solves the problem.

Hennie

Hi Jerry,

Thanks for your reply, maybe I should be doing this in VBA. What I am
trying to acheive is this:

I have one table (tbl01_Services) with a list of services that are
imported
from a text file, and an unrelated table (tbl01_PermSrvcsIgnore) that
contains a list of services that can be ignored (ie. set the [valid]
field
in
tbl01_services to 0 if it finds a match in tbl01_PermSrvcsIgnore). In
the
tbl01_PermSrvcsIgnore, the field 'Server' can have an individual Server
name,
or 'ALL'.

I run these two queries when data is first imported and they work fine:

' In the Services Table, set all valid to 0 for entries marked 'ALL' in
the
AddPermNoMon table
strSQL4 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & vbCrLf & _
"WHERE (((tbl01_PermSrvcsIgnore.Server) Like ""ALL"") AND
((tbl01_Services.Service) Like [Service Name]) AND
((tbl01_Services.ImpDate)
Is Null));"

' In the Services Table, set all valid to 0 for entries marked for
specific
Servers.
' in the AddPermNoMon table
strSQL5 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & vbclrf & _
"WHERE (((tbl01_Services.Server) Like [tbl01_PermSrvcsIgnore]![Server])
AND
((tbl01_Services.Service) Like [tbl01_PermSrvcsIgnore]![Service Name])
AND
((tbl01_Services.ImpDate) Is Null));"

Of the two SQL statements above, one updates services found with server
status of 'ALL', and the other updates individual server names, and
both
queries work with any records that have not yet been datestamped (ie.
((tbl01_Services.ImpDate) Is Null));

Now what I am trying to acheive is to do the same thing as above (after
records have been imported) and after the records have been
datestamped.

I am also trying to factor in which type of records I want to update
(ie
the
SQL below i have included the ImpDate and the Type).

The ImpDate is the datestamp, and the Type field can be PROD or DEV.

The ImpDate and Type do not matter in the above SQL because I can
compare
all records with no date stamp. However they do matter below.

I thought it would be a simple case (it was 03:00 am ) of just
changing:
(tbl01_Services.ImpDate) Is Null to (tbl01_Services.ImpDate) =
Forms![ProcessErr]![cmbImpDate]

And adding:

((tbl01_PermSrvcsIgnore.Type)=[forms]![ProcessErr]![cmbShutType]))

But when I do this, I get the two few parameters error.

Why does it work fine in the first SQL, but bomb in the second?

:

You are attempting to update two tables at the same time. It's very
unlikely
that you can do this. Instead you need to update one table then the
other.

Also you won't be able to use a direct joing on the two tables in the
Where
clause. You'll probably need a subquery using an In or Exists clause.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Can anyone advise why I am getting a 'Too few parameters - Expected
2'
with
these two SQL statement?

strSQL1 = "UPDATE tbl01_PermSrvcsIgnore, tbl01_Services SET
tbl01_Services.Valid = 0 " & _
"WHERE (((tbl01_Services.Server)=[tbl01_PermSrvcIgnore]![Server])
AND
((tbl01_Services.Service)=[Service Name]) =
((tbl01_PermSrvcsIgnore.Type)=[forms]![ProcessErr]![cmbShutType]));"

strSQL2 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & _
"WHERE (((tbl01_PermSrvcsIgnore.Server) Like ""ALL"") AND
((tbl01_Services.Type) = [forms]![ProcessErr]![cmbShutType]) AND
((tbl01_Services.Service) = [Service Name]) AND
((tbl01_Services.ImpDate) =
[forms]![ProcessErr]![cmbImpDate]));"

Any assistance would be greatly appreciated...

Cheers,
GT.

.

 

[GLT]

Hi hennie,

I understand now what you are asking.

These queries are totally seperate and independant of each other (ie one
query does not feed the results of another one), and they are based on
tables, not queries...

Cheers,
Geoff.

I'm referring to the second one. Is your sql based on a query. In other
words are you running a query from a query. Is the second one depended on
the result of the first query.

If that is the case then change your first query from a select query to a
make table query. Then use that created table as the bases of your second
query.

I find this the easier way to over come that problem as that error does not
seem to happen when based on two tables.

Hennie

Hi Hennie,

Thanks for your response - sorry Im a bit lost, are you talking about the
1st SQL that I posted or the second? What data do you copy into the make
table query?

Cheers

Hi,

I am not sure if you are running the second sql based on a query. I find
every time I do it and try to run an sql or query with code I get the
same
the same message. I know there is a more correct way of doing it but I
find
running a make table query and then run a query based on the resulting
table
solves the problem.

Hennie

Hi Jerry,

Thanks for your reply, maybe I should be doing this in VBA. What I am
trying to acheive is this:

I have one table (tbl01_Services) with a list of services that are
imported
from a text file, and an unrelated table (tbl01_PermSrvcsIgnore) that
contains a list of services that can be ignored (ie. set the [valid]
field
in
tbl01_services to 0 if it finds a match in tbl01_PermSrvcsIgnore). In
the
tbl01_PermSrvcsIgnore, the field 'Server' can have an individual Server
name,
or 'ALL'.

I run these two queries when data is first imported and they work fine:

' In the Services Table, set all valid to 0 for entries marked 'ALL' in
the
AddPermNoMon table
strSQL4 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & vbCrLf & _
"WHERE (((tbl01_PermSrvcsIgnore.Server) Like ""ALL"") AND
((tbl01_Services.Service) Like [Service Name]) AND
((tbl01_Services.ImpDate)
Is Null));"

' In the Services Table, set all valid to 0 for entries marked for
specific
Servers.
' in the AddPermNoMon table
strSQL5 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & vbclrf & _
"WHERE (((tbl01_Services.Server) Like [tbl01_PermSrvcsIgnore]![Server])
AND
((tbl01_Services.Service) Like [tbl01_PermSrvcsIgnore]![Service Name])
AND
((tbl01_Services.ImpDate) Is Null));"

Of the two SQL statements above, one updates services found with server
status of 'ALL', and the other updates individual server names, and
both
queries work with any records that have not yet been datestamped (ie.
((tbl01_Services.ImpDate) Is Null));

Now what I am trying to acheive is to do the same thing as above (after
records have been imported) and after the records have been
datestamped.

I am also trying to factor in which type of records I want to update
(ie
the
SQL below i have included the ImpDate and the Type).

The ImpDate is the datestamp, and the Type field can be PROD or DEV.

The ImpDate and Type do not matter in the above SQL because I can
compare
all records with no date stamp. However they do matter below.

I thought it would be a simple case (it was 03:00 am ) of just
changing:
(tbl01_Services.ImpDate) Is Null to (tbl01_Services.ImpDate) =
Forms![ProcessErr]![cmbImpDate]

And adding:

((tbl01_PermSrvcsIgnore.Type)=[forms]![ProcessErr]![cmbShutType]))

But when I do this, I get the two few parameters error.

Why does it work fine in the first SQL, but bomb in the second?

:

You are attempting to update two tables at the same time. It's very
unlikely
that you can do this. Instead you need to update one table then the
other.

Also you won't be able to use a direct joing on the two tables in the
Where
clause. You'll probably need a subquery using an In or Exists clause.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Can anyone advise why I am getting a 'Too few parameters - Expected
2'
with
these two SQL statement?

strSQL1 = "UPDATE tbl01_PermSrvcsIgnore, tbl01_Services SET
tbl01_Services.Valid = 0 " & _
"WHERE (((tbl01_Services.Server)=[tbl01_PermSrvcIgnore]![Server])
AND
((tbl01_Services.Service)=[Service Name]) =
((tbl01_PermSrvcsIgnore.Type)=[forms]![ProcessErr]![cmbShutType]));"

strSQL2 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & _
"WHERE (((tbl01_PermSrvcsIgnore.Server) Like ""ALL"") AND
((tbl01_Services.Type) = [forms]![ProcessErr]![cmbShutType]) AND
((tbl01_Services.Service) = [Service Name]) AND
((tbl01_Services.ImpDate) =
[forms]![ProcessErr]![cmbImpDate]));"

Any assistance would be greatly appreciated...

Cheers,
GT.

.

.

 

[John Spencer]

Too few parameters indicates that the SQL engine cannot decipher something in
your queries. Since you are building SQL strings I am going to guess that you
are trying to execute the SQL in your procedure.

The Jet Expression service will translate the SQL and take care of getting
values of control references and vba expressions. If you build the SQL string
in VBA, then you need to get the values and place them in the string.

Also, I think you have an error in the queries. I can't quite decipher your
where clause it seems to have an equals sign where there should be an " and "

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Bottom line is I think that you need to rewrite your SQL to read more like the
following:
strSQL1 = "UPDATE tbl01_PermSrvcsIgnore, tbl01_Services" & _
" SET tbl01_Services.Valid = 0 " & _
" WHERE tbl01_Services.Server=tbl01_PermSrvcIgnore]![Server]" & _
" AND tbl01_Services.Service=[Service Name] " & _
" AND tbl01_PermSrvcsIgnore.Type=""" & [forms]![ProcessErr]![cmbShutType] & """"

Is Service Name the name of a field or is it supposed to be a parameter
prompt? It it is a parameter prompt, then you need to get the value from
somewhere and then put it into the SQL string you are building.

strSQL2 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore " & _
" SET tbl01_Services.Valid = 0 " & _
" WHERE tbl01_PermSrvcsIgnore.Server Like ""ALL"") " & _
" AND tbl01_Services.Type =""" & [forms]![ProcessErr]![cmbShutType] & _
""" AND tbl01_Services.Service = [Service Name]" & _
" AND tbl01_Services.ImpDate = " & _
Format([forms]![ProcessErr]![cmbImpDate],"\#yyyy-mm-dd\#")

strSQL2 = "UPDATE tbl01_Services INNER JOIN tbl01_PermSrvcsIgnore " & _
" SET tbl01_Services.Valid = 0 " & _
" WHERE tbl01_PermSrvcsIgnore.Server Like ""ALL"") " & _
" AND tbl01_Services.Type =""" & [forms]![ProcessErr]![cmbShutType] & _
""" AND tbl01_Services.Service = [Service Name]" & _
" AND tbl01_Services.ImpDate = " & _
Format([forms]![ProcessErr]![cmbImpDate],"\#yyyy-mm-dd\#")


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Can anyone advise why I am getting a 'Too few parameters - Expected 2' with
these two SQL statement?

strSQL1 = "UPDATE tbl01_PermSrvcsIgnore, tbl01_Services SET
tbl01_Services.Valid = 0 " & _
"WHERE (((tbl01_Services.Server)=[tbl01_PermSrvcIgnore]![Server]) AND
((tbl01_Services.Service)=[Service Name]) =
((tbl01_PermSrvcsIgnore.Type)=[forms]![ProcessErr]![cmbShutType]));"

strSQL2 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & _
"WHERE (((tbl01_PermSrvcsIgnore.Server) Like ""ALL"") AND
((tbl01_Services.Type) = [forms]![ProcessErr]![cmbShutType]) AND
((tbl01_Services.Service) = [Service Name]) AND ((tbl01_Services.ImpDate) =
[forms]![ProcessErr]![cmbImpDate]));"

Any assistance would be greatly appreciated...

Cheers,
GT.

 

[vanderghast]

If you are using the User Interface and it works fine there, then, you are
probably using CurrentDb.Execute when it fails with the error you
mentioned. Try using DoCmd.RunSQL instead.

DoCmd.RunSQL, the User Interface, Dxxx functions (DLookup, DSum, ... ),
Recordset Control Source (for forms and for combo/listbox lists) all
recognize the syntax FORMS!formName!ControlName, but...

CurrentDb.Execute does not AUTOMATICALLY solves this parameter for you. If
you have to use CurrentDb, either embed the value directly in the string
(with the proper delimiter, also care about Null and string having the
delimiter you intend to use, such as a string like 4' 5" 7/8 as example,
containing already ' and ", potential delimiter) ; either open a querydef
object, fills all its parameter.Value from its Parameters collection with
appropriate value, and THEN use that querydef object instead of CurrentDb
(to open a recordset, let say as example).


Vanderghast, Access MVP

 

[GLT]

Hi Vanderghast and John,

Thanks for your replies - I do beleive I was using the CurrentDb.Execute
command and changed it back to DoCmd.RunSQL. I decided rather than to perorm
these updates in one query, to break the processing it up using a few queries
and temp tables, as per hemmies advice earlier.

Here is what my code looks like now, and it works great:

Private Sub btnUpdateData_Click()

On Error GoTo btnUpdateData_Click_Err

Dim dbsTest As DAO.Database
Dim rstTest, rsTest1, rsTest2 As DAO.Recordset
Dim strSQL0, strSQL, strSQL, strSQL1, strSQL2, strSQL4 As String

DoCmd.SetWarnings False

' Flush all valid field back to 1

strSQL0 = "UPDATE tbl01_Services SET tbl01_Services.Valid = 1,
tbl01_Services.ImpDate = [forms]![frm01_ProcessErr]![cmbImpDate],
tbl01_Services.Type = [forms]![ProcessErr]![cmbShutType];"

DoCmd.RunSQL strSQL0


strSQL = "SELECT DISTINCT tbl01_PermSrvcsIgnore.[Service Name] INTO
tbl01_Temp1 " & _
"FROM tbl01_Services, tbl01_PermSrvcsIgnore " & _
"WHERE (((tbl01_PermSrvcsIgnore.Server)=""ALL""));"

strSQL1 = "SELECT DISTINCT tbl01_Services.ID, tbl01_Services.Valid,
tbl01_Services.Server, tbl01_Services.Service, " & _
"tbl01_Services.ImpDate, tbl01_Services.Type INTO tbl01_Temp2 " & _
"FROM tbl01_Temp1 LEFT JOIN tbl01_Services ON tbl01_Temp1.[Service
Name] = tbl01_Services.Service " & _
"WHERE
(((tbl01_Services.ImpDate)=[Forms]![ProcessErr]![cmbImpDate]) " & _
"AND ((tbl01_Services.Type)=[Forms]![ProcessErr]![cmbShutType]));"

strSQL2 = "UPDATE tbl01_Services INNER JOIN tbl01_Temp2 ON tbl01_Services.ID
= tbl01_Temp2.ID SET tbl01_Services.Valid = 0;"


strSQL4 = "SELECT DISTINCT tbl01_Services.ID,tbl01_Services.Valid,
tbl01_Services.Server, tbl01_Services.Service, " & _
"tbl01_Services.ImpDate, tbl01_Services.Type INTO tbl01_Temp3 " & _
"FROM tbl01_PermSrvcsIgnore LEFT JOIN tbl01_Services " & _
"ON tbl01_PermSrvcsIgnore.Server = tbl01_Services.Server " & _
"WHERE
(((tbl01_Services.ImpDate)=[Forms]![ProcessErr]![cmbImpDate]) " & _
"AND ((tbl01_Services.Type)=[Forms]![ProcessErr]![cmbShutType]));"

strSQL5 = "UPDATE tbl01_Services INNER JOIN tbl01_Temp3 ON tbl01_Services.ID
= tbl01_Temp3.ID SET tbl01_Services.Valid = 0;"


DoCmd.RunSQL strSQL
DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
DoCmd.RunSQL strSQL4
DoCmd.RunSQL strSQL5

' Remove Temp Tables:

CurrentDb.Execute "DROP TABLE tbl01_Temp1"
CurrentDb.Execute "DROP TABLE tbl01_Temp2"
CurrentDb.Execute "DROP TABLE tbl01_Temp3"

DoCmd.SetWarnings True

btnUpdateData_Click_Exit:
Exit Sub
btnUpdateData_Click_Err:
MsgBox Error
Resume btnUpdateData_Click_Exit

End Sub

If you are using the User Interface and it works fine there, then, you are
probably using CurrentDb.Execute when it fails with the error you
mentioned. Try using DoCmd.RunSQL instead.

DoCmd.RunSQL, the User Interface, Dxxx functions (DLookup, DSum, ... ),
Recordset Control Source (for forms and for combo/listbox lists) all
recognize the syntax FORMS!formName!ControlName, but...

CurrentDb.Execute does not AUTOMATICALLY solves this parameter for you. If
you have to use CurrentDb, either embed the value directly in the string
(with the proper delimiter, also care about Null and string having the
delimiter you intend to use, such as a string like 4' 5" 7/8 as example,
containing already ' and ", potential delimiter) ; either open a querydef
object, fills all its parameter.Value from its Parameters collection with
appropriate value, and THEN use that querydef object instead of CurrentDb
(to open a recordset, let say as example).


Vanderghast, Access MVP

Can anyone advise why I am getting a 'Too few parameters - Expected 2'
with
these two SQL statement?

strSQL1 = "UPDATE tbl01_PermSrvcsIgnore, tbl01_Services SET
tbl01_Services.Valid = 0 " & _
"WHERE (((tbl01_Services.Server)=[tbl01_PermSrvcIgnore]![Server]) AND
((tbl01_Services.Service)=[Service Name]) =
((tbl01_PermSrvcsIgnore.Type)=[forms]![ProcessErr]![cmbShutType]));"

strSQL2 = "UPDATE tbl01_Services, tbl01_PermSrvcsIgnore SET
tbl01_Services.Valid = 0 " & _
"WHERE (((tbl01_PermSrvcsIgnore.Server) Like ""ALL"") AND
((tbl01_Services.Type) = [forms]![ProcessErr]![cmbShutType]) AND
((tbl01_Services.Service) = [Service Name]) AND ((tbl01_Services.ImpDate)
=
[forms]![ProcessErr]![cmbImpDate]));"

Any assistance would be greatly appreciated...

Cheers,
GT.