Please Help!

Xx1947xX
Xx1947xX used Ask the Experts™
on
I am very new to PB.
My report requirement is this.
Ask the user to type in two dates, FromDate and ToDate.
Take the value of those dates, and query the Employee table to retrieve all employees whose hire_date is between these two dates.

So, on my base sheet I created two single line edit boxes
named FromDt, and ToDt. And I have Submit button.
The way it's working right now is no matter what the user types in the FromDt, and Todt when they click on submit they get every row in the Employee table.

My problem is that I do not know what to put in the SQL "Where" clause. I think the syntax should be,
SELECT Empname, Empid
FROM   Employee
WHERE  hire_date BETWEEN :fromdt AND :todt

but when I do the above, a box pops up asking me to enter the value for fromdt, and todt. I want it to take the value from the single line edit boxes.
Can someone please tell me how I can tie what the user types in the two date fields to my sql query? I am having so much trouble, and the boss is sitting on my head waiting for me to finish up, without helping. Thank you so much. Please explain in detail the steps required because I am very new to PB. We are using PB 8.0. Again, thank you so very much! I only have 50 points....
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Your date formats may not all be the same try something like this is your where clause:

trunc(planned_start) BETWEEN trunc(to_date(dateSearch,'FMMM-DD-FMYY'))
                               AND trunc(to_date(dateSearch2,'MM-FMDD-FMYY'))
For each retrieval argument defined in your dataobject you need to pass them in via the retrieve function as shown above.

Ok. assume your sle's are named sle_fromdt and sle_todt and your datawindow is called dw_1

Change your dw_1.Retrieve()

TO

dw_1.Retrieve(Date(sle_fromdt.text), Date(sle_todt.text))

Thats it!

Sincerely,
Rich Bianco


Author

Commented:
Thank you for your patience. I think I am close to complete this report but, I need to retrive sle_empid from the datawindow also, and when I did,
dw_1.Retrieve(sle_empid.text, Date(sle_fromdt.text), Date(sle_todt.text))

And ran the report I got an error msg saying
"Retreive argument 1 does not match expected type."
Retreive argument 1 would be the sle_empid which I have defined as datatype Number. Can you please tell me what I should write in the dw_1.Retrieve(.... part? Thank you.
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

No problem. As you are finding out... your retrieval arguments must match in exact order and type that you have them defined.

You are sending in a string (sle_empid.text) when the dataobject is expecting a number.

Simply change it to this:

dw_1.Retrieve(Long(sle_empid.text), Date(sle_fromdt.text), Date(sle_todt.text))

Happy Holidays!
Rich Bianco



Author

Commented:
:-) Thank you very much. Now that error is gone...but there still is a problem. Now when I click on Submit nothing happens! Before when I just had,
dw_1.Retrieve()
Whenever someone clicked on Submit button a popup windows used to come up and ask the user for the retrieve arguments' value. Now however, I have
dw_1.Retrieve(long(sle_associateid),Date(sle_fromdt.text), Date(sle_todt.text))
and while it got rid of the annoying popup box, it doesn't do anything at all when I click on it!! Please reply soon! Thank you.
I hope your retrieve doesn't look 'exactly' like that. You should have sle_associateid.text. Notice you are missing the .text for that single line edit which means you are trying to convert the entire sle_associateid to a long.

The only other thing that is probably happening (if you aren't getting other errors) is that your retrieval arguments do not meet any criteria. Use a larger date range and see if it helps.

A better way to program this is to use variables, so that you can run through debugger and see what you are passing in OR you can display messagebox for debug purposes to see exactly what you are sending in..  try something similar to:

date ldt_fromdt, ldt_todt
long ll_associateid
integer li_rowcount

// put sle values into local variables
ldt_fromdt = Date(sle_frodt.text)
ldt_todt = Date(sle_todt.text)
ll_associateid = Long(sle_associateid.text)

// test display to see check values going in.
Messagebox("debug",'FromDt: ' + String(ldt_fromdt,'MM/DD/YYYY') + ' ToDt: ' + String(ldt_todt,'MM/DD/YYYY') + ' AssociateId: ' + String(ll_associateid))

// retrieve data
li_rowcount = dw_1.Retrieve(ll_associateid, ldt_fromdt, ldt_todt)

// another test display showing number of rows retrieved.
Messagebox("debug 2",'Number of Rows Retrieved: ' + string(li_rowcount))






hi

1) open your report datawindow.
2) select from menu : view > preview.
3) select from menu : rows > retrieve

check what arguments it asks. pass proper arguments to it. If you r able to get your output here, pass same values from submit button and check .

Remember datatype and order of arguments must match in Retrieve() function and as u had defined in datawindow retrieval argument list.

Author

Commented:
Rich, thank you so much for your patience, and thorough answers! I had indeed forgot to put the ".text" after "sle_associateid". I know that Oracle is your strong point, but any time you need any help with Oracle 8i/9i, or Unix/Linux just let me know :-) thnx. Sandeep thanks for your feedback & helping out fellow desi ;-)

Author

Commented:
I have accepted Rich's last answer. However, it's not showing up as such! Why? If I accept it again, will I be giving out the points again? Thanks!
Thanks Xx1947xX. It must not be working right, maybe tomorrow. It usually will show one answer as being accepted. It was a pleasure to help. I see you already have lots of help on the radio button question so you should be on your way to completing that project. :)

Happy holidays.

Author

Commented:
Got a question, How come it says Rich only received only 5 points when I accepted the answer and gave him 20 pts.???

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial