Thread: Need Macro to pull data from AR System - Remedy

Hi All,

I am looking for a Macro that pull the data from Remedy and it should promt for start date & end date, I am not sure what are all the details required for this. Please let me know if I miss any of the required parameter.

I have tried pulling the data using MS Query and was able to pull the data. In the table instead of having a standard date I have changed it to [Enter Start Date] as a parameter query and it is working in Query however I am getting an error "Driver] Parameter missing" in excel and my query is lost.

Driver used for this is "DSN=AR System ODBC Data Source;ARServer=remedy03;UID=userid;ARAuthentication=;SERVER=NotTheServer"

The code mentioned below works if the date is constant as mentioned below. I have attached the sample result sheet.

SELECT "ATS:AcxiomTechnicalServices"."Ticket Number", "ATS:AcxiomTechnicalServices"."Arrival Time", "ATS:AcxiomTechnicalServices"."Assigned Time", "ATS:AcxiomTechnicalServices"."Closed Time", "ATS:AcxiomTechnicalServices"."Pending Minutes", "ATS:AcxiomTechnicalServices"."Pending Minutes Business", "ATS:AcxiomTechnicalServices"."Assigned To Group", "ATS:AcxiomTechnicalServices"."Closed By Group"
FROM "ATS:AcxiomTechnicalServices" "ATS:AcxiomTechnicalServices"
WHERE ("ATS:AcxiomTechnicalServices"."Assigned Time">={ts '2011-09-01 00:00:00'} And "ATS:AcxiomTechnicalServices"."Assigned Time"<={ts '2011-09-16 00:00:00'}) AND ("ATS:AcxiomTechnicalServices"."Assigned To Group"='cec_windows')

Thanks in advance for all your help!!

Regadrs,
Humac

Bump no response

Hi,

Instead of specific date & group I have mentioned it as [Enter Start Date], [Enter End Date] & [Enter Group]. I am getting the result if I use the same in MS Query but not in exel. Hope I am using the correct syntax in SQL.

SELECT "ATS:AcxiomTechnicalServices"."Ticket Number", "ATS:AcxiomTechnicalServices"."Arrival Time", "ATS:AcxiomTechnicalServices"."Assigned Time", "ATS:AcxiomTechnicalServices"."Closed Time", "ATS:AcxiomTechnicalServices"."Pending Minutes", "ATS:AcxiomTechnicalServices"."Pending Minutes Business", "ATS:AcxiomTechnicalServices"."Assigned To Group", "ATS:AcxiomTechnicalServices"."Closed By Group"
FROM "ATS:AcxiomTechnicalServices" "ATS:AcxiomTechnicalServices"
WHERE ("ATS:AcxiomTechnicalServices"."Assigned Time">=[Enter Start Date]And "ATS:AcxiomTechnicalServices"."Assigned Time"<=[Enter End Date] AND ("ATS:AcxiomTechnicalServices"."Assigned To Group"=[Enter the Group])

Thanks in advance for all your help!!

Regards,
Humac

Thanks Dave!!

Have you verified that your parameters are pulling in correctly?
Yes, it is pulling correctly in MS QUERY & Excel if I give specific date & Group and its pulling data in MS Query even with [Enter Date] but when I try to refresh the same in excel its throwing an error message.

Don't you need to surround your dates with #?
Yes, I do surrond # for dates in MS Query and it is converting to {ts 'YYYY-MM-DD HH:MM:SS'} when I view code in SQL.

Why are your table and field names in double quotes?
I hope it is the syntax SQL use. I didn't use double quotes in MS Query.

Regards,
Humac

Hello Moderators,

As suggested by Dave, please move this thread from the Access portion of this forum to an Excel section.

Thanks in advance for your help!!

Regards,
Humac

Bump no response - I am bumping it as this thread is moved from other section.

Sorry for the inconvinience.