Date Parameter

[Guest]

I have a pass through query that has a date criteria in it. I want to change
it so that it looks at a form for the date.

Pass Through Criteria 1 (Working)
WHERE (((O101IA.TRETSER.PKG_SCH_RTN_DT)=TO_DATE('01/10/2005','MM/DD/YYYY')))

Pass Through Criteria 2 (Does not work)
WHERE (((O101IA.TRETSER.PKG_SCH_RTN_DT)=[Forms]![main]![txt_oo]))

Any ideas or suggestions would be greatly appreciated, thank in advance.

 

[Guest]

how is it not working? what error message are you getting?

 

[JohnFol]

I'd agree with the other response though, what error do you see? If this is
SQL, stick a trace on so you can see what is recevied by the server. You can
then cut'n'paste what's received into Query Analyser to see if it works.

At a stab I'd guess the passthrough is sending "[Forms]![main]![txt_oo]"
through to the server, rather than the value contained in the field.. . .

how is it not working? what error message are you getting?

-----Original Message-----
I have a pass through query that has a date criteria in it. I want to change
it so that it looks at a form for the date.

Pass Through Criteria 1 (Working)
WHERE (((O101IA.TRETSER.PKG_SCH_RTN_DT)=TO_DATE ('01/10/2005','MM/DD/YYYY')))

Pass Through Criteria 2 (Does not work)
WHERE (((O101IA.TRETSER.PKG_SCH_RTN_DT)=[Forms]![main]! [txt_oo]))

Any ideas or suggestions would be greatly appreciated, thank in advance.
.

 

[Guest]

It gives me an error message #920 "invalid relational operator" which I
interpret as not having the correct syntax... so hopefully I can get it to
work with the right syntax

how is it not working? what error message are you getting?

-----Original Message-----
I have a pass through query that has a date criteria in it. I want to change
it so that it looks at a form for the date.

Pass Through Criteria 1 (Working)
WHERE (((O101IA.TRETSER.PKG_SCH_RTN_DT)=TO_DATE ('01/10/2005','MM/DD/YYYY')))

Pass Through Criteria 2 (Does not work)
WHERE (((O101IA.TRETSER.PKG_SCH_RTN_DT)=[Forms]![main]! [txt_oo]))

Any ideas or suggestions would be greatly appreciated, thank in advance.
.

 

[Guest]

John, good stab because I did try
Format([Forms]![main]![txt_oo],'MM/DD/YYYY') and it gave me an error message
saying that it expected something but got text.

So how would I trace a pass through query and test it? I am not that
familiar with some of these functions.

I'd agree with the other response though, what error do you see? If this is
SQL, stick a trace on so you can see what is recevied by the server. You can
then cut'n'paste what's received into Query Analyser to see if it works.

At a stab I'd guess the passthrough is sending "[Forms]![main]![txt_oo]"
through to the server, rather than the value contained in the field.. . .

how is it not working? what error message are you getting?

-----Original Message-----
I have a pass through query that has a date criteria in it. I want to change
it so that it looks at a form for the date.

Pass Through Criteria 1 (Working)
WHERE (((O101IA.TRETSER.PKG_SCH_RTN_DT)=TO_DATE ('01/10/2005','MM/DD/YYYY')))

Pass Through Criteria 2 (Does not work)
WHERE (((O101IA.TRETSER.PKG_SCH_RTN_DT)=[Forms]![main]! [txt_oo]))

Any ideas or suggestions would be greatly appreciated, thank in advance.
.

 

[JohnFol]

Sounds like an Oracle error not MS SQL. If you use SQL* and paste the query
in there it will identify with an * the character causing the error. Of
course this presumes you know what is being sent to oracle. . ..

It gives me an error message #920 "invalid relational operator" which I
interpret as not having the correct syntax... so hopefully I can get it to
work with the right syntax

how is it not working? what error message are you getting?

