Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Posts
    7

    Exclamation Unanswered: How to write Update query,after checking a Field has value Or not in the Record which

    How to write Update query,after checking a Field has value Or not in the Record which have to updated

    Hi,

    i am using vb6 and the database is in access, i have fields with following datatype in the table

    AreaName Text
    Equipment Text
    NextTIDate Date/Time
    Duration Number
    CompletionDt Date/Time

    when the user choose a date in the MonthView control i update the choosen date to the NexTI Date by the Following query.

    update EqMaster set NextTIDate =#" & MonthView1.Value & "# Where AreaName='" & ArNm & "' And NextTiDate=#" & OldTiDt & "#"

    i need to check if the Duration field is empty or not, if it is empt, then the query should put the same MonthView1.Value to the CompletionDt Field. the data may or may not present in the CompletionDt Field in the table.

    AreaName NextTiDt Duration CompletionDt

    Area1 1/1/2005 10 1/11/2005
    Area2 3/10/2005 10
    Area3 3/10/2005

    assume the table by default contains the above values, some records will have values & some will not.

    if i am going to update NextTiDt Field where the AreaName is "Area 1" with the new MonthView1.Values = 1/1/2006 then the CompletionDt Field should be updated for the Record "Area1" as 1/11/2006 for 1st record (i.e after adding the 10 with the MonthView Control Value)

    for second record if MonthView1.Value = 10/10/2005 then CompletioDt for the Record Area2 should be updated as 10/20/2005

    for 3rd record if MonthView1.Value = 10/10/2005 then then the 3 rd record should be as below

    AreaName NextTiDt Duration CompletionDt

    Area3 3/10/2005 3/10/2005

    Kindly check this and reply me.

    Thankyou,
    Chock.

  2. #2
    Join Date
    Sep 2005
    Location
    Schaumburg, IL
    Posts
    28
    can u post your code so we may have a good view either your using ADO or something?

  3. #3
    Join Date
    Aug 2002
    Posts
    7
    Hi,

    Code:
    Private Sub cmdSave_Click()
       If CurSpread = "DecfpSpr" And FromENo = True Then
        fpSprMnu12.GetText 1, 1, ArNm
        
        SQL = "update EquipMaster Set Nextti=#" & MonthView1.Value & "# where ArName='" & ArNm & "' And ENo='" & ENm & "' And Nextti=#" & CurNextti & "#"
        cnnNew.Execute SQL
        FrameCal.Visible = False
        FromENo = False    
        FrameCal.Visible = False
        FrameParam.Visible = True
        fpSprMnu12.Visible = True
        lblYr.Visible = True
        Frame1.Visible = True
        cmdExit.Visible = True    
        Call FillCal
     ElseIf FromAr = True And CurSpread = "DecfpSpr" And FromStat = "Area" Then
        SQL = "update EquipMaster Set Nextti=#" & MonthView1.Value & "# where ArName='" & FromArNm & "' And Nextti=#" & CurNextti & "#"
        cnnNew.Execute SQL
        FromAr = False
        
        FrameCal.Visible = False
        FrameParam.Visible = True
        fpSprMnu12.Visible = True
        lblYr.Visible = True
        Frame1.Visible = True
        cmdExit.Visible = True
        
        Call FillCal
     ElseIf CurSpread = "DecfpSpr" And FromAr = False And FromENo = False Then
        If (Mid(FromArNm, Len(FromArNm) - 8, Len(FromArNm))) = "    Major" Or (Mid(FromArNm, Len(FromArNm) - 8, Len(FromArNm))) = "    Minor" Then
            FromArNm = Mid(FromArNm, 1, Len(FromArNm) - 9)
            If Mid(FromArNm, 5, 2) = "  " Then
                FromArNm = Mid(FromArNm, 7, Len(FromArNm))
            End If
        ElseIf Mid(FromArNm, (Len(FromArNm) - 3), Len(FromArNm)) = "    " Then
            FromArNm = Mid(FromArNm, 1, Len(FromArNm) - 4): FromArNm = Mid(FromArNm, 7, Len(FromArNm))
        Else
            FromArNm = Trim(FromArNm)
        End If
        
        SQL = "update EquipMaster Set Nextti=#" & MonthView1.Value & "# where ENo='" & FromArNm & "' And Nextti=#" & CurNextti & "#"
        cnnNew.Execute SQL
        
        FrameCal.Visible = False
        FrameParam.Visible = True
        fpSprMnu12.Visible = True
        lblYr.Visible = True
        Frame1.Visible = True
        cmdExit.Visible = True
        Call FillCal
     End If  ' DecfpSpr
    End Sub

    The Update statement marked with blue is the statement has to be changed.



    FrontEnd : VB6
    Database: MsAccess

    The subject of my issue is

    Is it possible to Check the following conditions in the Update query and execute the Update query

    1. Need to find whether the Duration Field has value or not in the Record which its going to be Updated.

    2. If Duration has value then the NextTiDt Field Value + Duration Field Value will be added & updated in the CompletionDt
    Field.


    I need to do this in the same Update query.
    I don't need to open a recordset and do the job, is it possible to do it within the same Update query.

    Any help will be appreciated.

    Thankyou,
    Chock.

  4. #4
    Join Date
    Dec 2004
    Location
    Laguna, Philippines
    Posts
    147
    i dont think its possible coz you cant use IF ELSE or CASE STATEMENTS w/in access queries.. but if you could find a way to use those statements then it is my solution to your problem otherwise you have to open a recordset..
    Give me a place to code and i shall move the earth!

Posting Permissions

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