Skip to main content
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Report Writer VBA
Finance | Project Operations, Human Resources, ...
Answered

Report Writer VBA

(0) ShareShare
ReportReport
Posted on by Sunil Chhabra 1,958

I have a VBA created for a report that extracts info from a SQL and table and works fine. I wanted to test it to bring in another field and I cannot understand why the VBA is failing

The message translates into that the column HOURS_I is invalid. But the column exists. If I run the query in Studio Management I correctly get a single value.

Not sure why the error.  Another puzzling thing is it works with the original SQL Field called compbal.  I just replace the query/table and want to insert the value to HRAttendVacUsed calculated field. There is no issue with calculated field - not sure why it is erroring out on HOURS_I column.

pastedimage1618283423634v1.png

Thanks

Categories:
Microsoft Dynamics GP
  • Suggested answer
    David Musgrave MVP GPUG All Star Legend Moderator Profile Picture
    David Musgrave MVP ... 14,053 Most Valuable Professional on at
    RE: Report Writer VBA

    You can use the same connection to run multiple queries.

    Or you can return multiple columns in the one query.

    The reason you are failing with the code is that there is no HOURS_I column in your query.  It would be just an untitled column.

    You need to use sum(HOURS_I) as HOURS_I to give the aggregated sum column a name.

    This article might help

    https://winthropdc.wordpress.com/2008/10/29/using-ado-with-vba-with-report-writer/

    Also if you want to move away from VBA which is now end of life, you can use GP Power Tools instead:

    https://winthropdc.wordpress.com/2019/08/12/gppt-replacing-vba-with-gp-power-tools-developer-tools/

    Regards

    David

  • Sunil Chhabra Profile Picture
    Sunil Chhabra 1,958 on at
    RE: Report Writer VBA

    Thanks Sandip - How would I add 1 more field.

    I have 2 calculated fields. The first one is populated using the above vba script. But how do I populate a second one. Do I need to create another Subroutine?

  • Verified answer
    sandipdjadhav Profile Picture
    sandipdjadhav 18,304 on at
    RE: Report Writer VBA

    Hello,

    May be you need to put "Select sum(HOURS_I) as HOURS_I"  , "As HOURS_I" required?

    Thanks much

    Sandip

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Ramesh Kumar – Community Spotlight

We are honored to recognize Ramesh Kumar as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Abhilash Warrier Profile Picture

Abhilash Warrier 565

#2
Martin Dráb Profile Picture

Martin Dráb 536 Most Valuable Professional

#3
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 402 Super User 2025 Season 1

Last month Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans