Crystal Reports Month Value

0
August 17, 2011

I been asking a lot of questions today, thanks to all who replied.

I'm coming across a problem trying to convert a number value to a date. The problem is when the month value is a two digit value. I'm using formula below:

stringvar MyDate:= ToText({StoreCheckIn.CHECK

INDATE},0,

"");

cdate(2000 + val(left(MyDate,1)),val(mi

d(MyDate,2

,2)),val(r

ight(MyDat

e,2)))

Crystal gives me an error stating that the month value must be between 1 and 12 for val(mid(MyDate,2,2)).

I check the field values, I think the problem has to be because some months start with zero, (ie 06).

Any help or suggestions would be great.  

Comments See all(0)

Add comment
0
August 17, 2011

if not (isnull({qryStoreCheckIn.C

HECKINDATE

})  or {qryStoreCheckIn.CHECKINDA

TE}="0") then

there are ( ) missing - add as shown

0
August 17, 2011

Can you post some sample values of your CheckinDate field (with and without the 2 digit month)

0
August 17, 2011

It's all one field, here are some examples

70608

70501

0

61231

I also have this statement in the beginning of the formual.

IF ISNULL({qryStoreCheckIn.CH

ECKINDATE}

) THEN

  0

ELSE

0
August 17, 2011

I think the issue is with the zero value.

Code around it like this:

if not isnull({qryStoreCheckIn.CH

ECKINDATE}

)  or {qryStoreCheckIn.CHECKINDA

TE}="0"

the(stringvar MyDate:= ToText({StoreCheckIn.CHECK

INDATE},0,

"");

         cdate(2000 + val(left(MyDate,1)),val(mi

d(MyDate,2

,2)),val(r

ight(MyDat

e,2))))

0
August 17, 2011

Had a typo:

if not isnull({qryStoreCheckIn.CH

ECKINDATE}

)  or {qryStoreCheckIn.CHECKINDA

TE}="0"

then (stringvar MyDate:= ToText({StoreCheckIn.CHECK

INDATE},0,

"");

         cdate(2000 + val(left(MyDate,1)),val(mi

d(MyDate,2

,2)),val(r

ight(MyDat

e,2))))

0
August 17, 2011

For this line, I received error msg of A month number must be between 1 and 12

cdate(2000 + val(left(MyDate,1)),val(mi

d(MyDate,2

,2)),val(r

ight(MyDat

e,2)))

0
August 17, 2011

Yup, I guess I didn't get rid of the other typo :

{qryStoreCheckIn.CHECKINDA

TE}="0" should be {qryStoreCheckIn.CHECKINDA

TE}<>"0"

if not isnull({qryStoreCheckIn.CH

ECKINDATE}

)  or {qryStoreCheckIn.CHECKINDA

TE}<>"0"

then (stringvar MyDate:= ToText({StoreCheckIn.CHECK

INDATE},0,

"");

         cdate(2000 + val(left(MyDate,1)),val(mi

d(MyDate,2

,2)),val(r

ight(MyDat

e,2))))

or change the first line to what Pete posted

0
August 17, 2011

Had to split points. To both, Thank you !!

Related Questions

Crystal Reports XI While Printing Records Null values not working

I inherited a report used to summarize monthly expenses. It works great except where While Printing Records is used to print an account summary. If an account has no activity that month instead of pri Read More

Views

16

Votes

0

Answers

9

August 17, 2011

Shared numbervar in Crystal Reports - linking between groups

Hi There, In my main report, I have two groups. The first group is an ID number, the second is a group by month. Within group 2, I have a subtotal of another field. Essentially, for each ID number I c Read More

Views

12

Votes

0

Answers

3

August 17, 2011

Convert Date (String) to true Date in Crystal 11

Hi guys, can someone help me please. I would like to show the &#39;Month&#39; value in an extra data field in Crystal. My date field is presented like this: 15/02/2011 &#160;and appears to be a string Read More

Views

22

Votes

0

Answers

3

August 16, 2011

Crytal Reports XI (Developer edition) Group by Month and not Date

