Too few parameters?

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


0
PayeDoc
12/16/2009 12:40:40 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

9 Replies
1473 Views

Similar Articles

[PageSpeed] 1

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" <enquiries@gppayroll.org.uk> wrote in message 
news:%23i3CExkfKHA.5568@TK2MSFTNGP02.phx.gbl...
> 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
>
> 

0
vanderghast
12/16/2009 2:10:25 PM
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" <vanderghast@com> wrote in message
news:2FDEF663-A443-48B6-A484-7516141D6BB0@microsoft.com...
> 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" <enquiries@gppayroll.org.uk> wrote in message
> news:%23i3CExkfKHA.5568@TK2MSFTNGP02.phx.gbl...
> > 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
> >
> >
>


0
PayeDoc
12/16/2009 3:16:56 PM
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" wrote:

> 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
> 
> 
> .
> 
0
Utf
12/16/2009 6:07:01 PM
As noted by Dale, it seems you are using a control on a subform. Dale's 
solution should work.

Vanderghast, Access MVP


"PayeDoc" <enquiries@gppayroll.org.uk> wrote in message 
news:ewu4ZImfKHA.4592@TK2MSFTNGP06.phx.gbl...
> 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" <vanderghast@com> wrote in message
> news:2FDEF663-A443-48B6-A484-7516141D6BB0@microsoft.com...
>> 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" <enquiries@gppayroll.org.uk> wrote in message
>> news:%23i3CExkfKHA.5568@TK2MSFTNGP02.phx.gbl...
>> > 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
>> >
>> >
>>
>
> 

0
vanderghast
12/16/2009 6:48:12 PM
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


"PayeDoc" <enquiries@gppayroll.org.uk> wrote in message 
news:%23i3CExkfKHA.5568@TK2MSFTNGP02.phx.gbl...
> 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
>
> 


0
Leslie
12/16/2009 8:23:25 PM
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" <dale.fye@nospam.com> wrote in message
news:108A3197-0F2C-4E63-AF53-E35ABBF39533@microsoft.com...
> 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" wrote:
>
> > 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
> >
> >
> > .
> >


0
PayeDoc
12/17/2009 10:16:56 AM
On Dec 17, 4:16=A0am, "PayeDoc" <enquir...@gppayroll.org.uk> wrote:
> 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 sti=
ll
> get this message! In your suggested syntax below I think there needs to b=
e
> an extra open-bracket imediately after the initial HAVING (to pair with t=
he
> close-bracket after ([x confirmed].entered), but even with this I still g=
et
> 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 ge=
t a
> message that access can't find the field 'Form' in the expression! I trie=
d
> changing it to [Forms], but got the same result.
>
> The syntax I now have is:
> strSQL =3D strSQL & "HAVING ((([x confirmed].entered)=3D " & "#" &
> [Forms]![staffs subform new]![Child813]![Form]![entered] & "#" & ") =A0an=
d
> (([x confirmed].employee)=3D" & Chr(34) & [Forms]![staffs subform
> new]![employee] & Chr(34) & "))"
>
> What am I doing wrong?
> Many thanks for your continued help.
> Les
>
> "Dale Fye" <dale....@nospam.com> wrote in message
>
> news:108A3197-0F2C-4E63-AF53-E35ABBF39533@microsoft.com...
>
>
>
> > PayeDoc,
>
> > I think your problem may have to do with your reference to the control =
on
> > your subform. =A0At least it looks like "child813" is a subform of "sta=
ffs
> > 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 # shoul=
d
> > work. =A0I think it should read something like:
>
> > "HAVING ([x confirmed].entered) =3D#" _
> > =A0 =A0 =A0 =A0 =A0 & =A0[forms]![staffs subform new]![child813]!form.[=
entered] _
> > =A0 =A0 =A0 =A0 =A0 & "#) and (" _
> > =A0 =A0 =A0 =A0 =A0 & [x confirmed].employee=3D" & Chr(34) _
> > =A0 =A0 =A0 =A0 =A0 & [Forms]![staffs subform new]![employee] & Chr(34)=
 & ")"
>
> > ----
> > HTH
> > Dale
>
> > "PayeDoc" wrote:
>
> > > Hello All
>
> > > I have SELECT statement that I have built using the SQL view of a que=
ry.
> 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)=3D" & Chr(34) & [Forms]![staffs subfor=
m
> > > new]![employee] & Chr(34) & "))"
>
> > > but this does not:
>
> > > "HAVING ((([x confirmed].entered) =3D" & Chr(34) & =A0[forms]![staffs
> subform
> > > new]![child813]![entered] & Chr(34) & ") and (([x confirmed].employee=
)=3D"
> &
> > > Chr(34) & [Forms]![staffs subform new]![employee] & Chr(34) & "))"
>
> > > and nor does this:
> > > "HAVING ((([x confirmed].entered) =3D [forms]![staffs subform
> > > new]![child813]![entered]) and (([x confirmed].employee)=3D" & 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" err=
or.
> 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 ne=
ver
> > > altered. The query I use to help me with the SQL syntax has no proble=
m
> with
> > > setting the [entered] parameter as criteria - that's how I got the fi=
rst
> > > failed effort above!
>
> > > Hope someone can help.
> > > Many thanks
> > > Leslie Isaacs
>
> > > .- Hide quoted text -
>
> - Show quoted text -

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.
0
mcescher
12/17/2009 1:51:52 PM
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




"mcescher" <chris.meinders@gmail.com> wrote in message
news:29347488-31ea-45b7-b400-3b4c6bf5e7e2@k19g2000yqc.googlegroups.com...
On Dec 17, 4:16 am, "PayeDoc" <enquir...@gppayroll.org.uk> wrote:
> 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" <dale....@nospam.com> wrote in message
>
> news:108A3197-0F2C-4E63-AF53-E35ABBF39533@microsoft.com...
>
>
>
> > 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" wrote:
>
> > > 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 -
>
> - Show quoted text -

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.


0
PayeDoc
12/18/2009 3:47:36 PM
I don't know about the rest of your SQL, but it looks like you have an extra
'parameter' in there and you have a '!' where you should have a '.'  The
format for referencing a subform control is:

Forms![main form name]![subform control name].Form![control name]

At least that is the format shown on the following link:
http://support.microsoft.com/kb/113352

You show the following:

[Forms]![staffs]!subform new]![Child813]![Form]![entered]

