Re: Dynamic query problem

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's maceslin followed 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


.

Relevant Pages

  • Re: Enterprise Manager confusion
    ... case of Microsoft introducing functionality in a Service Pack. ... On my old laptop, I had SQL Server Enterprise Manager installed, I believe ... sizes in the View designer, and properly handles CASE statements as well. ... It also take *dreadfully* long to load any complex queries into the View ...
    (microsoft.public.sqlserver.tools)
  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Massive Large Query Issues
    ... Project Budgets: Entered at the Capital Request level, ... installed (One install is typically $100,000 plus the cost of the equpment, ... install SQL Server. ... Often, a collection of queries ...
    (microsoft.public.access.queries)
  • Re: Massive Large Query Issues
    ... Project Budgets: Entered at the Capital Request level, ... install SQL Server. ... of queries? ... make of the query you provided is to roll it up into a subsequent ...
    (microsoft.public.access.queries)
  • Re: "Query Too Complex" Errors
    ... few dozens of queries, in the middle of which there's a long chain of ... we've been having a lot of those "Query Too ... some of the complexity in the SQL ... SQL statement you are working on. ...
    (microsoft.public.access.forms)
Loading