Prev: syntax for count text field
Next: Blank page in middle of report
From: esee on 22 Mar 2010 12:24
In my query builder, I'm getting the message:
"The string returned by the builder is too long. The result will be
truncated."

I think I understand why, as I am using a form to specify a large
number of options for the report.

Can I use VB to specify these criteria instead of the query builder?

Examples in query builder:
If [Forms]![boxSeatsMailings]![PW] Criteria =1 and (PFDirect>0 or
PFBulk>0 or PIDirect>0) then select record.
If [Forms]![boxSeatsMailings]![PW] Criteria =2 and (PFDirect>0 or
PFBulk>0 or PIDirect>0) then select record.

As indicated, the above is just a example. I'd like to do something
similar in VB, but where does the code go (how does it get called)?
From: Marshall Barton on 22 Mar 2010 12:58
esee wrote:

>In my query builder, I'm getting the message:
>"The string returned by the builder is too long. The result will be
>truncated."
>
>I think I understand why, as I am using a form to specify a large
>number of options for the report.
>
>Can I use VB to specify these criteria instead of the query builder?
>
>Examples in query builder:
>If [Forms]![boxSeatsMailings]![PW] Criteria =1 and (PFDirect>0 or
>PFBulk>0 or PIDirect>0) then select record.
>If [Forms]![boxSeatsMailings]![PW] Criteria =2 and (PFDirect>0 or
>PFBulk>0 or PIDirect>0) then select record.
>
>As indicated, the above is just a example. I'd like to do something
>similar in VB, but where does the code go (how does it get called)?


That's too vague for me to answer, but before going into
more specific details, take a look at
http://allenbrowne.com/ser-62.html
for an example of using code ti create a filter/criteria
string in code.

--
Marsh
MVP [MS Access]
From: esee on 22 Mar 2010 13:38
On Mar 22, 10:58 am, Marshall Barton <marshbar...(a)wowway.com> wrote:
> esee wrote:
> >In my query builder, I'm getting the message:
> >"The string returned by the builder is too long.  The result will be
> >truncated."
>
> >I think I understand why, as I am using a form to specify a large
> >number of options for the report.
>
> >Can I use VB to specify these criteria instead of the query builder?
>
> >Examples in query builder:
> >If [Forms]![boxSeatsMailings]![PW] Criteria =1 and (PFDirect>0 or
> >PFBulk>0 or PIDirect>0) then select record.
> >If [Forms]![boxSeatsMailings]![PW] Criteria =2 and (PFDirect>0 or
> >PFBulk>0 or PIDirect>0) then select record.
>
> >As indicated, the above is just a example.  I'd like to do something
> >similar in VB, but where does the code go (how does it get called)?
>
> That's too vague for me to answer, but before going into
> more specific details, take a look at
>        http://allenbrowne.com/ser-62.html
> for an example of using code ti create a filter/criteria
> string in code.
>
> --
> Marsh
> MVP [MS Access]

Thanks for responding Marsh
Keep in mind, that I'm using a Form to decide what to print on my
report. You example may address that, but I don't see how.

I have included a very small subset of my Query Builder code in hopes
that it will help you to answer my question...How and where do I do
this same thing with VB code. What procedure do I put it in to
"Filter" the reports as necessary.

SELECT DISTINCTROW [Forms]![boxSeatsMailings]![DC] AS DC1, [Forms]!
[boxSeatsMailings]![Montana] AS Montana, tblComputerAssignments.DBNum
FROM qryTotalPlatformSeats, zForms INNER JOIN (((tblBanks INNER JOIN
tblAddresses ON tblBanks.BankID = tblAddresses.BankID) INNER JOIN
tblContacts ON tblAddresses.AddrID = tblContacts.AddrID) INNER JOIN
tblComputerAssignments ON tblBanks.BankID =
tblComputerAssignments.BankID) ON zForms.FormsID =
tblBanks.SeatContracts
WHERE ((([Forms]![boxSeatsMailings]![DC])=No) AND (([Forms]!
[boxSeatsMailings]![Montana])=Yes)) OR ((([Forms]![boxSeatsMailings]!
[DC])=No) AND (([Forms]![boxSeatsMailings]![Montana])=No) AND
((tblComputerAssignments.DBNum)<>30)) OR ((([Forms]![boxSeatsMailings]!
[DC])=Yes) AND (([Forms]![boxSeatsMailings]![Montana])=Yes)) OR
((([Forms]![boxSeatsMailings]![DC])=Yes) AND (([Forms]!
[boxSeatsMailings]![Montana])=No) AND
((tblComputerAssignments.DBNum)<>30));

