If/Then Problems 1

[Gaffi1]

We've got a form to enter employee hours worked on a specific project. The hours worked are calculated within a macro which (simply said) takes the stop time and subtracts the start time. The macro itself works. Our problem is this: When the macro runs and either the start or stop time field is null, the macro errors.

We wanted to put in VB code that would prevent this from happening, but for some reason, we can't get it to work out. We've tried many different combinations of if/then/else/exit sub/goto/etc. Here's one of the better ideas we came up with that did not work:

Private Sub StopTime_AfterUpdate()
Dim strMacro As String
strMacro = "PayrollStartStopUpdate"
If Me.[StartTime] = Null Or Me.[StopTime] = Null Then
     Exit Sub
Else
     DoCmd.RunMacro (strMacro)
End If
End Sub

Whichever way we organize this however, either the macro still runs and we get the error, or the macro does not run when it should. Is there something at all we are missing in the code, or is this something that Access can't handle (though I doubt that)?

 

[TheAceMan1]

How are ya Gaffi1 . . . . .

Try this:

[blue]If Not IsNull(Me!StartTime) And Not IsNull(Me!StopTime) Then
   [green]'Your Code[/green]
End If[/blue]

Even better:

[blue]If Len(Trim(Me!StartTime) & "") > 0 And Len(Trim(Me!StopTime) & "") > 0 Then
   [green]'Your Code[/green]
End If[/blue]

You may also want to take a look at the [purple]Nz[/purple] function . . . .

See Ya! . . . . . .

 

[ZmrAbdulla]

Hi,
Try converting Macro to VBA code.Macros are sometime head ache stops running without giving any clue.
If you are in Access 2000 or higher there is an easy way to convert.
Tools > Macro > Convert Form's Macro to Visual Basic
To do this you have to bind the Macro any of your control either command button or any other control

Also there is XOR Operator in VBA. You may try that also.
For more details type XOR Operator in Help when you are in the code window.

Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[jsteph]

A simpler line:

If trim("" & Me.[StartTime]) = "" Or trim("" & Me.[StopTime]) = "" Then

Either Null or empty string will return "" with the above, no need to check Len(). Null errors are discarded in the Trim() function when concated with "", so the macro will not halt with error.

Also, for checking just null, when you want to differentiate between Null and "", use IsNull(xxx) instead of If xxx = null, or like aceman said, use Nz().
--Jim

 

[Gaffi1]

Thank you all so much for the quick and effective replies. I've got it figured out now.

Sorry for the delay in my reply- I've been working on this so much I just haven't had time.

Thank you so much again. =D

 

[Gaffi1]

Also, just to let you all know, I got it to work simply using the IsNull() function.