G
Guest
Thanks a lot Steve, that is exactly as I want it
CurtainMary
CurtainMary
G
Guest
Thanks Steve,
I must have missed something before, but after you clearly spelled it out to
me, I finally got it. One other question though if you don't mind.
Do wildcard characters work the same? For example, one of the fields is
jobdesc. Now say I want to run a query on any job desc with the word 'clean'
in it, how would I do that?
Thanks,
Chad
I must have missed something before, but after you clearly spelled it out to
me, I finally got it. One other question though if you don't mind.
Do wildcard characters work the same? For example, one of the fields is
jobdesc. Now say I want to run a query on any job desc with the word 'clean'
in it, how would I do that?
Thanks,
Chad
Steve Schapel said:
P
Powderfinger
Cool!
I like the way you start off with TRUE so you don't have to worry about the
AND at the beginning. I have actually developed a function
(RemovePrefix(strPrefix, strFilter) As String) which removes the AND for me.
I use If Len(Trim(Me.LName)) > 0 instead of IsNull because if the user
happens to leave a couple of spaces in there, it will consider it null.
Curious how you are using CLng() for the dates. Won't this convert the time
associated with the date also? All dates have times associated with them,
although in most cases it's zero which represents 12 midnight I believe. My
point is, say the start date is 2/4/2006 12:01 AM , then your routine will
miss dates of 2/4/2006 12:00 AM
I just don't like imbedding the form controls in the query. First, it makes
it annoying to debug since the form must be open in order for the query to
work. Second, if you want to use the same query for other forms/reports it's
useless.
Another thing I almost always do is create a one-record table (e.g .
tblReportParameters ) for the report options form. Access/VBA just works
well with tables , that's what everything revolves around. In this way I can
create a report based upon this table and insert it as a subReport in the
report header so the user knows what parameters he chose.
Jack
I like the way you start off with TRUE so you don't have to worry about the
AND at the beginning. I have actually developed a function
(RemovePrefix(strPrefix, strFilter) As String) which removes the AND for me.
I use If Len(Trim(Me.LName)) > 0 instead of IsNull because if the user
happens to leave a couple of spaces in there, it will consider it null.
Curious how you are using CLng() for the dates. Won't this convert the time
associated with the date also? All dates have times associated with them,
although in most cases it's zero which represents 12 midnight I believe. My
point is, say the start date is 2/4/2006 12:01 AM , then your routine will
miss dates of 2/4/2006 12:00 AM
I just don't like imbedding the form controls in the query. First, it makes
it annoying to debug since the form must be open in order for the query to
work. Second, if you want to use the same query for other forms/reports it's
useless.
Another thing I almost always do is create a one-record table (e.g .
tblReportParameters ) for the report options form. Access/VBA just works
well with tables , that's what everything revolves around. In this way I can
create a report based upon this table and insert it as a subReport in the
report header so the user knows what parameters he chose.
Jack
Steve Schapel said:
S
Steve Schapel
Chad,
Put the Criteria like this...
Like "*" & [Forms]![EMPHRS QUERY]![JobDesc] & "*"
Put the Criteria like this...
Like "*" & [Forms]![EMPHRS QUERY]![JobDesc] & "*"
S
Steve Schapel
Jack,
As far as I know, this is not possible. You can't have just a space, or
a couple of spaces - Access will automatically remove them, and then it
will be Null. So, no harm done by what you do there, but also no use
.
My example assumed that the user would only have provision to enter a
date, and no time portion. In my experience, this is by far the most
common scenario. If there is provision to include the time component, I
use CDbl() in the place of CLng().
Fair comments. I myself find there is a place for both approaches,
depending on the specific requirement. As for testing, if the form is
not open, you get prompted to type in the criteria, which I find fine
from a testing point of view.
Interesting idea. By the way, you can refer to a form control in the
Control Source of a textbox on the report. For example, I would quite
often have a textbox in the report header with something like this in
its Control Source...
=[Forms]![Reports]![Sport] & " Points Tables for " &
Format([Forms]![Reports]![DateFrom],"dd-mmm\yy") & " to "
Format([Forms]![Reports]![DateTo],"dd-mmm\yy")
As far as I know, this is not possible. You can't have just a space, or
a couple of spaces - Access will automatically remove them, and then it
will be Null. So, no harm done by what you do there, but also no use
.My example assumed that the user would only have provision to enter a
date, and no time portion. In my experience, this is by far the most
common scenario. If there is provision to include the time component, I
use CDbl() in the place of CLng().
Fair comments. I myself find there is a place for both approaches,
depending on the specific requirement. As for testing, if the form is
not open, you get prompted to type in the criteria, which I find fine
from a testing point of view.
Interesting idea. By the way, you can refer to a form control in the
Control Source of a textbox on the report. For example, I would quite
often have a textbox in the report header with something like this in
its Control Source...
=[Forms]![Reports]![Sport] & " Points Tables for " &
Format([Forms]![Reports]![DateFrom],"dd-mmm\yy") & " to "
Format([Forms]![Reports]![DateTo],"dd-mmm\yy")
G
Guest
Steve,
I adjusted the query criteria like you said, and everything worked well,
except that it seems like there are too many fields and too many records.
Now whenever I run the query, or even when I try to open the query it says
that there is not enough memory. Any ideas?
Sept
I adjusted the query criteria like you said, and everything worked well,
except that it seems like there are too many fields and too many records.
Now whenever I run the query, or even when I try to open the query it says
that there is not enough memory. Any ideas?
Sept
Steve Schapel said:
S
Steve Schapel
Chad,
A "not enough memory" error when trying to run a query usually does not
mean "not enough memory". It usually means there is a mistake in
the query design that makes it impossible to evaluate.
I expect that because of the approach we have been taking to this query,
by now Access will have moved all your criteria around to handle the Is
Null situation, which thus makes it hard to understand. I think it
would be good to discard what you have done, and start the query design
fresh. Re-enter the Criteria as we have discussed, close and save, and
try again. If it's still not working, go back to design view, select
SQL from the View menu, and copy/paste the SQL view of the query into
your reply here, and I'll see if I can see.
A "not enough memory" error when trying to run a query usually does not
mean "not enough memory"
. It usually means there is a mistake inthe query design that makes it impossible to evaluate.
I expect that because of the approach we have been taking to this query,
by now Access will have moved all your criteria around to handle the Is
Null situation, which thus makes it hard to understand. I think it
would be good to discard what you have done, and start the query design
fresh. Re-enter the Criteria as we have discussed, close and save, and
try again. If it's still not working, go back to design view, select
SQL from the View menu, and copy/paste the SQL view of the query into
your reply here, and I'll see if I can see.