Too few parameters?

  • Thread starter Thread starter PayeDoc
  • Start date Start date
P

PayeDoc

Hello All

I have SELECT statement that I have built using the SQL view of a query. The
SELECT statement contains WHERE, GROUP BY, HAVING and ORDER BY parameters.
All has been working fine, until today when I have needed to add an extra
criteria to the HAVING part:

This worked:

"HAVING ((([x confirmed].name)=" & Chr(34) & [Forms]![staffs subform
new]![employee] & Chr(34) & "))"

but this does not:

"HAVING ((([x confirmed].entered) =" & Chr(34) & [forms]![staffs subform
new]![child813]![entered] & Chr(34) & ") and (([x confirmed].employee)=" &
Chr(34) & [Forms]![staffs subform new]![employee] & Chr(34) & "))"

and nor does this:
"HAVING ((([x confirmed].entered) = [forms]![staffs subform
new]![child813]![entered]) and (([x confirmed].employee)=" & Chr(34) &
[Forms]![staffs subform new]![employee] & Chr(34) & "))"

The [x confirmed].entered field has been added to the SELECT and the GROUP
BY parts.

I keep getting either a "too few parameters" or a "data mismatch" error. In
case it is relevant (but I can't see that it can be!), [entered] is a
date/time field that defaults to 'Now' for each new record, and is never
altered. The query I use to help me with the SQL syntax has no problem with
setting the [entered] parameter as criteria - that's how I got the first
failed effort above!

Hope someone can help.
Many thanks
Leslie Isaacs
 
If it is a date_time, the delimiter is not Chr(34) but #. And ASSUMING your
setting provides a US format for dates (mm/dd/yyyy), then, try:

"HAVING ((([x confirmed].entered) =" & "#" & [forms]![staffs subform
new]![child813]![entered] & "#" & ") and (([x confirmed].employee)=" &
Chr(34) & [Forms]![staffs subform new]![employee] & Chr(34) & "))"


Note that


"HAVING ((([x confirmed].entered) = [forms]![staffs subform
new]![child813]![entered]) and (([x confirmed].employee) = [Forms]![staffs
subform new]![employee] ))"


without delimiter of any kind, regional setting concern, null presence,
etc., could also work if you use DoCmd, or the (whole SQL) string as record
source BUT NOT if you use CurrentDb, since CurrentDb does not resolve
automatically for you parameters like FORMS!formName!controlName,while DoCmd
does, as Dxxx functions (DLookup, DSum, DCount, ... ) and many
forms/controls properties accepting SQL strings.



Vanderghast, Access MVP
 
Hello Vanderghast

Many thanks for your reply.

OK: I have tried your suggestion below and now I'm getting no record - which
I am fairly confident is because of the date formatting issue. I 'sort of'
understood your comments about the possibility of not using delimeters (and
I would have like to do that, because it seems simpler!), but the line after
the SQL build-up is:
Set recPayroll = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
.... which I guess means that I am using CurrentDb, which if I have
understood you correctly means I need the delimeters.

So, how do I match the date formats between the field on the subform that I
want to use as criteria, and the field in the SQL statement? I tried setting
the format of the field on the subform to mm/dd/yyyy hh:nn:ss, but still got
no record. I also tried just mm/dd/yyyy, but still no good. Did I get the
format wrong? Might it be better to convert the [entered] field on the
subform to some numerical or string value that would be independent of the
formatting issue - so having (say) something like:

"HAVING (((Str([x confirmed].entered)) =Str([forms]![staffs subform
new]![child813]![entered])) and (([x confirmed].employee)=" & Chr(34) &
[Forms]![staffs subform new]![employee] & Chr(34) & "))"

Needless to say - this doesn't work: but am I on the right lines?!
I also tried 'hard-formatting' the dates:
"HAVING ((format(([x confirmed].entered),""dd/mm/yyyy"") =
format([Forms]![staffs subform new]![Child813]![entered],""dd/mm/yyyy""))
and (([x confirmed].name)=" & Chr(34) & [Forms]![staffs subform new]![name]
& Chr(34) & "))"
Again, needless to say, ....!

Thanks again for your help.
Les




vanderghast said:
If it is a date_time, the delimiter is not Chr(34) but #. And ASSUMING your
setting provides a US format for dates (mm/dd/yyyy), then, try:

"HAVING ((([x confirmed].entered) =" & "#" & [forms]![staffs subform
new]![child813]![entered] & "#" & ") and (([x confirmed].employee)=" &
Chr(34) & [Forms]![staffs subform new]![employee] & Chr(34) & "))"


Note that


"HAVING ((([x confirmed].entered) = [forms]![staffs subform
new]![child813]![entered]) and (([x confirmed].employee) = [Forms]![staffs
subform new]![employee] ))"


