If field is blank or if current month is in field show on report else 1

[Eprice]

I am really new to Crystal but do know some VBA from using Access. I am using the field Frequency Notes. If the field is blank I want that record to show or...if the current month (now is April) and the field contains a 4 then to show that record. Data in the field could be: 1,2,4,5,7,8 or 1,5,6 etc. or it could be blank. I want the blanks. I have no clue how to do this but think I would probably use an if statement. Please help.
Thanks
Lisa

 

[MCuthill]

Lisa,

Assuming I understand your post correctly, you want to see those Frequency Notes which are blank (null) OR if Frequency Notes contains the number of the current month?

If so, your record selection would be:

IsNull({Table.Frequency Notes}) OR
InStr({Table.Frequency Notes},ToText(Month(CurrentDate),0,""))>0

Hope This Helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

Try a record selection formula like this:

stringvar array x := "";
stringvar array y := "";
numbervar i;
if isnull({table.frequencynotes}) or
trim({table.frequencynotes}) = "" then
x := "00" else
x := split({table.frequencynotes},",");
for i := 1 to ubound(x) do(
redim preserve y[ubound(x)];
y := totext(val(x),"00")+","
);
(
x[1] = "00" or
totext(month(currentdate),"00") in y
)

This is an awkward solution and will be slow, but should work. The issue here is that the values in your string array can be one or two digits if they refer to months, and to get an accurate search you need to ensure a match based on two digits.

-LB

 

[MCuthill]

Thanks LB.

I had wondered briefly about the 2-digit months...

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Eprice]

lbass
How can your code be done with multiple fields in the formula. I will need to look at the two digit months. What MCuthill wrote works if I use it like this:

{Joblog_.FREQUENCY} = "MONTHLY" and
{Joblog_.DUE DATE} = 1.00 and
IsNull({Joblog_.Frequency Notes}) OR
{Joblog_.FREQUENCY} = "MONTHLY" and
{Joblog_.DUE DATE} = 1.00 and
InStr({Joblog_.Frequency Notes},ToText(Month(CurrentDate),0,""))>0

But... I'm not sure how to use your code in the Select Expert. I am really new with Crystal Reports, Access is my thing.
Thanks
Lisa

 

[lbass]

stringvar array x := "";
stringvar array y := "";
numbervar i;
if isnull({table.frequencynotes}) or
trim({table.frequencynotes}) = "" then
x := "00" else
x := split({table.frequencynotes},",");
for i := 1 to ubound(x) do(
redim preserve y[ubound(x)];
y := totext(val(x),"00")+",");
(
x[1] = "00" or
totext(month(currentdate),"00") in y
) and
{Joblog_.FREQUENCY} = "MONTHLY" and
{Joblog_.DUE DATE} = 1.00

There are two problems with your current selection formula. You MUST set off "or" statements in parens for them to be interpreted correctly, so to correct that, it would look like:

{Joblog_.FREQUENCY} = "MONTHLY" and
{Joblog_.DUE DATE} = 1.00 and
(
IsNull({Joblog_.Frequency Notes}) or
InStr({Joblog_.Frequency Notes},ToText(Month(CurrentDate),0,""))>0
)

But let's say that the Notes field contains 7,8,12. If the current month was January or February, a record would be returned because 1 is in 12, and 2 is in 12.

-LB

 

[Eprice]

I get error "A string is required here" on the line:
trim({Joblog_.Frequency Notes}) = "" then

The field in the spreadsheet for the report is General, I changed to text and that didn't help.
Lisa

 

[fisheromacse]

try:
trim(totext({Joblog_.Frequency Notes}))

 

[Eprice]

I changed the code to this:
trim(totext({Joblog_.Frequency Notes})) = "" then

and got a different error 'A String is Required here' for this other line of code:
x := split({Joblog_.Frequency Notes},",");

I changed the field to general format in Excel but that doesn't seem to help either. Would it be easier to change the data to Jan, Apr, Dec, etc.
Really, thanks for all your help
Lisa

 

