Macro If - Else statement Not Working

[santhosh23]

Hi ..

I have a subform which stores 3 VALUES Start Time , End Time and Work Time..I am creating a macro to check for the worktime greaterthan 8 hrs and send email to particular person . This is the macro function I used..

If [Forms]![RecordSubform]![Label17]>=8 then
email database object

Else
Msgbox .."No user Logged in morethan 8 hrs"..

when i executed this query it always goes to else statement and I receive this message box ..while my subform has the below values..

StartTime End Time WorkTime
07/27/15 04:55:27 PM 07/27/15 04:56:39 PM 00:01:12
07/27/15 04:57:03 PM 07/27/15 04:58:10 PM 00:01:07
07/27/15 04:58:26 PM 07/27/15 04:59:55 PM 00:01:29
07/28/15 05:22:03 PM 07/29/15 07:01:13 AM 13:39:10
07/29/15 08:04:46 AM 07/29/15 08:04:50 AM 00:00:04
07/29/15 08:05:27 AM 07/29/15 08:05:33 AM 00:00:06
07/30/15 07:40:03 AM 07/30/15 07:40:09 AM 00:00:06
07/30/15 03:11:14 PM 07/30/15 03:11:18 PM 00:00:04
07/31/15 07:49:14 AM 07/31/15 04:19:55 PM 08:30:41
26:07:15 10:24:35 PM 26:07:15 10:24:39 PM 00:00:04

Can anyone please help on this..

Thanks in advance

 

[Gasman]

You appear to be testing the label NOT the textbox.?

Try Textbox17 instead

 

[arnelgp]

should be:

If [Forms]![yourMainFormName]![RecordSubform].[Form]![Label17]>=8 then
email database object

 

[santhosh23]

Hi Gasman ..

Thanks for the reply ..I tried to execute the code but I got the type mismatch error number 13...I think the value is in text and i need to convert to number before executing the if -else statement....do you have any suggestions..

 

[santhosh23]

@ arnelgp..

thanks for the reply...I tried that code and executed this below query ,,
[Forms]![Navigation Form]![RecordSubform].[Form]![Label17]>=8
but again i get the Else Msgbox .."No user Logged in morethan 8 hrs".

but my subform consists of 2 values morethan 8 hrs..

07/27/15 04:58:26 PM 07/27/15 04:59:55 PM 00:01:29
07/28/15 05:22:03 PM 07/29/15 07:01:13 AM 13:39:10
07/29/15 08:04:46 AM 07/29/15 08:04:50 AM 00:00:04
07/29/15 08:05:27 AM 07/29/15 08:05:33 AM 00:00:06
07/30/15 07:40:03 AM 07/30/15 07:40:09 AM 00:00:06
07/30/15 03:11:14 PM 07/30/15 03:11:18 PM 00:00:04
07/31/15 07:49:14 AM 07/31/15 04:19:55 PM 08:30:41
26:07:15 10:24:35 PM 26:07:15 10:24:39 PM 00:00:04

 

[arnelgp]

if Label17 in the Main form:

If Val([Forms]![yourMainFormName]![Label17].[Caption])>=8 then
email database object

if Label17 in subform:

If Val([Forms]![yourMainFormName]![RecordSubform].[Form]![Label17].[Caption])>=8 then
email database object

 

[santhosh23]

Hi arnelgp ... I executed this query.. Val([Forms]![Navigation Form]![RecordSubform].[Form]![Label17])>=8 but again I got the else statement msgbox...

 

[Gasman]

Walk through the debugger and see what the value actually is.

I've just created a textbox on the form, did not set any format, and testing for numeric works fine.

BTW you are still using the label?

 

[santhosh23]

@ Gasman ..Yeah I am still using the label when I change to txtbox I am getting a error..my subform has the textbox in Plaintext format..do I need to change that and use textbox in my macro query..??

 

[Gasman]

You say Macro, but you are using VBA code? Yes?

Change the format of the textbox to a suitable format like General Number?

You have to use the correct control to start with, regardless of whether you get an error or not.

Test for "8" just to see what is in the textbox.?

 

[santhosh23]

@GASMAN ..I am using MACRO not VBA ..I am not able to change my format for my worktime..and in textbox i have only plain/rich text format...when I executed this query [Forms]![RecordSubform]![Text16]=8..i get an type mismatch error.

 

[Gasman]

@GASMAN ..I am using MACRO not VBA ..I am not able to change my format for my worktime..and in textbox i have only plain/rich text format...when I executed this query [Forms]![RecordSubform]![Text16]=8..i get an type mismatch error.

So test for "8" just to see what it holds?

I've not used Macroes like that, only as discrete steps.

 

[santhosh23]

@Gasman ..yeah tried the code like this if 8 then email ..when executed I got the if statement executed and the email options comes up ..so this is what i need to use to check ?? what does this statement means in BTW

 

[arnelgp]

if Text16 in the Main form:

If Val([Forms]![Navigation Form]![Text16])>=8 then
email database object

if Text16 in subform:

If Val([Forms]![Navigation Form]![RecordSubform].[Form]![Text16])>=8 then
email database object

 

[santhosh23]

@ arnelgp...I am getting else statement executed for the query ..
Val([Forms]![Navigation Form]![RecordSubform].[Form]![Text16])>=8 ...again the same issue the if statement is not executed

 

[arnelgp]

is it possible for you to upload your db?

 

[santhosh23]

@ arnelgp..I can email to your personal mail ID..will that works for you..

 

[arnelgp]

ag_puzon@yahoo.com

 

[Gasman]

Good luck with it. I'd be looking as to why the value is not numeric and correcting that.

 

[santhosh23]

@ gasman..Can you explain y does the statement sends email when we just put numeric in if statement..