-----Original Message-----
I have a pass through query that has a date criteria in it. I want to change
it so that it looks at a form for the date.

Pass Through Criteria 1 (Working)
WHERE (((O101IA.TRETSER.PKG_SCH_RTN_DT)=TO_DATE ('01/10/2005','MM/DD/YYYY')))

Pass Through Criteria 2 (Does not work)
WHERE (((O101IA.TRETSER.PKG_SCH_RTN_DT)=[Forms]![main]! [txt_oo]))

Any ideas or suggestions would be greatly appreciated, thank in advance.
.

 

[Lynn Trapp]

When you use a pass through query you simply send the text of your SQL
statement to the server database and it does all the functioning. In your
case, since Oracle has no knowledge of what [Forms]![main]![txt_oo] is, the
server is totally lost as to what you mean.

 

[Guest]

hi again
Try inclosing the ([Forms]![main]![txt_oo] in # #. It
might not be interpting the text as a date. If that don't
work the try ##([Forms]![main]![txt_oo]##. it seems to me
i had this problem once but i can't remember where. and i
think this is how i solved it.
good luck.

-----Original Message-----
John, good stab because I did try
Format([Forms]![main]![txt_oo],'MM/DD/YYYY') and it gave me an error message
saying that it expected something but got text.

So how would I trace a pass through query and test it? I am not that
familiar with some of these functions.

I'd agree with the other response though, what error do you see? If this is
SQL, stick a trace on so you can see what is recevied by the server. You can
then cut'n'paste what's received into Query Analyser to see if it works.

At a stab I'd guess the passthrough is sending "[Forms]! [main]![txt_oo]"
through to the server, rather than the value contained in the field.. . .

how is it not working? what error message are you getting?

-----Original Message-----
I have a pass through query that has a date criteria in
it. I want to change
it so that it looks at a form for the date.

Pass Through Criteria 1 (Working)
WHERE (((O101IA.TRETSER.PKG_SCH_RTN_DT)=TO_DATE
('01/10/2005','MM/DD/YYYY')))

Pass Through Criteria 2 (Does not work)
WHERE (((O101IA.TRETSER.PKG_SCH_RTN_DT)=[Forms]! [main]!
[txt_oo]))

Any ideas or suggestions would be greatly appreciated,
thank in advance.
.

.

 

[Lynn Trapp]

That's not going to help him because he is trying to do a Pass Through
query. Oracle does not know what [Forms]![main]![txt_oo] with or without the
pound signs.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

hi again
Try inclosing the ([Forms]![main]![txt_oo] in # #. It
might not be interpting the text as a date. If that don't
work the try ##([Forms]![main]![txt_oo]##. it seems to me
i had this problem once but i can't remember where. and i
think this is how i solved it.
good luck.

-----Original Message-----
John, good stab because I did try
Format([Forms]![main]![txt_oo],'MM/DD/YYYY') and it gave me an error message
saying that it expected something but got text.

So how would I trace a pass through query and test it? I am not that
familiar with some of these functions.

I'd agree with the other response though, what error do you see? If this is
SQL, stick a trace on so you can see what is recevied by the server. You can
then cut'n'paste what's received into Query Analyser to see if it works.

At a stab I'd guess the passthrough is sending "[Forms]! [main]![txt_oo]"
through to the server, rather than the value contained in the field.. . .




how is it not working? what error message are you getting?

-----Original Message-----
I have a pass through query that has a date criteria in
it. I want to change
it so that it looks at a form for the date.

Pass Through Criteria 1 (Working)
WHERE (((O101IA.TRETSER.PKG_SCH_RTN_DT)=TO_DATE
('01/10/2005','MM/DD/YYYY')))

Pass Through Criteria 2 (Does not work)
WHERE (((O101IA.TRETSER.PKG_SCH_RTN_DT)=[Forms]! [main]!
[txt_oo]))

Any ideas or suggestions would be greatly appreciated,
thank in advance.
.

.