Alpha Video Training
Results 1 to 18 of 18

Thread: Testing for Null Time value.

  1. #1
    Member
    Real Name
    Tony Saladino
    Join Date
    Aug 2005
    Posts
    77

    Default Testing for Null Time value.

    I'm trying to verify a value of type time exists before I create a new record. I've tried several methods to test if the value in the "end_time" field is Null, but none seems to work.

    How do you test for a Null time value in a browse?

    The documentation in the "Null Table Field Values" says if you want to test for a NULL time value use:
    "fieldname" = 00/00/0000 12:00:00 00 am
    That is the value that the Object Explorer shows in my application.

    My code:
    parentform:BROWSE2.Fetch_Last()
    if (parentform:Browse2:End_Time.Value = 00/00/0000 12:00:00 00 am)
    ui_msg_box("Info", "Value Found", UI_OK)
    endif

    This results in a "no such field" error.

    To test that End_Time.Value existed, I tried the following:
    parentform:BROWSE2.Fetch_Last()
    if (parentform:Browse2:End_Time.Value = ctodt("08/05/0000 12:00:00 00 am"))
    ui_msg_box("Info", "Value Found", UI_OK)
    else
    ui_msg_box("Info", "Value Not Found", UI_OK)
    endif

    No error from this, and it returned "Value Not Found", which was expected.

    Next test:
    parentform:BROWSE2.Fetch_Last()
    if (parentform:Browse2:End_Time.Value = ctodt("00/00/0000 12:00:00 00 am"))
    ui_msg_box("Info", "Value Found", UI_OK)
    else
    ui_msg_box("Info", "Value Not Found", UI_OK)
    endif

    Resulted in the error "Not a valid time format."

    Since it said the default null time was not a valid time format, I thought I'd test to see if the value in the field was not valid, and go based on that.

    if(IsTime(parentform:Browse2:End_Time.Value))
    ui_msg_box("Info", "Value Found", UI_OK)
    else
    ui_msg_box("Info", "Value Not Found", UI_OK)
    endif

    This resulted in the error "Variable is of different type."

    I've run out of ideas. What simple thing have I missed that will see if the time field is null?

    Tony

  2. #2
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default RE: Testing for Null Time value.

    what do these functions report?

    IsTime()
    IsShortTime()

    ???

    -- tom

  3. #3
    "Certified" Alphaholic Scott Emerick's Avatar
    Real Name
    Scott Emerick
    Join Date
    Dec 2000
    Location
    Central Virginia/ North Carolina Border
    Posts
    2,154

    Default RE: Testing for Null Time value.

    Tony,

    Is Time_end a field tye "Time" in your table?
    Is Time_end the header name in your browse?

    Make sure that the field is in fact a "time" type in your table by checking the table structure.

    Since ctodt("08/05/0000 12:00:00 00 am")) did return the correct responce, its my guess you have time end as a "Character" type.

    If not then check your column header. If it is something different then Time_end or Time end, then you need to reference that. For example, if your browse header says "Ending Time" then your syntax would be:

    parentform:Browse2:Ending_Time.Value

    Hope this helps.

    Scott

  4. #4
    Member
    Real Name
    Tony Saladino
    Join Date
    Aug 2005
    Posts
    77

    Default RE: Testing for Null Time value.

    The ISTIME() function returns .T. if the Time_String is a valid representation of a time value. The parameter should be of type character, so it should not give me the correct result.

    I was grasping at straws when I tried that function, but should have realized it would not work.

    The IsShortTime() function returns .T. if the Time_String is a valid representation of a short time value. This wouldn't work for two reasons. One, I'm dealing with a field that's of type "time", not type "short time". Two, it takes a parameter of type "character".

  5. #5
    Member
    Real Name
    Tony Saladino
    Join Date
    Aug 2005
    Posts
    77

    Default RE: Testing for Null Time value.

    End_Time is of type "time" is is 17 units long. I did check the table structure. Many times. ;)
    End_Time is also the header name in the browse.

    ctodt() returns a time format, not a character format. I tested that again, and it works perfectly if I have a valid time that is not 00/00/00 12:00:00 00 am.

    Not sure of where to go from here.

  6. #6
    VAR
    Real Name
    Martin W. Cole
    Join Date
    Apr 2000
    Location
    Terrell, Texas (near Dallas)
    Posts
    5,951

    Default RE: Testing for Null Time value.

    try this:

    t=table.get("tablename")
    if t.datefield.is_blank()
    'xxx
    end if

  7. #7
    Member
    Real Name
    Tony Saladino
    Join Date
    Aug 2005
    Posts
    77

    Default RE: Testing for Null Time value.

    Martin,

    That worked.

    I was hoping to do this with forms, but tables should work.

    Thanks,
    Tony

  8. #8
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default RE: Testing for Null Time value.

    Ok. I can see that now, too.

    I've just worked up an example that approaches the problem differently.

    1) instead of reading the column value from the browse object, I established a pointer to the table being displayed in the browse object.

    2) I used convert_type() to take the time value and convert it to a character string.

    3) I compare the character string to the null field string value

    It seems to work just fine. Perhaps there's an easier more direct way, but this is what I would up with, where START is a table fieldname, with data type of T.

    If convert_type(tbl.start,"C") = "00/00/00 00:00:00 00 AM" then 'time field is empty

    Hope this helps.

    -- tom

  9. #9
    Member
    Real Name
    Tony Saladino
    Join Date
    Aug 2005
    Posts
    77

    Default RE: Testing for Null Time value.

    Tom,

    That also worked.

    I tried doing the same thing on the column value form browse but it didn't work. I wonder why it's possible to do this two ways using tables, but it doesn't seem to work using the form/browse object.

    Thanks for the help.
    Tony

  10. #10
    VAR
    Real Name
    Martin W. Cole
    Join Date
    Apr 2000
    Location
    Terrell, Texas (near Dallas)
    Posts
    5,951

    Default RE: Testing for Null Time value.

    BROWSE2.Fetch_Last()
    if parentform:tables:tablename.datefield.is_blank()ui_msg_box("Info", "Value Found", UI_OK)
    end if

  11. #11
    Member
    Real Name
    Tony Saladino
    Join Date
    Aug 2005
    Posts
    77

    Default RE: Testing for Null Time value.

    Thanks Martin,

    I'm impressed with the multitude of ways to do this. You and Tom have been very helpful.

    Tony

  12. #12
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default RE: Testing for Null Time value.

    Tony,

    You can use the column value in the browse object, but it seems you must convert its data type to character in order to use it in other expressions:

    In my case START is a field in the table and the column title:

    convert_type(Parentform:browse1:start.value,"C") returns
    "00/00/0000 12:00:00 00 am" if the field is empty.

    -- tom

  13. #13
    Member
    Real Name
    Tony Saladino
    Join Date
    Aug 2005
    Posts
    77

    Default RE: Testing for Null Time value.

    Hi Tom,

    I tried that code and it didn't work. That why I said for some reason it doesn't seem to work in the form browse object.

    This code works:

    tbl as p
    tbl = table.get("work_time")
    tbl.fetch_last()
    if (convert_type(tbl.end_time,"C") = "00/00/0000 12:00:00 00 AM")
    ...
    end if

    This code doesn't work:

    parentform:BROWSE2.Fetch_Last()
    if convert_type( parentform:BROWSE2:End_Time.Value, "C") = "00/00/0000 12:00:00 00 AM"

    It doesn't give an error, it just never recognizes that the value in end_time is actually blank. I'm assuming that case does not matter. (end_time vs End_Time)

    Tony

  14. #14
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default RE: Testing for Null Time value.

    Tony,

    I'm not sure what I'm doing that you are not, but I have no difficulty with the embedded browse object, and disagree with your conclusion that my script won't work.

    Here's an example for you.

    Open the only form you'll find. It has one button. This button examines each of the three rows in the browse object. If the Start field is not null the field value is dumped to the trace window. If the start field is empty a statement to that effect is dumped to the trace window. Push the button. Check the trace window. The script is able to determine if the column value is empty just fine.

    Perhaps you're using a fieldname in your expression, instead of the column name? This often trips me up.

    -- tom

  15. #15
    Member
    Real Name
    Tony Saladino
    Join Date
    Aug 2005
    Posts
    77

    Default RE: Testing for Null Time value.

    Hi Tom,

    What I'm about to say isn't going to make an sense.

    I ran your demo and it worked, as expected. I then compared the code, and noticed I had some space in my code that you didn't have in your code. These spaces should not make any difference. I removed those spaces and retried.

    Original:
    if convert_type( parentform:BROWSE2:End_Time.Value, "C") = "00/00/0000 12:00:00 00 AM"

    Without spaces:
    if convert_type(parentform:BROWSE2:End_Time.Value,"C") = "00/00/0000 12:00:00 00 AM"

    My code now worked like a charm. I then put the spaces back in and it still worked, like it should.

    I'm sorry to put you through all this. Obviously I was doing something wrong, but I'm not sure what it was. Your code looked like it should have worked, it just wasn't working for me. Didn't mean to say your code didn't work.

    Tony

  16. #16
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default RE: Testing for Null Time value.

    Tony, no problem. No offense taken, I make mistakes in code all the time. Someone looking over my shoulder would think I write code the way I snow ski. I go faster and faster until I crash or fall down! Glad you got it going, and that the little example I cobbled together helped a bit. Good luck with the rest of your project. -- tom

  17. #17
    Member
    Real Name
    Bob Alston
    Join Date
    Feb 2012
    Location
    Dallas
    Posts
    180

    Default Re: Testing for Null Time value.

    I am trying to set a conditional style to make empty datetime fields (T) have a red background. I tried this in the conditional style

    convert_type(DOB,"C") = "00/00/00 00:00:00 00 AM"

    However it doesn't work. No red
    in face if I change the = to <> I don't get red either.

    Something simple I hope??

    bob
    Bob Alston
    -------------------------------------------------------------------
    http://webpages.charter.net/bobalston/bob1.htm
    Alpha five v11 Tips

  18. #18
    Member
    Real Name
    Bob Alston
    Join Date
    Feb 2012
    Location
    Dallas
    Posts
    180

    Default Re: Testing for Null Time value.

    My workaround was to add a new calculated field in the query based on if the target field isnull. then I base the conditional formatting on the logical field I created.

    I subsequently found an alternative approach, that I have not yet tested.
    http://msgboard.alphasoftware.com/al...ide-expression

    bob
    Bob Alston
    -------------------------------------------------------------------
    http://webpages.charter.net/bobalston/bob1.htm
    Alpha five v11 Tips

Similar Threads

  1. null
    By urbanski in forum Alpha Five Version 6
    Replies: 3
    Last Post: 09-11-2005, 09:23 AM
  2. Testing for Null in a Logical Field
    By Rhett Scott in forum Alpha Five Version 4
    Replies: 2
    Last Post: 02-07-2005, 08:12 PM
  3. new patch - null.ddx
    By dik_coleman in forum Alpha Five Version 6
    Replies: 17
    Last Post: 09-29-2004, 07:19 AM
  4. Null value
    By davidv43 in forum Alpha Five Version 6
    Replies: 6
    Last Post: 08-17-2004, 05:11 PM
  5. null.dbf
    By Dennis Mathias in forum Alpha Five Version 5
    Replies: 7
    Last Post: 05-27-2004, 06:14 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •