• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2117
  • Last Modified:

Query problem - why cant I query my date field?


Oracle 9i, forms 6i. Basically I have a booking system where I am using date_time field formatted to 'DY DD-MON-YYYY, HH24:MI'. I have discovered that I cannot effectively query my forms, I type in a query date exactly in the format above, for example, and when I execute, it just returns all records. I do not understand why it does this, it is recognising the date format as if i enter in MAR for example, it will show an error telling me I havent entered the date according to the format mask. A bit of info:  I change the session date format on opening the relevant forms (as I need the date format above including the time) and then change it back to DD-MON-YYYY on form close

I am using a tab canvas for each different type of 'lesson' and there is a where clause on the data block for each tab page that specifies each lesson type. The query only actions on the current ab page, and does return the correct records for each tab page type, but as described above when querying, it simply returns all records.

I need some way of allowing the user to specify the date, they cant navigate through all the lesson records each time they wish to make a booking. Please help!
2 Solutions
Is your date item a 'Database item'?  If not, Forms will not include that item when executing the query.

Also, do you have special code that alters the 'default' query condition (usually in PRE-QUERY trigger)
emmy21Author Commented:
Yes, each page is based on the lesson block, so I have ended up with lesson block, lesson1 block, lesson2 block where I have the need for 3 different lesson types on different tab pages.

There is no Pre-Query trigger, no fiddling at all apart from the where clauses attached to each block on each page to filter the lesson types onto each tab page.
Just to slimdown the error situation to this field... have you checked if the querying works ok for all other fields? When we do special processings, most of the time our mind tends to look at this spl processing only. In your case the special processing is the date format and so you might look only the date format errors. If you check and confirm all other fields query the records properly, then we are certain that this is because of the datetime format error.

We will take it from there...
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

To go through the steps :

1. Form is put into enter query mode.
2. User enters date in format specified by format mask on item
3. Form does Execute query - creates a SELECT .... FROM ...WHERE...
4. Query sent to Server and result returned from server

If you don't go into enter-query mode under step 1, you will by default, be in insert mode.
If you try to execute a query while in insert mode, you would probably get a prompt asking you to save data (though if there's any code to clear block or somesuch, this may not happen).

That's the first place I would look for a problem.

Second place is the query sent to the server. You can use :SYSTEM_LAST_QUERY to see what that was and if the predicate on the date was included.
Alternatively, you could do a trace (ALTER SESSION SET SQL_TRACE=TRUE)  and look at the server trace logs.

When you use dates for query you should be very carefull about the time component.
If you are looking for date comparison only you have to trim the time component stored in every date.

FROM   ...
WHERE trim(date_column) = trim(:date_item);


FROM   ...
WHERE TO_CHAR(date_column,'YYYYMMDD') = TO_CHAR(:date_item,'YYYYMMDD') ;

To compare dates with time component is not the usual case.
If you are receiving all the records, then hopefully, you haven't used "Where Clause" property of the block.
emmy21Author Commented:
ok, basically I have got a LOV working so that the user can select a master (lesson) record and the detail records (bookings, if any) will appear for that lesson.

I have included the date_time in the LOV as TO_CHAR for display purposes only showing the date and the time - and where I was going wrong was the conversion to pass the selected value back to the form for the query, so I have passed the original date_time through the LOV as well without a conversion, and this works.

 The problem is, I have a master detail form which I want the user to be able to query via a LOV (as they might not know a lesson exists before they look to see what is available) and typing in the date and time is a little longwinded but - I also need the LESSON master block to be protected from inserts etc. Yet the LOV seems to act as an insert even in query mode, and not regarded the same as if the user physically types in the date_time into the field. Or maybe my design should be that i create lessons on the master detail form in the first place, and so both blocks are updateable??

Thanks for your help
I, once had the smae issu. The simple solution is:

Create a non-database block (say block1) and create an item (say item1)  in it (non-database). Attach LOV to it.

Set both the master & detail blocks proporties (insert allowed, delete allowed, update allowed) to false.

In the Key-Next-Item trigger of Item1, use:

And if you can tell me your email address, i will send you sample form(emp-dept blocks) doing the same.

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now