IT SOLUTIONS
Your full service technology partner! 
-Collapse +Expand
Paradox
Search Paradox Group:

Advanced
-Collapse +Expand Paradox To/From
To/FromCODEGuides
-Collapse +Expand Paradox Store
PRESTWOODSTORE

Prestwood eMagazine

December Edition
Subscribe now! It's Free!
Enter your email:

   ► MB LobbyCorel Paradox / ObjectPAL Coding BoardObjectPAL Topic   Print This     

query between dates

query between dates in ObjectPAL topic (part of our Corel Paradox / ObjectPAL Coding group).

Quick Search: between   query between   query between dates  
boardBTCH
 (Inactive)
Ottawa, Ontario, Canada
I don't know what is happening but I hope someone is out there that can give me a better insight to this.
Have a table with fields, name A 25,
Ctime T and Cdate D.
I need to query by the name between two dates based on the Cdate field.
I have created a form with three fields on it, One for the name and the other two for the dates. These fields are undefined.
In the pushbutton to execute I have code like this:

method pushButton(var eventInfo Event)
var
q query
r Report
memName String
frDate, toDate Date
tv Tableview
endVar

memName = MEMBER.value
frDate = FROM_DATE.value
toDate = TO_DATE.value

q = Query
ANSWER: :PRIV:FTLMEMBERREPORT.DB

T1.DB | RECORD_NO | CTIME | CDATE |
| _join1 | Check | Check >=~frDate, <=~toDate |

T2.DB | RECORD_NO | NAME |
| _join1 | Check ~memName..|

EndQuery
executeQBE(q)
tv.open(":PRIV:FTLMEMBERREPORT.DB")

What is happening is when I enter dates on my form, I get nothing in the table. If I eliminate the dates and just have the name, I get records returned.
Why is this happening? I'm using the Windows Short format, mm/dd/yyyy, the default.
How can I set up a query so users can enter whatever name they want between two dates?
HELP
Confused
 Posted 20 years ago (Thread Starter)
Comment Quote
About boardBTCH -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Email Not Verified!
Once email is verified, we will review and approve the account.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #2119, 8 replies
Thread Started 5/12/2001 8:44:00 AM
View Counter=2626
Last Reply Posted 5/16/2001 7:47:00 AM)
Location=Ottawa, Ontario, Canada  
Joined=21 years ago   MB Posts=19  
boardBTCH
 (Inactive)
Ottawa, Ontario, Canada
But I don't understand why I would use a String variable when the datatype for the CDATE field is DATE.
If I use the for syntax that Joe mentioned, I get an error on the CDATE field in the query. It says that the expression is invalid.
Why can't I use a date variable for a datatype Date field? Confused
 Posted 20 years ago (Thread Starter)
Comment Quote
About boardBTCH -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Email Not Verified!
Once email is verified, we will review and approve the account.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #2141 (Level 1.1)  Reply to 2119
Thread Started 5/14/2001 11:32:00 AM
View Counter=2
Location=Ottawa, Ontario, Canada  
Joined=21 years ago   MB Posts=19  
boardBTCH
 (Inactive)
Ottawa, Ontario, Canada
As I was waiting for your replies, I kept fooling around with this as I couldn't understand what the heck was going on. And all of a sudden "KABLAM"....it works!!! This is what I did and I have absolutely no explaination for it.
I rebuilt my query from scratch but this time I added values into my query. So on the CDATE field I put >=04/01/2001, <=04/30/2001 and in the NAME field I put BENDER..
I saved that, then I created another form, did a Paste From and copied the recreated query. Ran that and I received an answer. I then one by one, changed the entered values with the variables. >=04/01/2001 to ~fDate, <=04/30/2001 to ~tDate and BENDER to ~mem..
Then I would run the form enter values like a user would and PRESTO....I get answers. I looked at my original code to the final on this test one and I do not see any difference at all. I have no explaination why I would have to insert a query with values in it, change those values to variables then it works?????? Clueless!!!
So this is the code that I have that works:
method pushButton(var eventInfo Event)
var
qry Query
memName String
dFrom, dTo Date
tv Tableview
endVar

memName = member.value
dFrom = from_date.Value
dTo = to_date.value

qry = Query
ANSWER: :PRIV:FTLMEMBERREPORT.DB

:TACTFTL:FirearmsLog.DB | RECORD_NO | CDATE
| _join1 | Check >=~dFrom, <=~dTo |

:TACTFTL:Members.DB | RECORD_NO | NAME |
| _join1 | Check ~memName.. |

EndQuery
If not(executeQBE(qry)) THEN
errorshow()
RETURN
ELSE
tv.open(":PRIV:FTLMEMBERREPORT.DB")
ENDIF

So it works....thanks for all your help.
If anyone of you have an explaination, please post it so I can understand it myself.
Thanks
Tracy.
 Posted 20 years ago (Thread Starter)
Comment Quote
About boardBTCH -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Email Not Verified!
Once email is verified, we will review and approve the account.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #2162 (Level 1.2)  Reply to 2119
Reply Posted 5/15/2001 10:29:00 AM
Location=Ottawa, Ontario, Canada  
Joined=21 years ago   MB Posts=19  
boardBTCH
 (Inactive)
Ottawa, Ontario, Canada
Again thank you Al. I will try the code to test the BDE and Paradox. If anything "strange" happens, I'll inform the group.
Ciao.
Tracy
 Posted 20 years ago (Thread Starter)
