Results 1 to 7 of 7
  1. #1
    Join Date
    May 2014
    Posts
    4

    Unanswered: Seperate records by docdate

    Hello all, new to crystal and sql.
    Hoping someone has a quick and easy answer to my question. Using crystal 14.0.2 and connecting to MS dynamics db (sql 2008).
    Working on a report that lists all invoices monthly by customer. The following code is what i have come up with.

    numbervar d := datepart("d", CurrentDate);
    numbervar m := datepart("m", CurrentDate);
    numbervar y := datepart("yyyy", CurrentDate);

    if {SOP30200.DOCDATE} in dateserial(y, m-12, 1) to dateserial(y, m-11, 1 - 1)
    then {SOP30200.DOCAMNT}
    else 0

    The results are not what is expected. (compared to running a straight sql script for the same info).
    Is there something i could be doing better?
    Need this ASAP!!
    Thanks in advance,

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What is your If statement trying to achieve, in words?

    It looks like you are trying see if the DOCDATE is between 12 months ago (1st of the month) and 1 months ago (1st of the month).

    I'm not 100% familiar with Crystal Syntax but "in" seems wrong.

    Why not try:
    Code:
    If {SOP30200.DOCDATE} >= dateserial(y, m-12, 1) And {SOP30200.DOCDATE} < dateserial(y, m-11, 1 - 1) Then
    ...
    George
    Home | Blog

  3. #3
    Join Date
    May 2014
    Posts
    4

    Thanks for the reply

    Unfortunately, that didn't change the output. You are correct in that im looking for a range of documents within the stated time frame.
    the code looks for in case any documents that are between 11 and 12 months ago.
    Thanks again.
    Gary

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just tested the following formula and get what I understand to be the correct result
    Code:
    numbervar d := datepart("d", CurrentDate);
    numbervar m := datepart("m", CurrentDate);
    numbervar y := datepart("yyyy", CurrentDate);
    
    If {@DOCDATE} >= DateSerial(y, m-12, 1) And {@DOCDATE} < DateSerial(y, m-11, 1) Then
        "YUUUUP!"
    Else
        "Nope."
    Running this today gives:

    DateSerial(y, m-12, 1) = 2013-05-01
    DateSerial(y, m-11, 1) = 2013-06-01
    George
    Home | Blog

  5. #5
    Join Date
    May 2014
    Posts
    4

    Document Dates

    that is correct. If you add the -1 into the date field you will just pull for that month. Exactly what im looking for.....

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sorted ?
    George
    Home | Blog

  7. #7
    Join Date
    May 2014
    Posts
    4

    sorted

    sorted by customer

Tags for this Thread

Posting Permissions

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