[lbass]

You need to check the datatype of {Joblog_.Frequency Notes} in Crystal. Place the field on the report, right click on it->browse field and check the datatype at the top, and then post what you see. Also, can you confirm that if you place the field in the detail section that one row (one instance of the field) might appear exactly like this:

1,3,4,5

-LB

 

[Eprice]

It shows a number format even though I formated the column in Excel as text. I did an update on the data but it doesnt change. What if I do an edit and replace and change all the numbers to the month (Jul, Aug) instead. I would look in the string to find what matches the system date using Instr. How would that be written?
Lisa

 

[lbass]

Please answer my other question. I don't see how a field could contain: 4,5,6,7 and still be interpreted as a number. Does the field really just contain only one number at a time????

-LB

 

[Eprice]

1,2,3,4,5,6,7,8,9,O,N,D

taken from the spreadsheet cell (I did not do this) where O is for Oct, N - November and D - December. I have cleaned it up and put 10,11,12 but still think it should be changed to Aug, Nov, Dec, etc. Yes, it does show as shown above but the field type Number. At a loss and confused. Should I start over?
Lisa

 

[lbass]

I'm sorry, but I don't believe a field returning those results could possibly appear in Crystal as a number datatype--not unless you have already converted it in a formula to replace the alpha characters with numbers and then if only one number exists in the field at a time. And I don't think you have answered whether one instance of the field contains a series of comma-separated values.

Please show what the results look like if you place the field in the detail section and verify the datatype is what is showing in Crystal.

-LB

 

[Eprice]

I am sorry, I have been out of work due to an unexpected family problem but am back now. I changed the field data to text only. How can I use this:
{Joblog_.FREQUENCY} = "MONTHLY" and
{Joblog_.DUE DATE} = 1.00 and
(
IsNull({Joblog_.Frequency Notes}) or
InStr({Joblog_.Frequency Notes},ToText(Month(CurrentDate),0,""))>0
)

if I changed the field to data like this:
Jan, Mar, Apr, May, Nov, Dec,

I need to format the currentmonth to a three digit if possible and not a numeric figure.
It seems a whole lot easier to have text in the fields instead of numbers and text.

 

[lbass]

You still have not answered my repeated question, so here it is stated another way: Does one cell ever contain more than one monthnumber? It appears not, so just change your formula to:

{Joblog_.FREQUENCY} = "MONTHLY" and
{Joblog_.DUE DATE} = 1.00 and
(
IsNull({Joblog_.Frequency Notes}) or
val({Joblog_.Frequency Notes}) = Month(CurrentDate)
)

-LB

 

[Eprice]

As I stated a field could have contained: 1,2,3,4,5,6,7,8,9,O,N,D
But I have since changed it to text data because Crystal WAS showing it as a number, I don't understand how. Since I changed the data to text it could contain:
Jan, Mar, Apr, May, Nov, Dec,
or just
Dec

Wouldn't Month(CurrentDate) show as 5 instead of May? I can do another edit and replace and change everything to the complete month, i was just trying to save room. Would you like me to send the spreadsheet?

 

[Eprice]

The old column I was trying to use was Frequency Notes column that held numbers and text. I am trying to use my new column Frequency Detail now that has all text to make it easier.
Thanks
Lisa

 

[lbass]

Crystal would never show one instance of a field containing:

1,2,3,4,5,6,7,8,9,O,N,D

...as a number datatype. Not possible.

If you have changed the field to contain 3-character month abbreviations, then you could probably just use:

{Joblog_.FREQUENCY} = "MONTHLY" and
{Joblog_.DUE DATE} = 1.00 and
(
IsNull({Joblog_.Frequency Notes}) or
trim({Joblog_.Frequency Notes})= "" or
totext(CurrentDate,"MMM") in {Joblog_.Frequency Notes}
)

-LB

 

[Eprice]

Thank you lbass, that last posting did the trick. I am now getting what I needed for my report. Thanks again. Lisa