Comment Quote
About boardBTCH -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Email Not Verified!
Once email is verified, we will review and approve the account.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #2170 (Level 1.3)  Reply to 2119
Reply Posted 5/16/2001 3:16:00 AM
Location=Ottawa, Ontario, Canada  
Joined=21 years ago   MB Posts=19  
AIBreveleri
The code in your original post should work. The problem probably resides in a difference between two date format settings: The one that Paradox is using and the one that the BDE is using.

When you code

   frDate = FROM_DATE.value
toDate = TO_DATE.value


the date values that you entered into the fields are converted to type date. This uses the current Paradox default date format, which you say is Windows Short.

When you execute a query, the BDE converts any extracted date column values to strings, using the current BDE date format setting. When you use a date type variable as a QBE parameter (tilde-variable), it is converted to a string before being inserted into the query. This conversion also uses the current BDE date format setting, in the hopes that this is the best chance of actually matching the table data.

The value of the data in the date type variables 'frDate' and 'toDate' can be examined by coding

var SI smallint endvar
...
frDate = FROM_DATE.value
toDate = TO_DATE.value
SI = year(frDate) SI.view("from year")
SI = month(frDate) SI.view("from month")
SI = day(frDate) SI.view("from day")
SI = year(toDate) SI.view("to year")
SI = month(toDate) SI.view("to month")
SI = day(toDate) SI.view("to day")
...


To see what your tilde-variables got turned into, insert this line into your code just before the 'executeQBE()' statement:

   q.writeQBE(":PRIV:__TMOUK.QBE")


Run the code, then open the file ':PRIV:__TMOUK.QBE' with a text editor. You will see the query with the actual values substituted for the parameters. If these values do not look like the values you typed into the unbound fields, then Paradox and the BDE are probably using different default date formats.

-Al
 Posted 20 years ago
Comment Quote
About AIBreveleri -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #2165 (Level 1.4)  Reply to 2119
Reply Posted 5/15/2001 11:50:00 AM
Location= 
Joined=20 years ago   MB Posts=286   KB Comments=8  
Most Recent Post
AIBreveleri
Apparently while I was writing my last post, you fixed your problem. If you got the query working, you don't need to waste time checking the various values and formats now. Of course you will keep the suggested methods in your toolkit for future use.

-Al.
 Posted 20 years ago
Comment Quote
About AIBreveleri -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #2175 (Level 1.5)  Reply to 2119
Reply Posted 5/16/2001 7:47:00 AM
Location= 
Joined=20 years ago   MB Posts=286   KB Comments=8  
Joe Ng
-- USA
Try this:

Var
...
sFromDate String
sToDate String
...
EndVar

sFromDate = Format("DM2,DD2,DY3",FROM_DATE.Value)
sToDate = Format("DM2,DD2,DY3",TO_DATE.Value)

Q=Query
...
|Check >=~sFromDate, <=~sToDate |
...
EndQuery
 Posted 20 years ago
Comment Quote
About Joe Ng -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #2130 (Level 1.6)  Reply to 2119
Reply Posted 5/14/2001 5:46:00 AM
Location=-- USA 
Joined=20 years ago   MB Posts=80  
Joe Ng
-- USA
Since sd.view returns a string value, as long as the user enters valid dates, Dave's query will work.
The "solution" I posted earlier only applies to older version of Pdox, think Y2K.
This shouldn't not be a problem if tmouk is using Pdox 9, this also applies to Atroxell's solution.

I would enforce date rules at FROM_DATE and TO_DATE field level.
That is, do not let the user leave the fields until valid dates are entered.
Rules I would enforce are:
field must be in mm/dd/yyyy format.
User can enter dates in this exact format or
use codes to make the field into this format.
Can From date be blank?
Can To date be blank?
If From date is not blank, then make sure To date is after From date.

With these rules, you have ensured the data integrity.
Now, you don't have to worry about them.
 Posted 20 years ago
Comment Quote
About Joe Ng -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.

Post ID #2158 (Level 1.7)  Reply to 2119
Reply Posted 5/15/2001 6:56:00 AM
Location=-- USA 
Joined=20 years ago   MB Posts=80  
DaveCasper
 (Inactive)
Dallas
Here's how I handle a something similar using "view" to handle the data input...

sd = "bill from?"
sd.view("Please Enter the Start Date...")
ed = "bill to?"
ed.view("Please Enter the End Date...")

q=Query

workorder.db | Date Ordered | Date Completed | Setup Notes |
| Check >=~sd, <=~ed | Check | Check |


EndQuery

executeQBE(q, "temp.db")
 Posted 20 years ago
Comment Quote
About DaveCasper -Collapse +Expand
Visit Profile
Inactive member.
Member does not subscribe to this thread.
Email Not Verified!
Once email is verified, we will review and approve the account.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #2142 (Level 1.8)  Reply to 2119
Reply Posted 5/14/2001 12:42:00 PM
Location=Dallas  
Joined=20 years ago   MB Posts=2  

Revive Thread!

Add a comment to revive this old thread and make this archived thread more useful.

Write a Comment...
Full Editor
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P195A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #2119 Counter
2626
Since 4/2/2008
Go ahead!   Use Us! Call: 916-726-5675  Or visit our new sales site: 
www.prestwood.com


©1995-2021 Prestwood IT Solutions.   [Security & Privacy]