Changes the values of an existing query

[Guest]

I have created a form where users enter dates into 2 text boxes (VALUE1 and
VALUE2) and hit a sumbit button. I would like to then take the values they
entered and use them to change the value in a query.

I would like to change the query from:
SELECT tblMain.[Date Of Job], tblMain.[Job Code], tblMain.[Tech Number]
FROM tblMain
WHERE (((tblMain.[Date Of Job]) Between #11/14/2004# And #11/20/2004#));

to

SELECT tblMain.[Date Of Job], tblMain.[Job Code], tblMain.[Tech Number]
FROM tblMain
WHERE (((tblMain.[Date Of Job]) Between #VALUE1# And #VALUE2#));

Is there a way to do this?
Thanks

 

[Rob Oldfield]

You can create a query by doing something like this....

dim sql as string
sql = "select * from whatever"
dim qdf as querydef
set qdf = currentdb.createquerydef("CreatedQuery", sql)

....so at a basic level you'd just need to use the dates entered on the form
and create the sql string. The CreateQueryDef method will go wrong if the
query already exists, so you'd probably need to check for it's existence
first and delete it if it is there unless you can guarantee that it will be
there.

Having said that, it's unusual (though not completely unknown) to require a
query to exist completely independent of any parameters entered via forms.
Are you not able to create the same effect by setting up the query on the
fly?

 

[Guest]

Thank you for the help. You are right, i don't need to have an existing query
to have the same effect so i won't have one.
one last question.
how do i check to see if the query ("CreatedQuery" in this case) already
exists and delete it if it does?

You can create a query by doing something like this....

dim sql as string
sql = "select * from whatever"
dim qdf as querydef
set qdf = currentdb.createquerydef("CreatedQuery", sql)

....so at a basic level you'd just need to use the dates entered on the form
and create the sql string. The CreateQueryDef method will go wrong if the
query already exists, so you'd probably need to check for it's existence
first and delete it if it is there unless you can guarantee that it will be
there.

Having said that, it's unusual (though not completely unknown) to require a
query to exist completely independent of any parameters entered via forms.
Are you not able to create the same effect by setting up the query on the
fly?

I have created a form where users enter dates into 2 text boxes (VALUE1 and
VALUE2) and hit a sumbit button. I would like to then take the values they
entered and use them to change the value in a query.

I would like to change the query from:
SELECT tblMain.[Date Of Job], tblMain.[Job Code], tblMain.[Tech Number]
FROM tblMain
WHERE (((tblMain.[Date Of Job]) Between #11/14/2004# And #11/20/2004#));

to

SELECT tblMain.[Date Of Job], tblMain.[Job Code], tblMain.[Tech Number]
FROM tblMain
WHERE (((tblMain.[Date Of Job]) Between #VALUE1# And #VALUE2#));

Is there a way to do this?
Thanks

 

[John Vinson]

how do i check to see if the query ("CreatedQuery" in this case) already
exists and delete it if it does?

Just delete it and trap the error.

John W. Vinson[MVP]

 

[Rob Oldfield]

Easiest way is to just try and delete it and ignore the error if the delete
fails (because it isn't there)....

set db=currentdb
sql="select blah from blah"
on error resume next
db.querydefs.delete "CreatedQuery"
on error goto 0
set qdf=db.createquerydef("CreatedQuery",sql)

....though you could check whether a query exists in the first place by
using....

function QueryExists(QName as string) as boolean
on error resume next
QueryExists = isobject(currentdb.querydefs(QName))
end function

Thank you for the help. You are right, i don't need to have an existing query
to have the same effect so i won't have one.
one last question.
how do i check to see if the query ("CreatedQuery" in this case) already
exists and delete it if it does?

You can create a query by doing something like this....

dim sql as string
sql = "select * from whatever"
dim qdf as querydef
set qdf = currentdb.createquerydef("CreatedQuery", sql)

....so at a basic level you'd just need to use the dates entered on the form
and create the sql string. The CreateQueryDef method will go wrong if the
query already exists, so you'd probably need to check for it's existence
first and delete it if it is there unless you can guarantee that it will be
there.

Having said that, it's unusual (though not completely unknown) to require a
query to exist completely independent of any parameters entered via forms.
Are you not able to create the same effect by setting up the query on the
fly?

I have created a form where users enter dates into 2 text boxes

(VALUE1
and

VALUE2) and hit a sumbit button. I would like to then take the values they
entered and use them to change the value in a query.

I would like to change the query from:
SELECT tblMain.[Date Of Job], tblMain.[Job Code], tblMain.[Tech Number]
FROM tblMain
WHERE (((tblMain.[Date Of Job]) Between #11/14/2004# And #11/20/2004#));

to

SELECT tblMain.[Date Of Job], tblMain.[Job Code], tblMain.[Tech Number]
FROM tblMain
WHERE (((tblMain.[Date Of Job]) Between #VALUE1# And #VALUE2#));

Is there a way to do this?
Thanks