Query date Parameter Anomaly

[PSULionRP]

I have a Microsoft Access Query that has an Enterable Date Parameter. I.E.

[Enter Last Business Day (yyyy-mm-dd)]

Now, the Query will go against an Oracle Database and the Date column being
satisfied is actually defined as a CHAR(10). So it seems as though the Date
Paramater has to include double quotation marks around it, like "2009-11-04".
But when the user is prompted for the date, it doesn't seem as though it's
putting it in the double quotation marks. When I run the query without the
parameter as "2009-11-04", it works perfect.

Is there any way to get double quotation marks implicitly around the entered
date parameter easily???

Thanks in advance for your review and hopeful for a reply.

Thanks!

PSULionRP

 

[KARL DEWEY]

Try this --

Str([Enter Last Business Day (yyyy-mm-dd)])

--
Build a little, test a little.

I have a Microsoft Access Query that has an Enterable Date Parameter. I.E.

[Enter Last Business Day (yyyy-mm-dd)]

Now, the Query will go against an Oracle Database and the Date column being
satisfied is actually defined as a CHAR(10). So it seems as though the Date
Paramater has to include double quotation marks around it, like "2009-11-04".
But when the user is prompted for the date, it doesn't seem as though it's
putting it in the double quotation marks. When I run the query without the
parameter as "2009-11-04", it works perfect.

Is there any way to get double quotation marks implicitly around the entered
date parameter easily???

Thanks in advance for your review and hopeful for a reply.

Thanks!

PSULionRP

 

[PSULionRP]

Hey Karl.

First of all, Thanks for helping me out here.

When I entered that expression as the Query Parameter...

Str([Enter Last Business Day (yyyy-mm-dd)])

I got this nasty dialog box that opened up on me with the following message.

This expression is typed incorrectly, or is too complex to be evaluated. For
example, a numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to variables.


Does this sound like something I'm going to have to do in a VBA Module???

Did I fat finger something???

Let me know your thoughts.

And Thanks Again!

Try this --

Str([Enter Last Business Day (yyyy-mm-dd)])

--
Build a little, test a little.

I have a Microsoft Access Query that has an Enterable Date Parameter. I.E.

[Enter Last Business Day (yyyy-mm-dd)]

Now, the Query will go against an Oracle Database and the Date column being
satisfied is actually defined as a CHAR(10). So it seems as though the Date
Paramater has to include double quotation marks around it, like "2009-11-04".
But when the user is prompted for the date, it doesn't seem as though it's
putting it in the double quotation marks. When I run the query without the
parameter as "2009-11-04", it works perfect.

Is there any way to get double quotation marks implicitly around the entered
date parameter easily???

Thanks in advance for your review and hopeful for a reply.

Thanks!

PSULionRP

 

[Douglas J. Steele]

Are you sure you need double quotes? In SQL Server, you need to use single
quotes.

For double quotes, try:

Chr(34) & [Enter Last Business Day (yyyy-mm-dd)] & Chr(34)

For single quotes, try:

Chr(39) & [Enter Last Business Day (yyyy-mm-dd)] & Chr(39)

However, that shouldn't be necessary. ODBC is supposed to take care of the
translation. If you've linked tables to Oracle, you should be using the
Access standard of # as the delimiter. It's only with pass-through queries
that you need to worry about the native DBMS rules, and you can't have
pass-through queries prompt for parameters.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hey Karl.

First of all, Thanks for helping me out here.

When I entered that expression as the Query Parameter...

Str([Enter Last Business Day (yyyy-mm-dd)])

I got this nasty dialog box that opened up on me with the following
message.

This expression is typed incorrectly, or is too complex to be evaluated.
For
example, a numeric expression may contain too many complicated elements.
Try
simplifying the expression by assigning parts of the expression to
variables.


Does this sound like something I'm going to have to do in a VBA Module???

Did I fat finger something???

Let me know your thoughts.

And Thanks Again!

Try this --

Str([Enter Last Business Day (yyyy-mm-dd)])

--
Build a little, test a little.

I have a Microsoft Access Query that has an Enterable Date Parameter.
I.E.

[Enter Last Business Day (yyyy-mm-dd)]

Now, the Query will go against an Oracle Database and the Date column
being
satisfied is actually defined as a CHAR(10). So it seems as though the
Date
Paramater has to include double quotation marks around it, like
"2009-11-04".
But when the user is prompted for the date, it doesn't seem as though
it's
putting it in the double quotation marks. When I run the query without
the
parameter as "2009-11-04", it works perfect.

Is there any way to get double quotation marks implicitly around the
entered
date parameter easily???

Thanks in advance for your review and hopeful for a reply.

Thanks!

PSULionRP

 

[KARL DEWEY]

I guess a string will not work we convert it to a date.
Try this first --

DateSerial(Left([Enter Last Business Day (yyyy-mm-dd)],4),Mid([Enter Last Business Day (yyyy-mm-dd)],6,2),Right([Enter Last Business Day (yyyy-mm-dd)],2))

You may need to convert the text from the Oracle Database also.

--
Build a little, test a little.

Hey Karl.

First of all, Thanks for helping me out here.

When I entered that expression as the Query Parameter...

Str([Enter Last Business Day (yyyy-mm-dd)])

I got this nasty dialog box that opened up on me with the following message.

This expression is typed incorrectly, or is too complex to be evaluated. For
example, a numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to variables.


Does this sound like something I'm going to have to do in a VBA Module???

Did I fat finger something???

Let me know your thoughts.

And Thanks Again!

Try this --

Str([Enter Last Business Day (yyyy-mm-dd)])

--
Build a little, test a little.

I have a Microsoft Access Query that has an Enterable Date Parameter. I.E.

[Enter Last Business Day (yyyy-mm-dd)]

Now, the Query will go against an Oracle Database and the Date column being
satisfied is actually defined as a CHAR(10). So it seems as though the Date
Paramater has to include double quotation marks around it, like "2009-11-04".
But when the user is prompted for the date, it doesn't seem as though it's
putting it in the double quotation marks. When I run the query without the
parameter as "2009-11-04", it works perfect.

Is there any way to get double quotation marks implicitly around the entered
date parameter easily???

Thanks in advance for your review and hopeful for a reply.

Thanks!

PSULionRP