If I am still being to vague, please let me know what I can do to
provide more detail.
From: Marshall Barton on 22 Mar 2010 17:26
esee wrote:

>On Mar 22, 10:58�am, Marshall Barton wrote:
>> esee wrote:
>> >In my query builder, I'm getting the message:
>> >"The string returned by the builder is too long. �The result will be
>> >truncated."
>>
>> >I think I understand why, as I am using a form to specify a large
>> >number of options for the report.
>>
>> >Can I use VB to specify these criteria instead of the query builder?
>>
>> >Examples in query builder:
>> >If [Forms]![boxSeatsMailings]![PW] Criteria =1 and (PFDirect>0 or
>> >PFBulk>0 or PIDirect>0) then select record.
>> >If [Forms]![boxSeatsMailings]![PW] Criteria =2 and (PFDirect>0 or
>> >PFBulk>0 or PIDirect>0) then select record.
>>
>> >As indicated, the above is just a example. �I'd like to do something
>> >similar in VB, but where does the code go (how does it get called)?
>>
>> That's too vague for me to answer, but before going into
>> more specific details, take a look at
>> � � � �http://allenbrowne.com/ser-62.html
>> for an example of using code ti create a filter/criteria
>> string in code.
>>
>
>Keep in mind, that I'm using a Form to decide what to print on my
>report. You example may address that, but I don't see how.
>
>I have included a very small subset of my Query Builder code in hopes
>that it will help you to answer my question...How and where do I do
>this same thing with VB code. What procedure do I put it in to
>"Filter" the reports as necessary.
>
>SELECT DISTINCTROW [Forms]![boxSeatsMailings]![DC] AS DC1, [Forms]!
>[boxSeatsMailings]![Montana] AS Montana, tblComputerAssignments.DBNum
>FROM qryTotalPlatformSeats, zForms INNER JOIN (((tblBanks INNER JOIN
>tblAddresses ON tblBanks.BankID = tblAddresses.BankID) INNER JOIN
>tblContacts ON tblAddresses.AddrID = tblContacts.AddrID) INNER JOIN
>tblComputerAssignments ON tblBanks.BankID =
>tblComputerAssignments.BankID) ON zForms.FormsID =
>tblBanks.SeatContracts
>WHERE ((([Forms]![boxSeatsMailings]![DC])=No) AND (([Forms]!
>[boxSeatsMailings]![Montana])=Yes)) OR ((([Forms]![boxSeatsMailings]!
>[DC])=No) AND (([Forms]![boxSeatsMailings]![Montana])=No) AND
>((tblComputerAssignments.DBNum)<>30)) OR ((([Forms]![boxSeatsMailings]!
>[DC])=Yes) AND (([Forms]![boxSeatsMailings]![Montana])=Yes)) OR
>((([Forms]![boxSeatsMailings]![DC])=Yes) AND (([Forms]!
>[boxSeatsMailings]![Montana])=No) AND
>((tblComputerAssignments.DBNum)<>30));
>
>If I am still being to vague, please let me know what I can do to
>provide more detail.

That example is a general outline of the same kind of thing
you are trying to do. I don't see how I can add to that
without just repeating it.


The first thing you should do is simplify the query to
something that works with a few simple criteria. Then
trudge your way through the example using just a couple of
the your form's criteria text boxes to get the structure of
the code working. Then try to expand it one criteria at a
time. If/when you reach a stumbling block, post back with
the code you have that works and a specific question about
the one that's giving you a problem.

