Using an IIf Statement in an SQL

[JezLisle]

I have this SQL below but what I want to do is if the ComboBox cboChangedDestination is amended then input the UserName and Date into the ChangedInputBy & ChangedInputDate

Private Sub cmdSubmit_Click()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim varResponse As Variant
Dim intPatientID As Integer
'*************************************************************************************
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
        cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
        "Data Source=" & cTables
'*************************************************************************************
    varResponse = MsgBox("Save Changes?", vbYesNo, cApplicationName)
        If varResponse = vbNo Then
            Me.Undo
                Exit Sub
        End If
    DoCmd.SetWarnings False
    Me.cboGender.Enabled = False
    Me.cboReferringAgent.Enabled = False
    Me.cboReferralDestination.Enabled = False
    Me.cboReasonNotAccepted.Enabled = False
    Me.cboChangedDestination.Enabled = False
    Me.cmdAddNew.Enabled = True
    Me.cmdSearchRecords.Enabled = True
    Me.cmdMainMenu.Enabled = True
    
    DoCmd.RunCommand acCmdSaveRecord
        sQRY = ""
            sQRY = sQRY & "UPDATE tblICReferralRecord "
            sQRY = sQRY & "SET [PatientRef] = '" & Me.txtPatientID & "' & '" & Me.txtForename & "'"
            sQRY = sQRY & ", [Forename] = '" & Me.txtForename & "'"
            sQRY = sQRY & ", [Surname] = '" & Me.txtSurname & "'"
            sQRY = sQRY & ", [Name] = '" & Me.txtForename & "'&' '&'" & Me.txtSurname & "'"
            sQRY = sQRY & ", [Address1] = '" & Me.txtAddress1 & "'"
            sQRY = sQRY & ", [Address2] = '" & Me.txtAddress2 & "'"
            sQRY = sQRY & ", [Address3] = '" & Me.txtAddress3 & "'"
            sQRY = sQRY & ", [Postcode] = '" & Me.txtPostcode & "'"
            sQRY = sQRY & ", [Gender] = '" & Me.cboGender & "'"
            sQRY = sQRY & ", [DateOfBirth] = '" & Me.txtDOB & "'"
            sQRY = sQRY & ", [Age] = '" & Me.txtAge & "'"
            sQRY = sQRY & ", [ReceivedDate] = '" & Me.txtReceivedDate & "'"
            sQRY = sQRY & ", [ReceivedTime]= '" & Me.txtReceivedTime & "'"
            sQRY = sQRY & ", [ReferringAgent]= '" & Me.cboReferringAgent & "'"
            sQRY = sQRY & ", [ReferralDestination] = '" & Me.cboReferralDestination & "'"
            sQRY = sQRY & ", [Comments]='" & Me.txtComments & "'"
            sQRY = sQRY & ", [ReasonNotAccepted]='" & Me.cboReasonNotAccepted & "'"
            sQRY = sQRY & ", [ChangedDestination]='" & Me.cboChangedDestination & "'"
            sQRY = sQRY & ", [ChangedDestinationComments]='" & Me.txtChangedComments & "'"
            sQRY = sQRY & ", [ChangedInputDate]= '" & VBA.Now & "'"
            sQRY = sQRY & ", [ChangedInputBy]= '" & fOSUserName & "'"
            sQRY = sQRY & ", [InputBy] = '" & fOSUserName & "'"
            sQRY = sQRY & ", [InputDate] = '" & VBA.Now & "'"
            sQRY = sQRY & ", [InputFlag] = 1 "
            sQRY = sQRY & "WHERE tblICReferralRecord.PatientID = " & Forms!frmInputData!txtPatientID
    DoCmd.RunSQL sQRY
    
    Me.txtDummy.SetFocus
    Call LockAll
    Exit Sub
End Sub

How can this be done?

 

[DCrake]

You can change VBA.Now to Now()

What is in the variable fOSUserName? Is this a public variable? Your syntax is correct.

Which element is not working?

David

 

[JezLisle]

It all works as it is, but what I want to change is that when the ComboBox cboChangedDestination is amended. Then the ChangeInputUser & ChangeInputDate need to be written, else leave blank.

 

[DCrake]

Ok then, Delcare a boolean variable in your forms declarations, say bFlag

Dim bFlag As Boolean

Then on the after update of the combobox set the bFlag to True

bFlag = True

Then on your update line

[ChangedInputBy]= '" & IIf(bFlag = True,fOSUserName,"") & "'"

Finally at the end of the sub reset the bFlag back to False

bFlag = False

 

[JezLisle]

Excellent Thanks, I'll give it a go