Search results by user ID 4190

Re: Parameters in Excel VBA

CDS_Josh — Wed, 06 Mar 2013 22:03:37 GMT

Thanks Dave. That did the trick and I did have to play around with the date formatting.

Much Appreciated!!!

Re: Parameters in Excel VBA

CDS_Josh — Tue, 05 Mar 2013 18:34:34 GMT

Thanks David. I understand that I can "Hard-Code" a date, but what i am trying to do is set up a variable that will be define in a Cell in excel (Which was part of the declaration section in my code above). That way, if the users changes the date in that field, the query will automatically filter with the new STARTDATE.

Thanks in advance

-Josh

Parameters in Excel VBA

CDS_Josh — Mon, 04 Mar 2013 16:32:18 GMT

I am using your "GetInvoices" API example to create an excel report that will populate data. The customer has requested that I create a date parameter for this information, but I have never been able to do this in the past.

Here is my code where the issue is coming up:

Dim STARTDATE As Date

S TARTDATE = Worksheets("RUN").Range("B8").Value

sSelectWhere = "ActivityType like 'OB%' and NOT(AnalyzorName is Null) and NOT(AnalyzorName = '') and (DueDateStart >= STARTDATE OR ActualCompletedDate >= STARTDATE)"

When I try to execute this report, I get an error: Error from the MethodAPI: Failure. The following fields do not exist in the Table Activity: 'STARTDATE', 'STARTDATE'.

I am hoping this is only a syntax issue on my part, because this would be extremely helpful to know.

Thanks in Advance!!

Re: Simple Formula Not Working

CDS_Josh — Fri, 29 Jun 2012 22:10:32 GMT

I only use the generate report function to test my reports. I find the preview screen to be extremely buggy and will not show any results at times, even though a generate report with a wide open filter range will show hundreds. I really wish Method would spend more time working with this report designer and work out all the kinks. Its a great tool when it works.

I managed to get my desired results through Excel using an API call.

Thanks anyways!!

Re: Simple Formula Not Working

CDS_Josh — Thu, 28 Jun 2012 21:20:01 GMT

Here is an example of what I am talking about (The values with the arrows pointing to them should be a 1 because both criteria are met)

Simple Formula Not Working

CDS_Josh — Thu, 28 Jun 2012 21:14:22 GMT

I am having nightmares with these functions in the Report Designer.

Here is a simple formula that is not working and I cannot figure out why:

Iif(([CallReport.ComplaintCode] = 'CA' and [CallReport.DateReceived] >= AddDays(Today(),-30)), 1, 0)

Can someone help me out with this. I have been designing reports for almost 15 years now and have never seen so many inconsistencies with a report designer. Sometimes it works, and sometimes it doesn't. And I cannot figure out why.

It could be me, but this is a very basic formula I have here!!

-Josh

Re: Date Difference as Integer in Report Designer

CDS_Josh — Thu, 28 Jun 2012 18:11:01 GMT

I had everything working fine and with ZERO changes to the above formulas, now it is no loner working correctly. It just groups everything in "<= 30 Days". I did not change a thing and it just stops working.

Can we open this thread back up for suggestions?

Re: Date Difference as Integer in Report Designer

CDS_Josh — Thu, 28 Jun 2012 17:32:04 GMT

I figured out a difference approach. I created 3 Date Fields using the AddDay function for 30,60,90 days ago and then made my formula based off that.

I really wanted to see the DATEDIFF fuction get incorporated because this is a great tool for aging reports.

Thanks anyways. I think I figure out what I need!!

Here is my code for future reference and for others:

30Days: AddDays(Today(), -30)

60 Days: AddDays(Today(), -60)

90 Days: AddDays(Today(), -90)

AgedGroup: Iif([CallReport.DateRcvd] >= [CallReport.Day30Date], '≤30 Days',Iif([CallReport.DateRcvd] < [CallReport.Day30Date] AND [CallReport.DateRcvd] >= [CallReport.Day60Date],'31-60 Days' ,Iif([CallReport.DateRcvd] < [CallReport.Day60Date] AND [CallReport.DateRcvd] >= [CallReport.Day90Date],'61-90 Days' ,'>90 Days')))

Re: Date Difference as Integer in Report Designer

CDS_Josh — Thu, 28 Jun 2012 16:52:35 GMT

Thanks for the reply, but I have already tried what you suggested and still get a date showing up in excel. I know I can change the format in excel, but I have another calculated field in the report that runs off the "Days Aged" field.

Here are the calculations for my fields:

AgedDays: Now() - [CallReport.DateReceived]

AgedGroup: Iif([AgeDays] <= 30, '≤30 Days', Iif([AgeDays] > 30 And [AgeDays] <= 60, '31-60 Days',Iif([AgeDays] > 60 And [AgeDays] <= 90, '31-60 Days', '>90 Days')))

I have the AgedDays set to integer

and here are my results.

This record should has fallen into the >90 Days group, since it is 107 Days ago (if you convert 4/15/1900 to number in excel). Am I missing something?

Date Difference as Integer in Report Designer

CDS_Josh — Wed, 27 Jun 2012 19:38:38 GMT

I am having a heck of a time getting a Date Difference field to show as an Integer in report designer. I also want to use this calculated field within another field to show Aging (30, 60, 90, >90).

Is this possible? I can get it to show as a date, but cannot convert to an integer or number

Thanks in Advance!!

-Josh