--
Marsh
MVP [MS Access]
From: esee on 23 Mar 2010 08:50
On Mar 22, 3:26 pm, Marshall Barton <marshbar...(a)wowway.com> wrote:
> esee wrote:
> >On Mar 22, 10:58 am, Marshall Barton wrote:
> >> esee wrote:
> >> >In my query builder, I'm getting the message:
> >> >"The string returned by the builder is too long.  The result will be
> >> >truncated."
>
> >> >I think I understand why, as I am using a form to specify a large
> >> >number of options for the report.
>
> >> >Can I use VB to specify these criteria instead of the query builder?
>
> >> >Examples in query builder:
> >> >If [Forms]![boxSeatsMailings]![PW] Criteria =1 and (PFDirect>0 or
> >> >PFBulk>0 or PIDirect>0) then select record.
> >> >If [Forms]![boxSeatsMailings]![PW] Criteria =2 and (PFDirect>0 or
> >> >PFBulk>0 or PIDirect>0) then select record.
>
> >> >As indicated, the above is just a example.  I'd like to do something
> >> >similar in VB, but where does the code go (how does it get called)?
>
> >> That's too vague for me to answer, but before going into
> >> more specific details, take a look at
> >>        http://allenbrowne.com/ser-62.html
> >> for an example of using code ti create a filter/criteria
> >> string in code.
>
> >Keep in mind, that I'm using a Form to decide what to print on my
> >report.  You example may address that, but I don't see how.
>
> >I have included a very small subset of my Query Builder code in hopes
> >that it will help you to answer my question...How and where do I do
> >this same thing with VB code.  What procedure do I put it in to
> >"Filter" the reports as necessary.
>
> >SELECT DISTINCTROW [Forms]![boxSeatsMailings]![DC] AS DC1, [Forms]!
> >[boxSeatsMailings]![Montana] AS Montana, tblComputerAssignments.DBNum
> >FROM qryTotalPlatformSeats, zForms INNER JOIN (((tblBanks INNER JOIN
> >tblAddresses ON tblBanks.BankID = tblAddresses.BankID) INNER JOIN
> >tblContacts ON tblAddresses.AddrID = tblContacts.AddrID) INNER JOIN
> >tblComputerAssignments ON tblBanks.BankID =
> >tblComputerAssignments.BankID) ON zForms.FormsID =
> >tblBanks.SeatContracts
> >WHERE ((([Forms]![boxSeatsMailings]![DC])=No) AND (([Forms]!
> >[boxSeatsMailings]![Montana])=Yes)) OR ((([Forms]![boxSeatsMailings]!
> >[DC])=No) AND (([Forms]![boxSeatsMailings]![Montana])=No) AND
> >((tblComputerAssignments.DBNum)<>30)) OR ((([Forms]![boxSeatsMailings]!
> >[DC])=Yes) AND (([Forms]![boxSeatsMailings]![Montana])=Yes)) OR
> >((([Forms]![boxSeatsMailings]![DC])=Yes) AND (([Forms]!
> >[boxSeatsMailings]![Montana])=No) AND
> >((tblComputerAssignments.DBNum)<>30));
>
> >If I am still being to vague, please let me know what I can do to
> >provide more detail.
>
> That example is a general outline of the same kind of thing
> you are trying to do.  I don't see how I can add to that
> without just repeating it.
>
> The first thing you should do is simplify the query to
> something that works with a few simple criteria.  Then
> trudge your way through the example using just a couple of
> the your form's criteria text boxes to get the structure of
> the code working. Then try to expand it one criteria at a
> time.  If/when you reach a stumbling block, post back with
> the code you have that works and a specific question about
> the one that's giving you a problem.
>
> --
> Marsh
> MVP [MS Access]

Okay, thank you...
 | 
Pages: 1
Prev: syntax for count text field
Next: Blank page in middle of report