Re: Dynamic query problem
- From: maceslin@xxxxxxxxx
- Date: Thu, 18 Oct 2007 01:51:32 -0000
On Oct 17, 9:41 am, Andy Hull <AndyH...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
Hi again
This post is getting pretty lengthy now and is gradually moving away from
the original topic so, if you are happy for me to do so I will send my next
post to your email.
Let me know know if this is ok.
I'm assuming it'smaceslinfollowed by at gmail dot com
I've typed it like that so that email harvesting programs can't extract it
from this post and start sending you unsolicited mail.
For that reason, you should think about changing your options for this
newsgroup so that your email doesn't show.
Here is the code I would consider using to process the dates...
If (chkDateRange) and Not (IsNull(txtBeginDate) and IsNull(txtEndDate)) Then
If strWhere = "" Then
strWhere = "Where"
Else
strWhere = strWhere & " OR "
End If
strWhere = strWhere & "(Date_Entered between " &
format(nz(txtBeginDate,#01/01/1900#),"\#mm\/dd\/yyyy\#") & " and " &
format(nz(txtEndDate,#12/31/9999#),"\#mm\/dd\/yyyy\#") & ")"
End If
Be careful with the long line as it will be wrapped in this window.
I don't know how familiar you are with using dates and formatting so it
might look very complicated at first glance.
Take the time to go through it carefully though with the goal of
understanding it all fully. If you just use the code as is (as many people
probably do in the newsgroup) you will get stuck in the future with similar
but slightly different problems. Whereas taking the time to understand it
will mean you can then apply that knowledge to solve all similar future
problems.
To come up with the above code I decided it was too troublesome to have to
test which date, if either, was null as this affects where the brackets and
the "and" can be placed. So I decided that if the begin date was null I'd use
01/01/1900 and if the end data was null I'd use 12/31/9999. I've assumed that
you won't be searching for dates outside this range. You can make the 1900
date earlier if you really nedd to.
Also, rather than using if then statements to test if they are null I used
the Nz function.
This works in the following way...
If txtBeginDate is null then nz(txtBeginDate,#01/01/1900#) = #01/01/1900#
But, if txtBeginDate isn't null then nz(txtBeginDate,#01/01/1900#) =
txtBeginDate (ie whatever the user entered)
You've asked about choosing AND's and OR's...
This is a tricky situation.
Not because it's hard to code necessarily but because combining AND's and
OR's is a confusing subject for lots of people so providing the option can
lead to more users making mistakes.
If we were to provide a fully featured dynamic query generator we would have
to let the user, not only choose AND's or OR's, but put their criteria in any
order and possibly choose where to put brackets.
This is a lot of coding effort and for many users unnecessary and confusing.
My opinion is, if a user needs that type of functionality and understands it
then they are better off being...
1) Provided with already built queries which they can edit
and/or 2) Allowed to build their own queries from scratch
To start answering your question about the subform...
I would design the query and/or report in full that you would want to run if
this option was chosen.
Having done that you will know what the SQL is going to be so look at that
SQL and see where it is the same as for the other queries and where it
differs.
Then you will design your if then statements to piece together the SQL as
required.
You may find that there is a chunk of SQL needed in the select and from
parts that isn't required in the other queries.
In that case you will need to add if then functionality to build the select
and from parts just like you currently do to build the where part.
Another long post - hope it makes sense
Remember to say if it would be better to email you direct
Regards
Andy Hull
If you are still willing to teach me (I am learning a lot not just
blindly copying your inputs) please continue to contact me at the
email address you listed or same name @juno.com
I understand the nZ statement but do not understand the purpose of
the"\" in addition to the"/"before everything in the user entered date
and can not find anyhting on the web that show it when formating dates
When nothing entered in the txt boxes I get "missing operator in query
statement" when I would expect to see everything since all records are
between the default values
I have spent this evening changing the "select" and "from" statments
to show only what I need. I have also rebuilt and renamed the report
rptInternal. I am using the same query (qryParamters) to populate a
form so that I can send it out with active links in it. Have started
to develop cmdEmail to do so
Am still trouble shooting chkStatus and cboStatus- the report does not
populate
May be out of loop until Monday evening as I am returning home late
tomm and then on road for USNA vs Wake Forest football game, will be
able to check email but not have much time to work problems
As always thanks for the instruction
Dave
.
- References:
- Re: Dynamic query problem
- From: Andy Hull
- Re: Dynamic query problem
- Prev by Date: Re: Passing > or < from a form
- Next by Date: Re: Query to return 4 records per table record
- Previous by thread: Re: Dynamic query problem
- Next by thread: Tracking missing records
- Index(es):
Relevant Pages
|