I don't know what is what on your form, but if, for example, the main form is
called 'staffs' and the subform is called 'subform new' and the control is
called 'entered' then it should be

Forms![staffs]![subform new].Form![entered]



PayeDoc wrote:
>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
>
>On Dec 17, 4:16 am, "PayeDoc" <enquir...@gppayroll.org.uk> wrote:
>> Dale and Vanderghast
>>
>[quoted text clipped - 106 lines]
>>
>> - Show quoted text -
>
>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.

-- 
Jim Burke

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200912/1

0
JimBurke
12/18/2009 7:56:21 PM
Reply:

Similar Artilces:

Crm 4 event log warnings. CRM Parameter Filter
Hi all, I am looking for a solution on the following issue. Any suggestions will be very welcomed! Thank you in advance. Background: We just upgraded MS Crm 3 to Crm 4 Question: Every time when a user tries to save an update of a record. A warning will be logged into Event Viewer --> Application. It happens to all entities. For example: 1. I am doing some updates on the contact form http://localhost:5555/CAMELOT-DEV/sfa/conts/edit.aspx?id={488FF696-4F8C-DE11-AC94-00505698306E}# 2. Click save 3. I will find a warning message in Event Viewer --> Application Exception information: ...

Count if between date parameters
I need create a formula that show how many patients were available to be seen on each day of the month. I would like to make the cut-off for changes of dates as 3pm, meaning that one day is from 3pm - 3pm the next day. e.g. - 12/30/09 available patients would be all those there from 3:01pm on 12/29/09 to 3:00pm on 12/30/09. If they're available for the full day count them only. Using this logic with the data below on 12/30/09 only 2 patients were available to be seen. Name Request DT Res DT P1 12/27/2009 1:26 12/31/2009 16:28 P2 12/27/2009 12:00 12/30/2009 7:20 P3 ...

Query Parameter
I want to supply a parameter to a query to define the from date and to date and retrieve data falling under this from and to date. but, i do not want it to be prompted, as the same criteria is to be used in more than one table. so, i want to store the from date and o date in a temp table and related this to the date field in different tables. thanks You could create a table (tblDateRange) with 2 date fields (datStart and datEnd) and only one record. Then add tblDateRange to your query and don't join it. Set the criteria under your date field to: Between [datStart] and [datEnd] ...

optional method parameter in MFC ActiveX
How do you set a method parameter as optional in an MFC ActiveX control? Kurt "Kurt" <k_nojunk@larimore.net> wrote in message news:%23HK$bCR4DHA.2468@TK2MSFTNGP09.phx.gbl... > How do you set a method parameter as optional in an MFC ActiveX control? > > Kurt Depends, (1) do you mean when you create an activex control yourself, and you wish to make a parameter optional? Or (2) do you want to pass an optional argument to a method of the ActiveX control? Solution (1) : Make it a variant parameter. If the variant is of type VT_ERROR and the value is DISP_E_PARAMNOTFOU...

Too few parameters?
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) & ")...

Printing a Report with Parameters
When printing a report with Parameters. The report will come up on the screen after you enter the parameter. When you then print the report it asks for the parameter again. After we click finish, the following error appears on the screen 'Information is needed before this report can be processed (On Cache Page/Server EIACRM.pgsvr) The report will not print. what server os is your crm web running and also what is your domain controller(s) running? also its possible you have embedded the servername in the report. open the report in crystal designer and go to database-verify dat...

