Method:API Q&A

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

Method_David — Wed, 06 Mar 2013 15:55:13 GMT

Hi CDS_Josh,

I'm not advising you to hardcode it. The string you are sending has been hardcoded to

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

Since STARTDATE is not a field in your table you get the error

So wherever you are building your sSelectWhere you are not dynamically adding the StartDate, pseudocode below to illiustrate the idea

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

I'm also gonna advise you format the date in YYYY/MMM/DD format....eg. 2013/Mar/06

Dave

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

Re: Parameters in Excel VBA

Method_David — Mon, 04 Mar 2013 22:06:57 GMT

Hi CDS_Josh,

Based on the error message it looks like you are not actually populating the startdate variable in your code.

ie. you are using

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

when you need to replace that value with something like

sSelectWhere = "ActivityType like 'OB%' and NOT(AnalyzorName is Null) and NOT(AnalyzorName = '') and (DueDateStart >= '2013/mar/04' OR ActualCompletedDate >= '2013/mar/04')"

Dave

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!!