5 Replies - 533 Views - Last Post: 24 June 2011 - 09:06 AM Rate Topic: -----

#1 boythesis10  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 23-June 11

QUESTION: How can i Select the Date from a Previous Field?

Posted 23 June 2011 - 10:38 PM

I wanted to get the Previous Field Value of LastDocDate column. How can i attain that with this formula?

the Query for this is:
This is my Formula.
rs.Open "SELECT * FROM Installment_w_DueDate where AccountNo = '" & txtAccountNo.text & "' and TotalAdjust <> 0", MDIMain.strConnect, adOpenStatic, adLockReadOnly

Penalty_y = Round(((rs!TotalAdjust * 0.05) / 30) * (DateDiff("d", DateAdd("d", 1, rs!lastdocdate), (dtPretDate.Value + 1))))
    penalty_tot = penalty_tot + Penalty_y



Is This A Good Question/Topic? 0
  • +

Replies To: QUESTION: How can i Select the Date from a Previous Field?

#2 boythesis10  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 23-June 11

Re: QUESTION: How can i Select the Date from a Previous Field?

Posted 23 June 2011 - 10:39 PM

here is my Query. What is wrong with this?
rs.Open "SELECT * FROM Installment_w_DueDate where LastDocDate < (SELECT LastDocDate FROM Installment_w_DueDate where AccountNo = '" & txtAccountNo.text & "')", MDIMain.strConnect, adOpenStatic, adLockReadOnly


Was This Post Helpful? 0
  • +
  • -

#3 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9204
  • View blog
  • Posts: 34,582
  • Joined: 12-June 08

Re: QUESTION: How can i Select the Date from a Previous Field?

Posted 24 June 2011 - 06:57 AM

It means your sub query is returning more than one row of data and SQL is not sure what to do since the < evaluation requires it be only one row.
Was This Post Helpful? 1
  • +
  • -

#4 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 817
  • Joined: 16-September 09

Re: QUESTION: How can i Select the Date from a Previous Field?

Posted 24 June 2011 - 07:19 AM

If i'm reading this correctly, you want to know how to scroll backwards in the recordset?

If so try adOpenDynamic, adLockBatchOptimistic

If thats not the issue, post a little bit more about what you're trying to do so we can try and help :)
Was This Post Helpful? 0
  • +
  • -

#5 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6063
  • View blog
  • Posts: 23,516
  • Joined: 23-August 08

Re: QUESTION: How can i Select the Date from a Previous Field?

Posted 24 June 2011 - 07:55 AM

Merged pretty much duplicate topics.
Was This Post Helpful? 0
  • +
  • -

#6 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: QUESTION: How can i Select the Date from a Previous Field?

Posted 24 June 2011 - 09:06 AM

View Postguyfromri, on 24 June 2011 - 03:19 PM, said:

If i'm reading this correctly, you want to know how to scroll backwards in the recordset?

If so try adOpenDynamic, adLockBatchOptimistic

If thats not the issue, post a little bit more about what you're trying to do so we can try and help :)

Personally, I would use a Static cursor for this rather than a dynamic one. Updatability isn't necessary and represents overhead.

Dynamic cursors are the most overhead of all, and should be reserved for times when multiple users need to be immediately aware of all insertions, edits, and deletions of one another to a given table or group of tables. (There are often better ways to accomplish this anyway.) Keyset cursors are updatable; other users will not be aware of additions, however. Also, the full row of data for any record is only fetched when the cursor hits the record. Only the keys are fetched when opening the recordset. Static cursor isn't updatable, and it is this type that can be disconnected from the database. I've found that either static or firehose (forward-only) cursors serve any purposes that I've ever needed: I do my updating using SQL action queries and refresh datasets manually as needed. I can't remember any time that I've used a dynamic or keyset cursor in a production environment, concurrency issues notwithstanding.

This post has been edited by BobRodes: 24 June 2011 - 09:06 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1