MSN Parameters
I have a free e-mail account at msn.com. I had to install a new hard drive and reinstall my software. What are the parameters to access my e-mail at msn? POP3, HTTP, etc. If POP3 what are the parameters for outgoing and incoming e-mail? Thanks. JustAsking JustAsking wrote: And you are using WHICH version of Outlook? > I have a free e-mail account at msn.com. That doesn't make clear whether you have a classic MSN Hotmail account or a new Windows Live Hotmail account. You sure you didn't get converted to a Windows Live Hotmail account? For HOW LONG have you had the ...

Taking multiple parameters
I am running a query inside my macro that creates a worksheet full of some info. I want users to be able to take the results of one macro, and use it as arguments for another macro. So lets say I run 1 macro, I get 2 user ids as a result, say: 1, 2 Now I want to use these user ids as input parameters. Keep in mind these input parameters might vary. So how do I supply these as parameters to another macro? Pass the calculated or retreived values as parameters to the second macro: Sub Macro1() IDValue1 = "ID 123" IDValue2 = "ID 456" Macro2 IDValue1, IDValue2 End Sub Sub M...

Parameters in waveinproc
Hello. The documentation for WaveInProc, the callback function for waveform audio, defines 5 parameters, including an instance and a handle. Using that definition works in debug but crashes in release mode. Digging around in the assembler reveals that in fact this function (and its sister function for output) needs only two parameters (It does a RET 8 - hence two parameters). The second param usefully points to the relevant wavehdr structure. Fixing this to two parameters produces code that no longer crashes in Release mode. But this all seems very strange - why on earth does the documen...

Problem passing a parameter from a Report to CRM
Hi, I'm trying to show a company forecast in an iFrame, the report runs fine on the server and even through the Reports section in CRM. This is the code I am using on the OnLoad event of the form that the iFrame is in: crmForm.all.new_closedate.FireOnChange(); var url = "http://ruby/reportserver?%2fSales+Reports%2fCompany_Forecast&rs:Command=Render&Close_Date_Filter=" var Close_Date_Filter = crmForm.all.new_closedate.DataValue var param = "&rc:parameters=false" url = url+Close_Date_Filter+param document.all.IFRAME_Forecast.src=url The parameter is se...

How do I create a graph displaying two parameters for one item
My example: Distance verses time for the path that a car travels A1:A10- some time values B1:B10- some distance values select any cell in A1:B10 click on Chart Wizard and select XY chart click OK a few times now you need to do some formatting double click elements of chart and format as needed read a simple Excel book to learn more best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "lavalisa" <lavalisa@discussions.microsoft.com> wrote in message news:89B1537B-7737-4B14-BB07-057F37B466D6@microsoft.com... > My example: Distance verses tim...

Parameter Query
I'm running a simple parameter query and it's working fine, however when I go to load the report based on the query it keeps asking for the parameter over and over again. The main report and sub report are based on the same query, but when I finally enter the parameter enough times only the main report data appears, the sub-report comes up blank. Is there a way a parameter query can run a main report and sub report at the same time? T.Kay wrote: >I'm running a simple parameter query and it's working fine, however when I go >to load the report based on the qu...

user form parameters
Where can I learn how to set up the user form parameters I have a user form with three text boxes but when I click tab I do not know how to control the next text box to go. Then I have a commandbuttom which I expect user to click but would like to be able to skip the buttom and just accept the enter to execute macro. Can I do it? In the Userform design mode, right click the text box and select properties. You will find 3 properties. Tab Index, TabKeyBehaviour and TabStop TabIndex starts at zero for the first stop. Set the Numeric 0 to 2 for each of the 3 textboxes in th...