without delimiter of any kind, regional setting concern, null presence,
etc., could also work if you use DoCmd, or the (whole SQL) string as record
source BUT NOT if you use CurrentDb, since CurrentDb does not resolve
automatically for you parameters like FORMS!formName!controlName,while DoCmd
does, as Dxxx functions (DLookup, DSum, DCount, ... ) and many
forms/controls properties accepting SQL strings.



Vanderghast, Access MVP



PayeDoc said:
Hello All

I have SELECT statement that I have built using the SQL view of a query.
The
SELECT statement contains WHERE, GROUP BY, HAVING and ORDER BY parameters.
All has been working fine, until today when I have needed to add an extra
criteria to the HAVING part:

This worked:

"HAVING ((([x confirmed].name)=" & Chr(34) & [Forms]![staffs subform
new]![employee] & Chr(34) & "))"

but this does not:

"HAVING ((([x confirmed].entered) =" & Chr(34) & [forms]![staffs subform
new]![child813]![entered] & Chr(34) & ") and (([x confirmed].employee)=" &
Chr(34) & [Forms]![staffs subform new]![employee] & Chr(34) & "))"

and nor does this:
"HAVING ((([x confirmed].entered) = [forms]![staffs subform
new]![child813]![entered]) and (([x confirmed].employee)=" & Chr(34) &
[Forms]![staffs subform new]![employee] & Chr(34) & "))"

The [x confirmed].entered field has been added to the SELECT and the GROUP
BY parts.

I keep getting either a "too few parameters" or a "data mismatch" error.
In
case it is relevant (but I can't see that it can be!), [entered] is a
date/time field that defaults to 'Now' for each new record, and is never
altered. The query I use to help me with the SQL syntax has no problem
with
setting the [entered] parameter as criteria - that's how I got the first
failed effort above!

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
 
PayeDoc,

I think your problem may have to do with your reference to the control on
your subform. At least it looks like "child813" is a subform of "staffs
subform new"

Normally, the syntax for that looks like:

Forms!FormName!SubformName!Form.ControlName

So I think your reference to the Entered control should read:

Forms![staffs subform new]![child813]!form!Entered

That, combined with Michel's suggestion about wrapping dates in # should
work. I think it should read something like:

"HAVING ([x confirmed].entered) =#" _
& [forms]![staffs subform new]![child813]!form.[entered] _
& "#) and (" _
& [x confirmed].employee=" & Chr(34) _
& [Forms]![staffs subform new]![employee] & Chr(34) & ")"
 
As noted by Dale, it seems you are using a control on a subform. Dale's
solution should work.

Vanderghast, Access MVP


PayeDoc said:
Hello Vanderghast

Many thanks for your reply.

OK: I have tried your suggestion below and now I'm getting no record -
which
I am fairly confident is because of the date formatting issue. I 'sort of'
understood your comments about the possibility of not using delimeters
(and
I would have like to do that, because it seems simpler!), but the line
after
the SQL build-up is:
Set recPayroll = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
... which I guess means that I am using CurrentDb, which if I have
understood you correctly means I need the delimeters.

So, how do I match the date formats between the field on the subform that
I
want to use as criteria, and the field in the SQL statement? I tried
setting
the format of the field on the subform to mm/dd/yyyy hh:nn:ss, but still
got
no record. I also tried just mm/dd/yyyy, but still no good. Did I get the
format wrong? Might it be better to convert the [entered] field on the
subform to some numerical or string value that would be independent of the
formatting issue - so having (say) something like:

"HAVING (((Str([x confirmed].entered)) =Str([forms]![staffs subform
new]![child813]![entered])) and (([x confirmed].employee)=" & Chr(34) &
[Forms]![staffs subform new]![employee] & Chr(34) & "))"

Needless to say - this doesn't work: but am I on the right lines?!
I also tried 'hard-formatting' the dates:
"HAVING ((format(([x confirmed].entered),""dd/mm/yyyy"") =
format([Forms]![staffs subform new]![Child813]![entered],""dd/mm/yyyy""))
and (([x confirmed].name)=" & Chr(34) & [Forms]![staffs subform
new]![name]
& Chr(34) & "))"
Again, needless to say, ....!

Thanks again for your help.
Les




