Using Code instead of Criteria

E

esee

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)?
 
M

Marshall Barton

esee said:
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.
 
E

esee

esee said:
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.

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.
 
M

Marshall Barton

esee said:
esee said:
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.
 
E

esee

esee said:
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.

Okay, thank you...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top