Getting close!
As i have it written:
Dim db As Database
Set db = CurrentDb
Dim qdf As QueryDef
qdf.SQL = "SELECT [Query C].* " + "FROM [Query C]" + "WHERE ((([Query
C].Dateof)>=#9/15/**** # And ([Scores-Win Loss].Dateof)<=#12/15/****#));"
Set qdf = Nothing
Set db = Nothing
( This probably gives me enough flexiblity "string", I'll work with the
input boxes later.)
I get a VB run time error # 91.
Object variable or Width block variable not set
--
David McKnight
:
Oops. I missed that Klatuu's suggestion wasn't right. Allen's, however,
is
OK...
"SELECT [table A].* FROM [table A] WHERE Month([table A].[DATEOf])=8;"
would
give you just August.
With regard to the flexibility, that's a bit of 'how long is a piece of
string' type question. Let's assume that you want the user to be able to
enter two dates and just limit the data to dates between those. The best
way to approach this is to just go to a query window and set it up (using
any two dates). You can then use the View, SQL View command to see the
sql
text for that query. That will look something like...
SELECT [Table A].*
FROM [Table A]
WHERE ((([Table A].DateOf)>=#1/1/2005# And ([Table
A].DateOf)<=#1/10/2005#));
so that's the text that you'd need to build, replacing the dummy dates
with
the dates that the user has entered. So maybe...
qdf.sql="SELECT [Table A].* "+ _
"FROM [Table A] "+ _
"WHERE ((([Table A].DateOf)>=#"+ _
forms![DateInput]![StartDate]+"# And ([Table A].DateOf)<=#"+ _
forms![DateInput]![EndDate]+"#));"
There's another issue there if you use dd/mm/yyyy format dates. But
we'll
worry about that later.
message
When I use this code I get a missing operator error. One thing I don't
get
that I'm trying to achieve from the below statement is flexiblity to
specify
a month/day combination , say all days between 9/12 & 11/12 for any
given
year.
--
David McKnight
:
It will be something like Klatuu posted in your other thread...
qdf.Sql= "SELECT [table A] WHERE Month([DATEOf]) = 8;"
message
Oops, had a spelling error so "Query C" line is okay. However,
having
problems with the next line getting syntax correct.
--
David McKnight
:
Sorry if I'm taking you code too verbatim, but this is how I
translated
into
what I'm trying to do.
Dim db As Database
Set db = CurrentDb
Dim qdf As QueryDef
Set qdf = db.QueryDefs("Query C")
qdf.SQL = "select * [Dateof]= 8/*/****, or 9/*/****, Or
10/1/****"
Set qdf = Nothing
Set db = Nothing
Query C being the orginal query with a field called [Dateof].
With
is
coed I
get a run time error of 3265, Item not found in collection. this
is
on
lineSet qdf=....
--
David McKnight
:
Yes. That's what I thought you had. The idea is that if your
final
query A
is based on another query B which is based on another query C,
then
you can
use my example to modify the SQL of C (so limiting it to
whatever
date
range
you wish), and then use query A.
"David McKnight" <
[email protected]>
wrote
in
message
Not really following this reply. Just so you understand
better
what
I'm
trying to do .. more info.
I have a rather complex series of queries that gets me an avg
value
of [a]
for each
. I would like some times to have the code avg
only
by
a set
of
dates (say Aug and Sept) based on a text box. Since my final
query
[A]
does
not have a date field, nor could it, to have the corrrect out
put.
How can
I
preselect out only Aug and Sept dates based on text box
input. I
was
hoping
to have a WHERE statement that would be temporarly inserted
into
underlining
query so that the final query would give the desire output
but
then
remove
the WHERE statement afterwords so that a different set of
dates
could be
evaluated. I know it may seem like the easy way would be to
setup a
seperate
underling query but the whole series if very complicated and
it
seems like
code could do this.
David McKnight
:
There might be a better solution depending on your exact
situation, but
you
can set the SQL of a query by something like...
dim db as database
set db=currentdb
dim qdf as querydef
set qdf = db.querydefs("QueryName")
qdf.sql = "select * from whatever where field=3"
set qdf=nothing
set db=nothing
Just use that to pick out where month=8 or 9, and then
build
another (or
more than one) query to query that.
"David McKnight"