ObjectDataSource and SqlDataSource parameters
I have a GridView that has both SqlDataSources and ObjectDataSources. I use to only have 3 SqlDataSources and I usually set my parameters in a method where I pass the SqlDataSource as a parameter and set the parameters based on which parameter is passed. *************************************** protected void SetData(SqlDataSource sds) { string exceptionTypes = ""; string jobs = ""; JobBucketList jobsList = new JobBucketList(); // Set up the parameters sds.SelectParameters.Clear(...

could not complete the operation. parameter values are invalid
trying to send an email to a list of 30 people and I get this error. Why ask this in a MS Word fourm? Try the following link to the new Answers forum and click on the Outlook section and ask your question there. http://social.answers.microsoft.com/Forums/en-US/categories Best of luck DeanH "angelfire" wrote: > trying to send an email to a list of 30 people and I get this error. ...

Parameter queries and parameter queries
Hi Don't know if Ive got the title right but I least I know what I mean (I think) I have been doing some parameter queries with combo boxes to choose values,run qureies and the open reports, what I would like to do now is have a form that can run a query from a drop down list something like a contacts db I would like the first box to show all the companies I have in a table, the when I select one I would like only the contacts for that company to be visible in the next drop-down box so I can choose which contact I want and then display their contact information. in a form view where it...

Multi Select List Box as Source for Parameter Query
Let me try to explain exactly what I'm trying to do and see if anyone can help me with this. I have a table in my database, called Skills Table. It's a little complicated to explain what 'skills' are, but suffice it to say each skill in my table has a record source. I therefore created a second table, called Source Table, listing the names of the record sources that each skill could come from. I then linked this table to the Skills Table by Source ID. What I want to do now is create a form with a Multi Select List box, where I can select which sources I want to use for skills...

CRM Parameter Filter
Hi, Is it possible to bypass the Parameter Filter, so i can pass Non-CRM related data via the url to a form? So i can react to it in the OnLoad Event? It worked well on 3.0, but 4.0 runs a parameter filter and throws an error on unknown parameters. On Nov 14, 11:29 am, ckokely <christoph.kok...@ptm-edv.at> wrote: > Hi, > > Is it possible to bypass the Parameter Filter, so i can pass Non-CRM related > data via the url to a form? So i can react to it in the OnLoad Event? It > worked well on 3.0, but 4.0 runs a parameter filter and throws an error on > unknown param...

SQLSiteMapProvider with a parameter problem
I'm using the below code with no problem except when trying to use a parameter. The Parameter is supposed to be passed in the BuildSiteMap method....but it's not. I've ran it through SQL Profiler and the parameter isn't even showing up. Can anyone tell me what I may be doing wrong? Thanks ///////////////////////////////////////////////////////////////////////////// Web.Config ///////////////////////////////////////////////////////////////////////////// <siteMap defaultProvider="SqlSiteMapProvider"> <providers> <add name=&...

Reference page header to query parameters
How do you reference a page header in a report to return the parameter that you selected in your query? For example, I have two calculated fields in my final query based on two other queries. The two other queries contain the parameters. The parameters are for month, year and quarter. So if I answer my parameters like: Month1: 2 Year1: 2010 Qtr1: 1 Month2: 1 Year2: 2010 Qtr: 1 I want my report headers to reference my selections and return in my report’s page header: Feb 2010 Qtr1 Jan 2010 Qt1 I’ll be so incredibly relieved if I can get this to work. I’ve wast...

Select range item to add up to a (parameter) total
I have a list of data (A1:B15) that contains a product (A) and amount (B). I need a formula or other method that will determine what product(s) will add up to a given total---the total is a parameter. For example: banana, $1.00 apple, $0.50 pear, $2.00 grapes, $3.00 Total (Parameter): $5 Answer: pear, $2.00 grapes, $5.00 Note that an item cannot be counted more than once. ...

too few parameters
Microsoft Excel 2002 SP-2, Microsoft Windows XP professional I have my queries written in MS Access, and I pull the results into Excel via 'import external data'...'New database query'. My queries run fine in Access, and it works ok for most of the stuff I pull into Excel, but sometimes when I try to pull the results in Excel, I get an error that says "too few parameters expected". The error occurs at the very last step in the 'get external data' procedure, as Excel tries to connect to the datasource. This seems to occur most often when I have a union...

parameter postcode
hi, erm i was wondering if some one can give me the criteria for the following: basically i have a form with a field called postcode and when i enter a part of the postcode i want the query to display all properties with the full postcode? e.g i enter e7 i want the query to display all properties that are e7 5dl, e7 6hf, e7 3ed etc. so when half a postcode is entered i want full postcode to be included in the search? thank you. Use this as criteria -- Like [Enter first part of PostCode] & "*" -- KARL DEWEY Build a little - Test a little "T3E" wrote: > ...

This or that parameters?
Hi, I have a report that includes two different date fields. My users want to be able to select a date range for either field. For example we have a referral date and an admit date. They want to be able to run this report for either a referral date range or an admit date range but not both. How could I set this up in reporting services? The default is for parameters to work in a logical AND configuration, like, referral date AND payer. And I've looked at castcading parameters but I don't see a way to perform an OR logical selection. Any ideas? Examples would ...

passing parameters to Crystal 07-01-03
Is it possible to programmatically pass parameters to a crystal report? say a quote id... If so, how is this done? Any insight would be greatly appreciated. Thanks, Justin Nope... Beautiful... eh? Rob Schrader FrontLine Technology "justin" <justinda@invision.net> wrote in message news:063e01c33fdf$10f73840$a001280a@phx.gbl... > > Is it possible to programmatically pass parameters to a > crystal report? say a quote id... > > If so, how is this done? > > Any insight would be greatly appreciated. > > > Thanks, > Justin You're...