Use Alias field name in vb6 Data report

Question

0.00/5 (No votes)

See more:

Dear friends
I have a project and i need to put three fields on my data report namley
DEPARTMENT, SEX, TOTALPATIENTS

Here DEPARTMENT and SEX are fields in my data base table and TOTALPATIENTS is the calculated field (Alias) created with the following query

SQL

SELECT DEPARTMENT, SEX, COUNT(*) AS TotalPatients
FROM PATIEnts
GROUP BY DEPARTMENT, SEX
ORDER BY DEPARTMENT;

But i have a problem now.
When i put DEPARTMENT , SEX and TOTALPATIENTS as rptlabels and rtptextboxes on my VB6 data report it shows data field TOTALPATIENTS not found.
How to solve that.
Please help

Sarfaraz

Posted 20-Dec-12 2:17am

sarfarazbhat

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DaveP62 · Accepted Answer · 2012-12-20T09:58:00

One possible solution is to create a view in the database using the select statement you are showing above. Then change the select statement in your application to select from the view. But you will not need the COUNT(*) in your application because it will be in the view. So all your application will see is the field names without an alias.

Another possible solution is to re-write the select in your application like this

SQL

SELECT DEPARTMENT, SEX, TOTALPATIENTS
FROM (SELECT DEPARTMENT, SEX, COUNT(*) AS TotalPatients
      FROM PATIEnts
      GROUP BY DEPARTMENT, SEX)
ORDER BY DEPARTMENT;

Another possible solution is that the VB6 data report is case sensitive. So you would either need to change the alias to "AS TOTALPATIENTS" or change the rptlabel to "TotalPatients".