I am using Crystal Reports XI (developer edition). I have created a simple reports that displays :- Date | Document | Number | Value When I use the wizard for the connection using ADO.net XML (reading Read More

Views

7

Votes

0

Answers

4

August 17, 2011

Stored Procedure not working in Crystal

I am trying to create a stored procedure that will be used in a crystal report. When I run the SQL as a select query, it displays the results I am looking for, but when I run it as a procedure and cal Read More

Views

4

Votes

0

Answers

16

August 17, 2011

Closing the Database connection in ASP when using Crystal Report Objects.

I have generated crystal reports, then written the ASP code to export this report in PDF format. I have multiple pages of this nature for separate reports. The problem I encounter is after I run these Read More

Views

47

Votes

0

Answers

11

August 16, 2011

How would I build a formula in Crystal for the following?

I want a rolling 24 months backwards from currentdate If the transaction is occuring within the current year/month then print the value (that would be the most current column) Then in the previous col Read More

Views

7

Votes

0

Answers

12

August 16, 2011

Suppress 0 value in graph

My question seems simple but you will see that it&#39;s more complicated than it appears : I have 3 databases where I get information for sale values for a specific region. I want to do a graph with a Read More

Views

22

Votes

0

Answers

21

August 16, 2011

Crystal Report 11 -- Formula help -- concat

I want to concatenate month and year and convert that to a number. For example, DatePart (&quot;m&quot;, {Command.PDATE}) which gives just the months DatePart (&quot;yyyy&quot;, {Command.PAYMENTDATE}) Read More

Views

17

Votes

0

Answers

8

August 16, 2011

Crystal reports format number

I have the following problem when the value is returned to the screen the number show like 2,009 how do I rid of the comma? Is there a format function? Dim MonthInt as number Dim MonthString as String Read More

Views

9

Votes

0

Answers

8

August 16, 2011

How do I count the  number of a value inside a groupname?

Hi everybody, I have this report (see attached report) and I have made &#160;groups per month. Now I want to count the numbers of &quot;OK&quot; in each month (January, February, March etc. etc.) I ha Read More

Views

11

Votes

0

Answers

4

August 15, 2011

Want to Chart 12 Mth Moving Average

Hi, Very embarrassed asking this question but having brain fade. This is so silly.... Have 3 fields, customer date value Grouped by Customer (ascending) &#160; &#160; &#160; &#160; &#160; &#160; &#160 Read More

Views

13

Votes

0

Answers

13

August 15, 2011

How to display "0.00" when field is null

I have a Crystal report in my organization that I&#39;m trying to modify but I&#39;m not getting it right. &#160;This report gives us a &quot;YTD Budget&quot; total. &#160;For some accounts that this Read More

Views

19

Votes

0

Answers

23

August 16, 2011

comments in Crystal Reports

Hi there, When I am trying to create new formulas for the new fiscal year. However when I copy the formula from previous year it takes the value as if it was a comment. For example This is the formula Read More

Views

8

Votes

0

Answers

4

August 16, 2011

Running total of a group field that's evaluated at print time

I have a report that is based on a hierarchy, like this: Group 1: Campus Group 2: Building on campus Group 3: Room in building Group 4: Location in room (e.g. DESK) Group 5: Site in location (e.g. DRA Read More

Views

8

Votes

0

Answers

3

August 16, 2011

Is it possible to get a Crystal Report formula to return one of two values?

I mostly work in VBA but now am in the world of Crystal. &#160;In the reading up I have done, I believe that only one value can be returned from a crystal &quot;procedure&quot;. &#160;But is it possib Read More

Views

6

Votes

0

Answers

3

August 17, 2011

Parameter Fields - Selecting Months/Years between range

I&#39;m using Crystal Reports 9. &#160;I want to create a select condition in the Select Expert to only return records within a given date range. &#160;Unfortunately, my date and months are in differe Read More

Views

9

Votes

0

Answers

3

August 16, 2011

Looking for data values without a where clause

I have a report which is grouped by Project, Account Type (Revenue, Expense, Non-Accountting), and Account Item (Rev. Labor, Rev Non labor..) I need a calculation which will allow me to find a specifi Read More

Views

6

Votes

0

Answers

5

August 16, 2011

Blank Date Parameters

I have created a report that lists cash deposits by date range. &#160;I currently have a default report scheduled on Crystal Server to run with hard-coded dates for the current fiscal year. &#160;What Read More

Views

13

Votes

0

Answers

13

August 15, 2011

This is the very first question

Hello guys! This is the first post in this site Read More

Views

2k

Votes

4

Answers

2

January 08, 2016