vanderghast said:
If it is a date_time, the delimiter is not Chr(34) but #. And ASSUMING your
setting provides a US format for dates (mm/dd/yyyy), then, try:

"HAVING ((([x confirmed].entered) =" & "#" & [forms]![staffs subform
new]![child813]![entered] & "#" & ") and (([x confirmed].employee)=" &
Chr(34) & [Forms]![staffs subform new]![employee] & Chr(34) & "))"


Note that


"HAVING ((([x confirmed].entered) = [forms]![staffs subform
new]![child813]![entered]) and (([x confirmed].employee) = [Forms]![staffs
subform new]![employee] ))"


without delimiter of any kind, regional setting concern, null presence,
etc., could also work if you use DoCmd, or the (whole SQL) string as record
source BUT NOT if you use CurrentDb, since CurrentDb does not resolve
automatically for you parameters like FORMS!formName!controlName,while DoCmd
does, as Dxxx functions (DLookup, DSum, DCount, ... ) and many
forms/controls properties accepting SQL strings.



Vanderghast, Access MVP



PayeDoc said:
Hello All

I have SELECT statement that I have built using the SQL view of a
query.
The
SELECT statement contains WHERE, GROUP BY, HAVING and ORDER BY parameters.
All has been working fine, until today when I have needed to add an extra
criteria to the HAVING part:

This worked:

"HAVING ((([x confirmed].name)=" & Chr(34) & [Forms]![staffs subform
new]![employee] & Chr(34) & "))"

but this does not:

"HAVING ((([x confirmed].entered) =" & Chr(34) & [forms]![staffs subform
new]![child813]![entered] & Chr(34) & ") and (([x
confirmed].employee)=" &
Chr(34) & [Forms]![staffs subform new]![employee] & Chr(34) & "))"

