Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2006
    Posts
    48

    Unhappy Unanswered: Updating field based on criteria

    So here's my problem...I have a table with 5 fields, - cheque #, cheque date, amountpaid, amountreceived and status. When the user clicks on a command button call it 'cmdUpdate' on the form 'MyForm', I need the system to check the amountpaid and amountreceived fields and if they (the values in the fields)match, the status should be set to 'Cashed'; if the present date is 6 months or more greater than the cheque date, the status should be set to 'Stale' and for all others the status should be set to 'unpresented'. Could you help me with the vb code to run behind the button for this to take place? Presently the code behind the button is; SO I need to add some statements where the blue text is written)
    Private Sub cmdAppendCheques_Click()

    'turn off warnings
    DoCmd.SetWarnings False

    'append validated cheques to master
    DoCmd.OpenQuery "qryAddCashToMaster"
    (This is where the other code should be and should read something like
    if tblData.amountpaid = tblData.amountreceived then
    tblStatus = "Cashed"
    elseif...
    Could you help me out)

    'clear the appended cheques
    DoCmd.OpenQuery "ClearPostedCashedCheques"

    'refresh the screen
    Me.Requery

    'turn on the warnings
    DoCmd.SetWarnings True
    End Sub

    Can anyone help me out? Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Try this one line:

    Code:
    CurrentDb.Execute "UPDATE tblData SET Status=IIF(amountpaid=amountreceived,'Cashed'," & _
                         "IIF(DateDiff('m', [cheque date], Now) < 6,'Unpresented','Stale'))"
    If you want to compare the 6 months to say 180 days then you simply need to change the 'm' located within the DateDiff statement to 'd' then change the 6 to 180.

    If you want to compare to Weeks then simply change the 'm' located within the DateDiff statement to 'ww' then change the 6 to whatever number of weeks you want.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Jun 2006
    Posts
    48
    CyberLynx,

    You are fabulous! Thank you tonnes. That line of code work good...it really shows!

    I have another question pertaining to the first, with respect to stale records, some records contain null cheque date cells and as another check for the stale status, the cheque date field should not be null, could I used an 'And' within that statement you give?
    For instance, IIF [cheque date] IsNotNull And DateDiff('m', [cheque date], Now) < 6,'Unpresented','Stale'))"

    Thanks.

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487

    Question

    Something like that...here you go:

    Code:
    CurrentDb.Execute "UPDATE tblData SET Status=IIF(amountpaid=amountreceived,'Cashed'," & _
                         "IIF(IsNull([cheque date])=False And DateDiff('m', [cheque date], Now) < 6,'Unpresented','Stale'))"
    .

    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Jun 2006
    Posts
    48
    Well, there you go again...a great display of your self taught skills and knowledge. Thanks much for speedy responses.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •