 How to retrive date&time records in forms [message #167412] |
Wed, 12 April 2006 20:27  |
yash1000
Messages: 22
Registered: August 2005
|
Junior Member |
|
|
Hi,
I have created table called EMP which has three column
empno,hiredate, sal
EMP
EMPNO number,
hiredate DATE,
sal number.
I have created three records
EMPNO HIREDATE SAL
1 13-Apr-2006 13:07:51 300
2 19-Mar-2006 13:10:12 400
3 12-Feb-2006 13:10:39 500.
1. I have created form called EMP_FRM to display the records
2. I want to display hiredate in DDMONRR foramt. I have set it's format mask properties to DDMONRR.
My problem when I run the form and query on hiredate and empno with value as 19MAR06 and 2 respectively.
I do not get any record display, but I get frm-40301. Query cause not records to be re-trived. Re-enter.
Could you please help me to work around with this issue.
Many Thanks,
Yash.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to retrive date&time records in forms [message #167923 is a reply to message #167447] |
Mon, 17 April 2006 22:58  |
yash1000
Messages: 22
Registered: August 2005
|
Junior Member |
|
|
As I have explanied earlier I have created table with following data. As show below
EMPNO HIREDATE SAL
1 13-Apr-2006 13:07:51 300
2 19-Mar-2006 13:10:12 400
3 12-Feb-2006 13:10:39 500.
FORM CHANGES
1 . I have created EMP data block,using data block wizard and followed the normal sequene till the completion of Layout wizard.
2.Now I am running the form(form version [32 Bit] Version 6.0.8.14.1) and want to retrive employe hired on 13-Apr-2006 date. When I queried on hiredate column with value 13-Apr-2006 I get no record returned. But as you can see record exists in DATABASE for 13-APR-2006.
I hope I have answered you question. Let me know if you any doubt.
Cheers,
Yash
|
|
|
|
|
|
| Re: How to retrive date&time records in forms [message #167965 is a reply to message #167923] |
Tue, 18 April 2006 02:34 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Yes, your record 'exists' in '13-APR-2006' but you are searching FOR '13-APR-2006' which means '13-APR-2006 00:00:00' not '13-APR-2006 13:07:51'.
We need to handle your HIREDATE in one of a couple of different ways.
(1) For example, either have two fields OVERLAYED so that you do data entry in one and retrieval in the other. The first is the database date time field and the second is a non-database date field. In the 'where' clause you have 'trunc(HIREDATE) = NVL(:blk.NDB_HIREDATE,trunc(HIREDATE))'. You will have to find and show the fields as you go.
(2) Your HIREDATE is NOT displayed on the canvas. In the Post-Query trigger you have ":blk.NDB_HIREDATE := :blk.HIREDATE;". The field NDB_HIREDATE IS displayed on the canvas. In the Pre-Insert and Pre-Update triggers, OR, repeat OR, in the When-Validate-Item you have ":blk.HIREDATE := :blk.NDB_HIREDATE;". Then in your 'where' clause you can have 'trunc(HIREDATE) = NVL(:blk.NDB_HIREDATE,trunc(HIREDATE))' or if you DO wish to search by date and time then you could use the default_where facility of the Set_Block_Property to add the required retrieval code. Search this forum for 'default_where' for examples.
David
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
