Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

problem with locating and moving to a specific record

Posted on 2011-04-24
5
Medium Priority
?
301 Views
Last Modified: 2012-05-11
Have a form to select a customer from a drop down box and then move a main form to that found record. However it is not exactly working. Here is the code i am using:

SELECT eventbook
SET ORDER TO closest ASCENDING
SET NEAR ON
SEEK PadR(cFindcustomer,Len(eventbook.cust))+DToS(Date())

SET NEAR off
RELEASE cFindcust
Clear
Thisform.Release()

This code finds the record i searched for but moves to the very next record. On my main form i simply need to hit a button i have created to move back one in the records and boom, there it is.  it is a functional work around but I figure there is something that i am missing in the code to make it stay on the record i was searching for and not going to the next record.
0
Comment
Question by:phantomcom
5 Comments
 

Author Comment

by:phantomcom
ID: 35457962
Oops. The release line should read RELEASE cFindcustomer.  however this doesn't change the problem.
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 35458205
When you are searching using near, it will go to the next record in case it did not find a match. So you have to compare the variable with the record and use less criteria in your search. For example use the client with the last month, or last year, ...

Or you can filter the records of that customer only and proceed to the last date for that customer in case non-matching records are found.

I only use the NEAR to get currency rates. Otherwise, I use the filtered records solution.
0
 
LVL 43

Accepted Solution

by:
pcelba earned 1000 total points
ID: 35458390
Yes, I agree to Captain's answer.

You may also do it following way:

SELECT eventbook
SET ORDER TO closest DESCENDING
SET NEAR ON
IF SEEK( PadR(cFindcustomer,Len(eventbook.cust))+DToS(Date())) OR eventbook.cust = cFindcustomer
  *-- Record was found
ELSE
  *-- Record not found
ENDIF
SET NEAR off
0
 
LVL 30

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 1000 total points
ID: 35460681
To judge from the seek you have an index tag "closest" on cust + dtos(datefield).

In most cases this will not have an entry for the current date. As you have NEAR ON therefor it's most probable you find go over the last record of the customer you want to find to the next customer. NEAR always means nerest in order, if you set the order DESCENDING instead, you will find the record you want to find. Another fix would be to search with no dtos part.

So either 1)

SELECT eventbook
SET ORDER TO closest DESCENDING
SET NEAR ON
SEEK PadR(cFindcustomer,Len(eventbook.cust))+DToS(Date())

Open in new window


OR 2)

SELECT eventbook
SET ORDER TO closest ASCENDING
SET NEAR ON
SET EXACT OFF
SEEK cFindcustomer

Open in new window


1) will let you find the last record of the customer.
2) will let you find the first record of the customer.

Both will again find the next customer, if there is no record at all for the searched cFindcustomer, so you also better add code to check at what customer you arrive after the seek.

Bye, Olaf.
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 35460770
You can even seek cFindcustomer in case 1), as it always finds the last record due to descending order, so you only need EXACT OFF, not NEAR ON. It's also simple to see of you got no record for a customer, as you then will be at EOF() or BOF(). You could also use the SEEK() function and only in case it returns .T. display/process the found record.

This translates to

1)
SELECT eventbook
SET ORDER TO closest DESCENDING
SET EXACT OFF
IF NOT SEEK(cFindcustomer,"eventbook","closest")
    Messagebox("not found")
ENDIF

Open in new window


2)
SELECT eventbook
SET ORDER TO closest ASCENDING
SET EXACT OFF
IF NOT SEEK(cFindcustomer,"eventbook","closest")
    Messagebox("not found")
ENDIF

Open in new window


You see, this way it's only a matter of ascending or descending order to find the last or first record of the customer and you get a .F. from SEEK() if no record is found.

Bye, Olaf.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
In this article I will be showing you how to subnet the easiest way possible for IPv4 (Internet Protocol version 4). This article does not cover IPv6. Keep in mind that subnetting requires lots of practice and time.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question