If (condition) true then value = x, else value = null

[Guest]

I would like the code below to do this:
If the HSILFinalLetter field has a date (i.e. is not null)
and the HSILLetterResponse field (data type is text) doesn't have the string
"preg*" in it,
Then make the value of the HSILSurgicalDx field = 8
but if the HSILLetterResponse field has "preg*" in it somewhere, then leave
the HSILSurgicalDx field value as Null

After I tested the following code, the problem is that the HSILSurgicalDx
has an 8 regardless of the HSILLetterReponse field having "Preg*" or not.

How do I accomplish what I'd like to do with this code? Let me know if you
need any clarification about my info.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(HSILFinalLetter) = True Then
If (HSILLetterResponse <> "preg*") Then
HSILSurgicalDx.Value = 8
Else
HSILSurgicalDx.Value = Null
End If
End If
End Sub

 

[Dirk Goldgar]

I would like the code below to do this:
If the HSILFinalLetter field has a date (i.e. is not null)
and the HSILLetterResponse field (data type is text) doesn't have the
string "preg*" in it,
Then make the value of the HSILSurgicalDx field = 8
but if the HSILLetterResponse field has "preg*" in it somewhere, then
leave the HSILSurgicalDx field value as Null

After I tested the following code, the problem is that the
HSILSurgicalDx has an 8 regardless of the HSILLetterReponse field
having "Preg*" or not.

How do I accomplish what I'd like to do with this code? Let me know
if you need any clarification about my info.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(HSILFinalLetter) = True Then
If (HSILLetterResponse <> "preg*") Then
HSILSurgicalDx.Value = 8
Else
HSILSurgicalDx.Value = Null
End If
End If
End Sub

Your statement of what you're after isn't entirely clear, but you might
start with something like this:

If IsNull(HSILFinalLetter) = False Then

If HSILLetterResponse Like "*preg[*]*" Then
HSILSurgicalDx.Value = Null
Else
HSILSurgicalDx.Value = 8
End If

End If

That's based on your statement that you want to look for the string
"preg*" in HSILLetterResponse somewhere. By that I take it that you
actually do need to see the '*' on the end of the string, and you want
to know if that f-ve-character string appears anywhere in the field.

 

[Guest]

Thanks! That did the trick.

I would like the code below to do this:
If the HSILFinalLetter field has a date (i.e. is not null)
and the HSILLetterResponse field (data type is text) doesn't have the
string "preg*" in it,
Then make the value of the HSILSurgicalDx field = 8
but if the HSILLetterResponse field has "preg*" in it somewhere, then
leave the HSILSurgicalDx field value as Null

After I tested the following code, the problem is that the
HSILSurgicalDx has an 8 regardless of the HSILLetterReponse field
having "Preg*" or not.

How do I accomplish what I'd like to do with this code? Let me know
if you need any clarification about my info.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(HSILFinalLetter) = True Then
If (HSILLetterResponse <> "preg*") Then
HSILSurgicalDx.Value = 8
Else
HSILSurgicalDx.Value = Null
End If
End If
End Sub

Your statement of what you're after isn't entirely clear, but you might
start with something like this:

If IsNull(HSILFinalLetter) = False Then

If HSILLetterResponse Like "*preg[*]*" Then
HSILSurgicalDx.Value = Null
Else
HSILSurgicalDx.Value = 8
End If

End If

That's based on your statement that you want to look for the string
"preg*" in HSILLetterResponse somewhere. By that I take it that you
actually do need to see the '*' on the end of the string, and you want
to know if that f-ve-character string appears anywhere in the field.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)