Can't get my If statement right

[Ally]

On the "Click" event of my Close command button I have various criteria to ensure that the user has filled in all the relevant fields. I now need to amend one slightly but can't get it right.

If the Outcome field has been filled in, but PAROnDisch hasn't (which is a value of -1) then a message box appears telling them to enter the PAROnDisch. Now there's extra criteria: all the unit numbers are numbers, except for "Ward". If the UnitNo = Ward then it's ok to close, so I tried adding "... And Me.UnitNo <> "Ward", but it doesn't work. It thinks it's ok even when the UnitNo is not a ward.

I have tried various sub "Ifs ... Then GoTo 1 ... GoTo 2" etc but that didn't work either.

This is the code as it is:

ElseIf Not IsNull([Outcome]) And Me.PAROnDisch = -1 And Me.UnitNo <> "Ward" Then

        msgbox "No PAR score on discharge entered.  Please enter.", vbOKOnly, _ 
              "Missing Information"
        DoCmd.GoToControl "PAROnDisch"

Anyone any ideas please?

 

[wh00t]

Dim wrd As Integer

wrd = Forms!FormName!Ward


ElseIf Not IsNull([Outcome]) And Me.PAROnDisch = -1 And Me.UnitNo <> wrd Then

msgbox "No PAR score on discharge entered. Please enter.", vbOKOnly, _
"Missing Information"
DoCmd.GoToControl "PAROnDisch"

 

[Ally]

Thanks, but unfortunately, this stops the whole thing from working. When I debug, it highlights:

DocName = "frmPatient"
LinkCriteria = "[unitno]=" & "'" & Me![UnitNo] & "'"
wrd = Forms!frmEpisode!Ward

... then skips all the way down to

Resume Exit_cmdClose_Click

 

[wh00t]

does the ward field contain just a number? or does it contain letters also

 

[Rich]

Try
ElseIf Not IsNull([Outcome]) And Me.PAROnDisch = -1 And Me.UnitNo Not "Ward" Then

ElseIf Not IsNull([Outcome]) And Me.PAROnDisch = -1 And Me.UnitNo Not = "Ward" Then

ElseIf Not IsNull([Outcome]) And Me.PAROnDisch = -1 Then
If Me.UnitNo Not "Ward" Then
etc.
one might work

 

[Ally]

Contains letters as well. It is a text field, specifically for the purpose of containing "Ward". All the other records are numeric, but obviously had to make it a text field.

 

[Fizzio]

Ally, a couple of other thoughts,
Have you tried
If Me.Unit = "Ward" Then
If Not IsNull([Outcome]) And Me.PAROnDisch = -1 Then
msgbox "No PAR score on discharge entered. Please enter.", vbOKOnly, "Missing Information" DoCmd.GoToControl "PAROnDisch"
end if
end if

Or trying
ElseIf Not IsNull([Outcome]) And Me.PAROnDisch = -1 And CStr(Me.UnitNo) <> "Ward" Then '(I thinks it's CStr - convert to string)

Just a stab in the dark!

 

[wh00t]

the way I am reading it is

if UnitNo is not equal to the word "Ward" then blah blah

so does the UnitNo field contain the word Ward or is it a number?

 

[Ally]

Thanks Fizz - your CStr one worked!

Thanks everyone - I did try all the others but they would work for one set of criteria, but then not on another.

Just to answer your question wh00t :

the way I am reading it is

if UnitNo is not equal to the word "Ward" then blah blah

so does the UnitNo field contain the word Ward or is it a number?

Yes, "Ward" is the word ward, not a number. Broken down, this is what I'm looking at:

• If UnitNo = a number and PAROnDisch =-1 (the default) then no good, as PAROnDisch must be 0 or above
  
• If UnitNo = "Ward" (text) and PAROnDisc = -1 then OK (as PAROnDisch N/A to "Ward")
  
• If UnitNo = a number and PAROnDisc = 0 or above, then OK

 

[wh00t]

I was confusing myself the more I read it

 

[Ally]

Just one thing I am confused about ...

If it's a text field anyway, why did I need to convert it to a string?!

 

[Fizzio]

Maybe Access is treating the contents like a variant and you are comparing that to a string? I dont know why I even suggested it but hey, it works