Alpha Video Training
Results 1 to 6 of 6

Thread: Query Problem

  1. #1
    Member
    Real Name
    Vernon R Jorgensen
    Join Date
    Mar 2015
    Posts
    6

    Default Query Problem

    I'm trying to run a query from date 11/15/2016 to date 01/17/2017 t. when I run it I get the 11/15/2016 date and the 01/01/2017-01/18/2016. Why are the other2016 dates missing?

  2. #2
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,607

    Default Re: Query Problem

    I'm guessing that you have a character field holding the date rather than a date type field.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

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

    Default Re: Query Problem

    I agree with Al but I don't think you're telling the whole story. With character data type representations there are no records if you query from date 11/15/2016 to date 01/17/2017. Character values are evaluated left to right, character by character and 01/17/2017 is lower than 11/15/2016.
    There can be only one.

  4. #4
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,660

    Default Re: Query Problem

    If this is for a Report filter, you can define arguments to prompt the user for the date range. In the Alpha Sports Invoice report I defined 2 date type arguments :vStart and :vEnd then used this expression for the report filter:

    between(Date,:vStart,:vEnd)

    RepDateArgs_20170119.jpg
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  5. #5
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,660

    Default Re: Query Problem

    Bump - I was surprised to find this post of mine, because I don't use arguments in reports. But since reading up on them, I think I would like to. I want to convert a report I have that is currently using ASKD vars to query for the date range. And I want to make the prompt for the date range optional depending on where the report is called from. Arguments seem to be the perfect answer for this, but I am having trouble understanding how to pass values to the arguments defined in the report filter in my script that will call the report.

    The Wiki page is Arguments in Layout Filters almost at the bottom.

    If I use the arguments vStart and vEnd like in the filter I defined above, then have the user create a filter expression using the genie to get the dates in the current month; Ex: month(mydatefield) = month(date()) - how would I get the first and last dates from that query range to pass to my arguments?

    Edit: Since I was using the ASKD vars in multiple calcs in the report - how do I switch those to use the arguments instead? There is no F10 option that appears in the calc expression editor like there is in the filter expression editor once the args are defined. Which may be the reason I abandoned the use of this feature in the first place...
    Last edited by MoGrace; 05-28-2018 at 05:58 PM.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  6. #6
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,660

    Default Re: Query Problem

    I think I have my query problem resolved here
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

Similar Threads

  1. Query Problem
    By Ckirkpatrick in forum Alpha Five Version 11 - Desktop Applications
    Replies: 3
    Last Post: 05-23-2013, 01:11 PM
  2. Query gen problem
    By peterg000 in forum Alpha Five Version 6
    Replies: 1
    Last Post: 08-29-2005, 02:58 AM
  3. Query Problem
    By Kevin McDuff in forum Alpha Five Version 5
    Replies: 3
    Last Post: 09-16-2003, 08:06 AM
  4. Query Problem
    By John Keogh in forum Alpha Five Version 5
    Replies: 18
    Last Post: 05-06-2003, 06:55 AM
  5. Query problem?
    By Jim in forum Alpha Five Version 4
    Replies: 2
    Last Post: 01-04-2002, 06:26 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
  •