and nor does this:
"HAVING ((([x confirmed].entered) = [forms]![staffs subform
new]![child813]![entered]) and (([x confirmed].employee)=" & Chr(34) &
[Forms]![staffs subform new]![employee] & Chr(34) & "))"

The [x confirmed].entered field has been added to the SELECT and the GROUP
BY parts.

I keep getting either a "too few parameters" or a "data mismatch"
error.
In
case it is relevant (but I can't see that it can be!), [entered] is a
date/time field that defaults to 'Now' for each new record, and is
never
altered. The query I use to help me with the SQL syntax has no problem
with
setting the [entered] parameter as criteria - that's how I got the
first
failed effort above!

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
Dale and Vanderghast

Many thanks for your replies. I will try the amended syntax tomorrow (it's
Tuesday night here now), and will let you know how I get on.
Thanks again
Les
 
Dale and Vanderghast

This is driving me nuts!

Two problems:

1. It seems that whatever bracketing I have at the very end of the HAVING
clause, I keep getting "Compile error: expected: end of statement" at the
end. I have counted the brackets and paired them off, but even then I still
get this message! In your suggested syntax below I think there needs to be
an extra open-bracket imediately after the initial HAVING (to pair with the
close-bracket after ([x confirmed].entered), but even with this I still get
the compile error. I have tried different bracket combinations, but still no
good.

2. When I add [Form] to the syntax for the reference to the subform, I get a
message that access can't find the field 'Form' in the expression! I tried
changing it to [Forms], but got the same result.

The syntax I now have is:
strSQL = strSQL & "HAVING ((([x confirmed].entered)= " & "#" &
[Forms]![staffs subform new]![Child813]![Form]![entered] & "#" & ") and
(([x confirmed].employee)=" & Chr(34) & [Forms]![staffs subform
new]![employee] & Chr(34) & "))"

What am I doing wrong?
Many thanks for your continued help.
Les


Dale Fye said:
PayeDoc,

I think your problem may have to do with your reference to the control on
your subform. At least it looks like "child813" is a subform of "staffs
subform new"

Normally, the syntax for that looks like:

Forms!FormName!SubformName!Form.ControlName

So I think your reference to the Entered control should read:

Forms![staffs subform new]![child813]!form!Entered

That, combined with Michel's suggestion about wrapping dates in # should
work. I think it should read something like:

"HAVING ([x confirmed].entered) =#" _
& [forms]![staffs subform new]![child813]!form.[entered] _
& "#) and (" _
& [x confirmed].employee=" & Chr(34) _
& [Forms]![staffs subform new]![employee] & Chr(34) & ")"


----
HTH
Dale



PayeDoc said:
Hello All

I have SELECT statement that I have built using the SQL view of a query. The
SELECT statement contains WHERE, GROUP BY, HAVING and ORDER BY parameters.
All has been working fine, until today when I have needed to add an extra
criteria to the HAVING part:

This worked:

"HAVING ((([x confirmed].name)=" & Chr(34) & [Forms]![staffs subform
new]![employee] & Chr(34) & "))"

but this does not:

"HAVING ((([x confirmed].entered) =" & Chr(34) & [forms]![staffs subform
new]![child813]![entered] & Chr(34) & ") and (([x confirmed].employee)=" &
Chr(34) & [Forms]![staffs subform new]![employee] & Chr(34) & "))"

and nor does this:
"HAVING ((([x confirmed].entered) = [forms]![staffs subform
new]![child813]![entered]) and (([x confirmed].employee)=" & Chr(34) &
[Forms]![staffs subform new]![employee] & Chr(34) & "))"

The [x confirmed].entered field has been added to the SELECT and the GROUP
BY parts.

I keep getting either a "too few parameters" or a "data mismatch" error. In
case it is relevant (but I can't see that it can be!), [entered] is a
date/time field that defaults to 'Now' for each new record, and is never
altered. The query I use to help me with the SQL syntax has no problem with
setting the [entered] parameter as criteria - that's how I got the first
failed effort above!

Hope someone can help.
Many thanks
Leslie Isaacs


.
Click to expand...
Click to expand...
 
Dale and Vanderghast

This is driving me nuts!

Two problems:

1. It seems that whatever bracketing I have at the very end of the HAVING
clause, I keep getting "Compile error: expected: end of statement" at the
end. I have counted the brackets and paired them off, but even then I still
get this message! In your suggested syntax below I think there needs to be
an extra open-bracket imediately after the initial HAVING (to pair with the
close-bracket after ([x confirmed].entered), but even with this I still get
the compile error. I have tried different bracket combinations, but stillno
good.

2. When I add [Form] to the syntax for the reference to the subform, I get a
message that access can't find the field 'Form' in the expression! I tried
changing it to [Forms], but got the same result.

The syntax I now have is:
strSQL = strSQL & "HAVING ((([x confirmed].entered)= " & "#" &
[Forms]![staffs subform new]![Child813]![Form]![entered] & "#" & ")  and
(([x confirmed].employee)=" & Chr(34) & [Forms]![staffs subform
new]![employee] & Chr(34) & "))"

What am I doing wrong?
Many thanks for your continued help.
Les




PayeDoc,
Click to expand...
I think your problem may have to do with your reference to the control on
your subform.  At least it looks like "child813" is a subform of "staffs
subform new"
Click to expand...
Normally, the syntax for that looks like:

So I think your reference to the Entered control should read:
Click to expand...
Forms![staffs subform new]![child813]!form!Entered
Click to expand...
That, combined with Michel's suggestion about wrapping dates in # should
work.  I think it should read something like:
Click to expand...
"HAVING ([x confirmed].entered) =#" _
          &  [forms]![staffs subform new]![child813]!form.[entered] _
          & "#) and (" _
          & [x confirmed].employee=" & Chr(34) _
          & [Forms]![staffs subform new]![employee] & Chr(34)& ")"
Hello All
I have SELECT statement that I have built using the SQL view of a query. The
SELECT statement contains WHERE, GROUP BY, HAVING and ORDER BY parameters.
All has been working fine, until today when I have needed to add an extra
criteria to the HAVING part:
This worked:
"HAVING ((([x confirmed].name)=" & Chr(34) & [Forms]![staffs subform
new]![employee] & Chr(34) & "))"
but this does not:
"HAVING ((([x confirmed].entered) =" & Chr(34) &  [forms]![staffs subform
new]![child813]![entered] & Chr(34) & ") and (([x confirmed].employee)=" &
Chr(34) & [Forms]![staffs subform new]![employee] & Chr(34) & "))"
and nor does this:
"HAVING ((([x confirmed].entered) = [forms]![staffs subform
new]![child813]![entered]) and (([x confirmed].employee)=" & Chr(34) &
[Forms]![staffs subform new]![employee] & Chr(34) & "))"
The [x confirmed].entered field has been added to the SELECT and the GROUP
BY parts.
I keep getting either a "too few parameters" or a "data mismatch" error. In
case it is relevant (but I can't see that it can be!), [entered] is a
date/time field that defaults to 'Now' for each new record, and is never
altered. The query I use to help me with the SQL syntax has no problem with
setting the [entered] parameter as criteria - that's how I got the first
failed effort above!
Hope someone can help.
Many thanks
Leslie Isaacs
.- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

Have you tried doing a "debug.print strSQL" after you build it? What
are the results? Can you paste this into a regular query and return
records?

Hope this helps,
Chris M.
 
Hello Chris

Many thanks for your suggestion.

Unfortunately I can't even finish building the SQL - I get the message that
access can't find the field 'Form' in the expression. I did initially get
the syntax for the whole statement from a - working - query, but in the
query I didn't need the the [Form] before [entered] in [Forms]![staffs
subform new]![Child813]![Form]![entered].

In fact, I have just tried removing that [Form], and the code ran! I have
tried so many variations of the syntax that I've lost track of them, so now
I don't know the difference between the current version and the original
one. The problem now, however, is that the record being returned by the sql
is not getting updated as different records are selected in the subform: if
I run the sql code and view the record returned, then move to another record
in the subform, then run the sql code again and view the record returned, I
should see a different record - but I don't! I tried doing a refresh after
moving to another record in the subform, by hitting F9, but that just moved
the record in the subform back to the original record!

Hope you can help.
Thanks again
Les




Dale and Vanderghast

This is driving me nuts!

Two problems:

1. It seems that whatever bracketing I have at the very end of the HAVING
clause, I keep getting "Compile error: expected: end of statement" at the
end. I have counted the brackets and paired them off, but even then I still
get this message! In your suggested syntax below I think there needs to be
an extra open-bracket imediately after the initial HAVING (to pair with the
close-bracket after ([x confirmed].entered), but even with this I still get
the compile error. I have tried different bracket combinations, but still no
good.

2. When I add [Form] to the syntax for the reference to the subform, I get a
message that access can't find the field 'Form' in the expression! I tried
changing it to [Forms], but got the same result.

The syntax I now have is:
strSQL = strSQL & "HAVING ((([x confirmed].entered)= " & "#" &
[Forms]![staffs subform new]![Child813]![Form]![entered] & "#" & ") and
(([x confirmed].employee)=" & Chr(34) & [Forms]![staffs subform
new]![employee] & Chr(34) & "))"

What am I doing wrong?
Many thanks for your continued help.
Les




PayeDoc,
Click to expand...
I think your problem may have to do with your reference to the control on
your subform. At least it looks like "child813" is a subform of "staffs
subform new"
Click to expand...
Normally, the syntax for that looks like:

So I think your reference to the Entered control should read:
Click to expand...
Forms![staffs subform new]![child813]!form!Entered
Click to expand...
That, combined with Michel's suggestion about wrapping dates in # should
work. I think it should read something like:
Click to expand...
"HAVING ([x confirmed].entered) =#" _
& [forms]![staffs subform new]![child813]!form.[entered] _
& "#) and (" _
& [x confirmed].employee=" & Chr(34) _
& [Forms]![staffs subform new]![employee] & Chr(34) & ")"
Hello All
I have SELECT statement that I have built using the SQL view of a
Click to expand...
Click to expand...
query.
The
SELECT statement contains WHERE, GROUP BY, HAVING and ORDER BY parameters.
All has been working fine, until today when I have needed to add an extra
criteria to the HAVING part:
This worked:
"HAVING ((([x confirmed].name)=" & Chr(34) & [Forms]![staffs subform
new]![employee] & Chr(34) & "))"
but this does not:
"HAVING ((([x confirmed].entered) =" & Chr(34) & [forms]![staffs subform
new]![child813]![entered] & Chr(34) & ") and (([x
Click to expand...
Click to expand...
confirmed].employee)="
&
Chr(34) & [Forms]![staffs subform new]![employee] & Chr(34) & "))"
and nor does this:
"HAVING ((([x confirmed].entered) = [forms]![staffs subform
new]![child813]![entered]) and (([x confirmed].employee)=" & Chr(34) &
[Forms]![staffs subform new]![employee] & Chr(34) & "))"
The [x confirmed].entered field has been added to the SELECT and the GROUP
BY parts.
I keep getting either a "too few parameters" or a "data mismatch"
Click to expand...
Click to expand...
error.
In
case it is relevant (but I can't see that it can be!), [entered] is a
date/time field that defaults to 'Now' for each new record, and is never
altered. The query I use to help me with the SQL syntax has no problem with
setting the [entered] parameter as criteria - that's how I got the first
failed effort above!
Hope someone can help.
Many thanks
Leslie Isaacs
.- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

Have you tried doing a "debug.print strSQL" after you build it? What
are the results? Can you paste this into a regular query and return
records?

Hope this helps,
Chris M.
 
Back
Top