New call-to-action
Results 1 to 14 of 14

Thread: update Logical Field from Date Field

  1. #1
    Member
    Real Name
    John Burke
    Join Date
    Oct 2003
    Location
    Prince George, BC
    Posts
    230

    Default update Logical Field from Date Field

    Hi all, I found some threads about updating fields with info from another, but not what I am trying to do. I have a table, imported from Access. I actually ziped the table in a previous thread, msg id 79745, a couple of days ago. In this table are two fields, date_close (d) and inactive (l). I have 2300+ records. Would like to run an update to mark the inactive field to .t., which by the way is a checkmark box, based on the file being closed. any suggestions, tks jb

  2. #2
    Member
    Real Name
    Steve Andrews
    Join Date
    Jan 2004
    Location
    Mahwah, NJ
    Posts
    644

    Default RE: update Logical Field from Date Field

    ...based on the file being closed.

    If you want the inactive field set a certain way when the close date is within a certain date range, check out the Between() function. It will accept date arguments and it returns a logical value. I assume you know the basics of how to set up an update operation?

    Steve

  3. #3
    Member
    Real Name
    John Burke
    Join Date
    Oct 2003
    Location
    Prince George, BC
    Posts
    230

    Default RE: update Logical Field from Date Field

    Hi, thanks for the reply, here is what I tried, placing a tmp btn on form. The form is based on set with the topparent being wclients. I get an argument is incorrect daya type, which is true as one is date and other is logical, I'm just grasping at straws here to get something working. I read a lot of threads about between(), tried them interactivly, no results. I'll go to learningalpha and see if there is something there. tk jb

    tbl = table.open("wclients")
    query.filter = "fileopened = '01/01/1996'"
    query.filter = "file_close = '12/31/2004'"
    query.order = ""
    query.options = ""
    indx = tbl.query_create() (Bombs Here)
    update.fields = 1
    update.field= "inactive"
    update.expr = ".t."

  4. #4
    Member
    Real Name
    Romy Huang
    Join Date
    Dec 2004
    Posts
    303

    Default RE: update Logical Field from Date Field

    you can use update operation.

    put condition:
    fileopened"={01/01/1996} and file_close"={12/31/2004}

    put field:
    inactive

    put expression:
    .T.

  5. #5
    Member
    Real Name
    Romy Huang
    Join Date
    Dec 2004
    Posts
    303

    Default RE: update Logical Field from Date Field

    put condition:
    fileopened={01/01/1996} .and. file_close={12/31/2004}

  6. #6
    Member
    Real Name
    Steve Andrews
    Join Date
    Jan 2004
    Location
    Mahwah, NJ
    Posts
    644

    Default RE: update Logical Field from Date Field

    John,

    If fileopened and file_Close are date type fields, that explains your problem. You are using them as character fields. Moreover, your second assignment of query.filter merely overwrites the first. They should be combined with an .AND. operator between them.

    So if these are date fields give this a try:

    query.filter = "fileopened={01/01/1996} .AND. file_close={12/31/2004}"

    I'm a little surprised if you want the query based on two date fields having two specific dates rather than, say, having one date field being between two specific dates. Let us know the specifics of what you want.

    Steve

  7. #7
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: update Logical Field from Date Field

    For later usage if you want to know why your attempt didn't work...

    A second assignment to a variable is not additive, it replaces the value. So

    query.filter = "fileopened = '01/01/1996'" - first assignment
    ? query.filter
    = "fileopened = '01/01/1996'"

    query.filter = "file_close = '12/31/2004'" - second assignment
    ? query.filter
    = "file_close = '12/31/2004'"

    To combine filter criteria use

    query.filter = "fileopened = '01/01/1996'"+".and."+"file_close = '12/31/2004'"
    ? query.filter
    = "fileopened = '01/01/1996'.and.file_close = '12/31/2004'"


    But since I believe you mentioned that the fields were dates, you probably wanted to use

    query.filter = "fileopened = {01/01/1996}"
    ? query.filter
    = "fileopened = {01/01/1996}"

    query.filter = "file_close = {12/31/2004}"
    ? query.filter
    = "file_close = {12/31/2004}"

    Combined as
    query.filter = "fileopened = {01/01/1996}"+".and."+"file_close = {12/31/2004}"
    ? query.filter
    = "fileopened = {01/01/1996}.and.file_close = {12/31/2004}"


    The rest of your script wouldn't have worked either but you can now see a way to construct proper filter expressions.

    I think I remember a good article on www.learnalpha.com but your can also good information in the help files.

  8. #8
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default RE: update Logical Field from Date Field

    Didn't mean to repeat your excellent advice, just took me longer than you to compose a response.

  9. #9
    Member
    Real Name
    John Burke
    Join Date
    Oct 2003
    Location
    Prince George, BC
    Posts
    230

    Default RE: update Logical Field from Date Field

    Thanks guys, sorry did have the .and. between dates, caused a little confusion. The replies did twig one thing, I really didn't need the start date, only needed to change the inactive field to .t. if there was a closed date. managed to do what I needed through an operation.. I did look at Dr. Wayne's site and found some good articles on between dates. thank again to all. jb

  10. #10
    Member
    Real Name
    John Burke
    Join Date
    Oct 2003
    Location
    Prince George, BC
    Posts
    230

    Default RE: update Logical Field from Date Field

    Hi again, the update worked fine in the table, however, when I look at my form, the inactive field is blank. The field on the form is a checkbox, if I had left the field as t or f would it have made a difference, if so, then how can I get my checkbox to show an X. thk again jb

  11. #11
    Member
    Real Name
    Romy Huang
    Join Date
    Dec 2004
    Posts
    303

    Default RE: update Logical Field from Date Field

    in form design model, check the property of inactive, if it is check-box type it will work. see attachment

  12. #12
    Member
    Real Name
    John Burke
    Join Date
    Oct 2003
    Location
    Prince George, BC
    Posts
    230

    Default RE: update Logical Field from Date Field

    Hi, that's my problem, I Have the inactive field on the from, set to check box, when the update ran it didn't check the boxes accordingly. I ran the update on the table and the results are correct, .t. if there was a end date and .f. if there wasn't. The form just shows a blank box.?? jb

  13. #13
    Member
    Real Name
    Romy Huang
    Join Date
    Dec 2004
    Posts
    303

    Default RE: update Logical Field from Date Field

    if it is .f., it will blank box, if it is .t., it will be checked. Did you check the property of inactive field which is check-box type?

  14. #14
    Member
    Real Name
    John Burke
    Join Date
    Oct 2003
    Location
    Prince George, BC
    Posts
    230

    Default RE: update Logical Field from Date Field

    Hi Romy, not my finest hour, the form is based on a set, another tbl in the set also has an inactive field. Guess which one it was set to, duh.. Everying as it should be, thanks for help. jb

Similar Threads

  1. Logical Field
    By Phil Rolf in forum Web Application Server v6
    Replies: 1
    Last Post: 08-30-2005, 04:25 PM
  2. Update date field from 3 fields
    By George Corder in forum Alpha Five Version 4
    Replies: 2
    Last Post: 12-14-2002, 07:00 PM
  3. Logical Field
    By Bradley Chesnut in forum Alpha Five Version 5
    Replies: 6
    Last Post: 08-31-2002, 03:01 PM
  4. Update date field
    By Michael Humby in forum Alpha Five Version 4
    Replies: 4
    Last Post: 10-16-2001, 08:13 AM
  5. How can Update a field in only current rec w date
    By Glen Beer in forum Alpha Five Version 4
    Replies: 2
    Last Post: 02-08-2001, 06:04 PM

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
  •