| Query the record based on date [message #401181] |
Sat, 02 May 2009 01:00  |
honey_28112007
Messages: 10
Registered: March 2009
|
Junior Member |
|
|
I have a order table having 4 columns
cid
oid
Date_of_order and
delivery_date.
I have a block based on it. I want to query the block so that it can give me orders between two dates i've entered. Like i want the orders placed between two dates e.g. 1-apr-2009 and 30-apr-2009.
What should i do..? thx in advance
|
|
|
|
|
|
|
|
| Re: Query the record based on date [message #401217 is a reply to message #401181] |
Sat, 02 May 2009 14:02  |
 |
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
| honey_28112007 | I want to query the block so that it can give me orders between two dates i've entered
|
Where did you enter those dates? Which items? Did you create, for example, a control block which contains two DATE items? If so, then @klat's DEFAULT_WHERE suggestion *should* work - only if you know how to properly use it. Saying that "it's not working" is useless. Why is it not working? Was there any error message? If so, which one? If not, how does your statement look like? To me, it seems that you don't know how to properly use this functionality and not that it doesn't work. You aren't the very first one who uses (or is supposed to use) the DEFAULT_WHERE and, believe me, it works.
Basically, you don't have to do anything: Forms is capable of querying data the way you have described by using "query variables". Here's how it works:
- Run the form; enter query mode
- Instead of specifying value, enter a query variable into an item. It starts with a colon (:) sign and a name (any name would be OK. For example ":a" or ":my_date" etc.). This is an example based on Scott's EMP table:
- Now press EXECUTE QUERY. A "Query/Where" window will open. Enter your search criteria in there, just as you'd put it into a SELECT's WHERE clause. For example:
- Finally, click the <OK> button. Records you're interested in will be displayed:
As you've seen, that's really nothing much (just if you know how to use it).
Just as an add-on: you may use as many variables as you wish. For example, if you wanted to select employees who were hired between dates from a previous example and whose names contain letter "A", you might have done that as follows:
- enter ":A" into the ENAME item
- enter ":B" into the HIREDATE item
- WHERE clause would be
:a like '%A%' and
:b between to_date('01.03.1981', 'dd.mm.yyyy')
and to_date('01.09.1981', 'dd.mm.yyyy')
So, yes - there usually is more than just one way to solve the problem. Though, you should learn how to properly ask the question, explain what you have, what you did and how the system responded. Without all of these, we will probably not be able to help much.
-
Attachment: frm_q_2.PNG
(Size: 5.14KB, Downloaded 1367 times)
-
Attachment: frm_q_1.PNG
(Size: 1.59KB, Downloaded 1397 times)
-
Attachment: frm_q_3.PNG
(Size: 2.78KB, Downloaded 1346 times)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
