Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2008
    Posts
    6

    Unanswered: Paradox query on year

    I would like to query a date field for only the current year. Is it possible to put a function like year() in the query?

  2. #2
    Join Date
    Dec 2007
    Posts
    369
    you can't put functions in queries, but there are always other ways to do the same thing. in this case you can do a query with "..2008" as the parameter in the date field.
    --
    Steven Green - Myrtle Beach, South Carolina USA

    http://www.OasisTradingPost.com

    Oasis Trading Post
    - Collectibles and Memorabilia
    - Vintage Lego Sets and Supplies
    - and Paradox Support, too

  3. #3
    Join Date
    Jul 2008
    Posts
    6
    Tks for answer. IT works if I use ..08. Not sure why it doesn't work with ..2008. I can get same results if I use >= 01/01/08, <= 12/31/08 but I was trying to avoid having the user change the query before running the report. I was hoping for something like the parameter form that MS Access offers or using Year or Year -1, assuming the user would run the final report for a year on the following year.
    Last edited by ParaDi; 07-10-08 at 09:58.

  4. #4
    Join Date
    Dec 2007
    Posts
    369
    ..08 instead of ..2008 is based to your Windows Regional Settings and/or your BDE settings..

    you can pass anything changable like that to a query with a variable, of course.. in many cases, it is recommended that you use a "criteria table" to get around possible issues with local settings, when working with dates..

    you push the full date selection into a table, and it's automatically saved the right way, for that machine.. then you use an example element to link the criteria table to the data table..

    also, with Paradox, you can use functions in scans and some SQL queries, but not QBE queries..
    --
    Steven Green - Myrtle Beach, South Carolina USA

    http://www.OasisTradingPost.com

    Oasis Trading Post
    - Collectibles and Memorabilia
    - Vintage Lego Sets and Supplies
    - and Paradox Support, too

  5. #5
    Join Date
    Jul 2008
    Posts
    6
    For others who are looking for a solution in Paradox like MS Access parameter query/forms, I used the below code in a report which works like a parameter form to prompt the user for input to run a query and then print the report.



    method pushButton(var eventInfo Event)
    ;
    var
    compVar String
    tblName String
    tblVar Table
    rptVar Report
    rptInfo ReportPrintInfo
    yesVar String
    deptVar String
    startVar String
    qryName Query
    endVar

    compVar = "Completed"
    tblName = ":PRIV:ANSWER.DB"
    rptInfo.name = "EMR_TRSC.RSL"

    yesVar = msgQuestion("Special Reports",
    "Do you want to Print Preview the \n" +
    "of Transcripts? The printer \n" +
    "setting under File/Printer Setup... must \n" +
    "be set to Letter/Portrait before printing!")

    switch
    case yesVar = "Yes" :

    deptVar = "Press Shift+Insert, then Enter..."
    deptVar.view("Enter Department Name .. for all depts")
    startVar "enter year like ..08 for year 2008"
    startVar.view("Enter the year")

    qryName = Query

    EMP_NAME.DB | Employee Name | Employee Number |
    | Check _join1 | Check _join2 |

    EMP_NAME.DB | Status | Department Name | Bureau |
    | NOT TM | Check ~deptVar | Check |

    EMR_REGS.DB | Employee Name | Employee Number |
    | _join1 | _join2 |

    EMR_REGS.DB | Type | Number | Start Date |
    | Check | Check | Check startVar |

    EMR_REGS.DB | Class Name | Hours | Class Status |
    | Check | Check | Check ~compVar |

    EndQuery

    if deptVar <> "Press Shift+Insert, then Enter..." then
    if executeQBE(qryName, tblName) then
    if isTable(tblName) then
    tblVar.attach(tblName)
    if not tblVar.isEmpty() then
    rptVar.open(rptInfo)
    else
    msgStop("Special Reports",
    "No classes Completed by " + deptVar)
    endif
    endif
    else
    errorShow()
    endif
    else
    msgStop("Special Reports",
    "No Department Name entered...")
    endif

    case yesVar = "No" :
    endSwitch

    endmethod

  6. #6
    Join Date
    Dec 2007
    Posts
    369
    nice, but one big black hole.. never use the view() function in a real app to get user input.. you have no control over what the user does with it.. view() is just for temp stuff, and stuff that you do on-the-fly..

    and, whenever possible, have the user select from a list.. sure, this context doesn't seem that critical, but it's the concept and the theory.. if you present a list, the user can't make a typo, you restrict the choices to stuff that's valid, you're also presenting a construct when you can control the concept of Escape and/or Cancel, etc..
    --
    Steven Green - Myrtle Beach, South Carolina USA

    http://www.OasisTradingPost.com

    Oasis Trading Post
    - Collectibles and Memorabilia
    - Vintage Lego Sets and Supplies
    - and Paradox Support, too

Posting Permissions

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