This Is A Weird Report Problem!

[gator9]

The code below was working just as it should until I streamlined the database and updated with more stability. All of the fields that are need to run this report is available. The code is the control source for a financial report sent to the clients. Union query is need so that we can get all data from active records and archived records.

My problem now is that the report is showing records from one field in a totally different field, but when I go into the table to check to make sure that the data is in the correct field, it is. Now if you get rid of the union query it shows the data correctly. Any idea?

SELECT DISTINCTROW Projects.*, Mid([ProjectName],InStr([ProjectName]," ")+1) & ", " & Left([ProjectName],InStr([ProjectName]," ")-1) as LstFrst
FROM Projects WHERE (((Projects.ClientID)=forms!Clients![ClientID])); UNION SELECT DISTINCTROW aProjects.*, Mid([ProjectName],InStr([ProjectName]," ")+1) & ", " & Left([ProjectName],InStr([ProjectName]," ")-1) as LstFrst FROM aProjects WHERE (((aProjects.ClientID)=forms!Clients![ClientID]));

Sincerely,

Charles

http://grahamenterprises.net

 

[PHV]

Is the table structure of Projects strictly the same as aProjects ?
Furthermore I don't think the semicolon ( before UNION is necessary.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gator9]

Yes the table structure is the same on both the projects, and aprojects. I will try removing the semicolon

Sincerely,

Charles

http://grahamenterprises.net

 

[gator9]

Your right the semicolon is not nessary but the problem is still there.

Sincerely,

Charles

http://grahamenterprises.net

 

[Lightning]

Rather than using * to list all the fields in the query, specifically name them to ensure that they are displayed in the same order.

HTH
Lightning

 

[gator9]

Thanks Lighting that is what the problem was, I guess it neeeded to know the exact order. What puzzles me is why it worked before.

Here is the correct code for anyone that needs example:

SELECT DISTINCTROW Projects.ProjectID, DebtorPaid, TotalPaidToClient, TotalPlus, Mid([ProjectName],InStr([ProjectName]," ")+1) & ", " & Left([ProjectName],InStr([ProjectName]," ")-1) as LstFrst
FROM Projects WHERE (((Projects.ClientID)=forms!Clients![ClientID])) UNION SELECT DISTINCTROW aProjects.ProjectID, DebtorPaid, TotalPaidToClient, TotalPlus, Mid([ProjectName],InStr([ProjectName]," ")+1) & ", " & Left([ProjectName],InStr([ProjectName]," ")-1) as LstFrst FROM aProjects WHERE (((aProjects.ClientID)=forms!Clients![ClientID]));

Sincerely,

Charles

http://grahamenterprises.net