I'm doing a Databse pull to get some information. I have the query working
but I'd like to have the Date part of the query to be entered via a prompt or
enter dates into a field and press retrieve button. How do I setup the Query
Value to allow me to do this?

http://nickhodge.co.uk/gui/datamenu/...taexamples.htm

--
Regards,
Tom Ogilvy


"Mike Punko" wrote in message
...
I'm doing a Databse pull to get some information. I have the query working
but I'd like to have the Date part of the query to be entered via a prompt
or
enter dates into a field and press retrieve button. How do I setup the
Query
Value to allow me to do this?

Ok the code kind of worked. It does prompt me for the date but no matter
what I type I get the error "ORA-01861: literal does not match format string"
any ideas

My old code just to run the query was.

=#2005/10/25 7:00:00 AM# And <=#2005/10/26 8:00:00 AM#

But when I try to just type in 2005/10/25 7:00:00 for my Start date and visa
versa for the End date I get the above error.

"Tom Ogilvy" wrote:

http://nickhodge.co.uk/gui/datamenu/...taexamples.htm

--
Regards,
Tom Ogilvy


"Mike Punko" wrote in message
...
I'm doing a Databse pull to get some information. I have the query working
but I'd like to have the Date part of the query to be entered via a prompt
or
enter dates into a field and press retrieve button. How do I setup the
Query
Value to allow me to do this?

Try typing it in with the # signs.

Obviously the database server doesn't see what is delivered as a query as
having valid values. So I can't really address that for this approach. If
you want to do it in code, you could build the string to match what works.

--
Regards,
Tom Ogilvy


"Mike Punko" wrote in message
...
Ok the code kind of worked. It does prompt me for the date but no matter
what I type I get the error "ORA-01861: literal does not match format
string"
any ideas

My old code just to run the query was.

=#2005/10/25 7:00:00 AM# And <=#2005/10/26 8:00:00 AM#

But when I try to just type in 2005/10/25 7:00:00 for my Start date and
visa
versa for the End date I get the above error.

"Tom Ogilvy" wrote:


http://nickhodge.co.uk/gui/datamenu/...taexamples.htm

--
Regards,
Tom Ogilvy


"Mike Punko" wrote in message
...
I'm doing a Databse pull to get some information. I have the query
working
but I'd like to have the Date part of the query to be entered via a
prompt
or
enter dates into a field and press retrieve button. How do I setup
the
Query
Value to allow me to do this?

OK well maybe this might help

This is what I type in for the Value in the query

=2005/10/25 7:00 And <=2005/10/26 8:00

And this is what it does automaticly once I press enter.

=#2005/10/25 7:00:00 AM# And <=#2005/10/26 8:00:00 AM#


"Tom Ogilvy" wrote:

Try typing it in with the # signs.

Obviously the database server doesn't see what is delivered as a query as
having valid values. So I can't really address that for this approach. If
you want to do it in code, you could build the string to match what works.

--
Regards,
Tom Ogilvy


"Mike Punko" wrote in message
...
Ok the code kind of worked. It does prompt me for the date but no matter
what I type I get the error "ORA-01861: literal does not match format
string"
any ideas

My old code just to run the query was.

=#2005/10/25 7:00:00 AM# And <=#2005/10/26 8:00:00 AM#

But when I try to just type in 2005/10/25 7:00:00 for my Start date and
visa
versa for the End date I get the above error.

"Tom Ogilvy" wrote:


http://nickhodge.co.uk/gui/datamenu/...taexamples.htm

--
Regards,
Tom Ogilvy


"Mike Punko" wrote in message
...
I'm doing a Databse pull to get some information. I have the query
working
but I'd like to have the Date part of the query to be entered via a
prompt
or
enter dates into a field and press retrieve button. How do I setup
the
Query
Value to allow me to do this?

Hi,

I was grappling with this problem for quite a while and have (finally)
come up with a solution so hopefully this will help you.

The problem (I believe) is that the database is Oracle, whose internal
date format is (usually) 'DD-MON-YY'. No matter how I tried to pass
the date through a parameter box in MS Query it failed with an Oracle
error code.
I have not found a way round this using MS Query but I have found a way
round it if you build the query in VBA.

I just started recording a macro and opened a query in MS Query and
then passed it back to Excel. When you examine the Macro code you can
see where you need to edit the SQL.

Add the parameter value as a "?" (as suggested in this thread I think).
Then when you try and run the Macro Excel will prompt you for the
value. It should work if you enter a valid Oracle format date, e.g.
01-OCT-05.
I've linked this to a cell where the format is "text" so that it
doesn't try and convert it to an Excel date.

HTH.


--
scos00
------------------------------------------------------------------------
scos00's Profile: http://www.excelforum.com/member.php...o&userid=28264
View this thread: http://www.excelforum.com/showthread...hreadid=479472