Query Runs, but causes Report to not load

EFox

Registered User.
Local time
Today, 12:02
Joined
Jun 12, 2014
Messages
23
Hello,

I am new to Access and was given a 4 year old application for my internship. I have a Access 2013 front end that connects to a SQL 2008 database.

I have created two queries and then joined them into a single query (using Access design view). The query runs and returns the data I need in a spreadsheet, but when I go to open the report I get the error message:

"This expression is typed incorrectly, or it is too complex to be evaluated."

Here is my query:

SELECT
DepositMonthlyQuery.mydate,
DepositMonthlyQuery.DepositDate,
DepositMonthlyQuery.RentSum,
DepositMonthlyQuery.UtilSum,
DepositMonthlyQuery.LDSum,
DepositMonthlyQuery.IntSum,
DepositMonthlyQuery.SecuritySum,
DepositMonthlyQuery.OtherSum,
DepositMonthlyQuery.TotalSum,
TransactionQueryForDeposits.mydate,
TransactionQueryForDeposits.TransactionDate,
TransactionQueryForDeposits.SumOfRentTransaction,
TransactionQueryForDeposits.SumOfUtilityTransaction,
TransactionQueryForDeposits.SumOfLongDistanceTransaction,
TransactionQueryForDeposits.SumOfInternetTransaction,
TransactionQueryForDeposits.SumOfDepositTransaction,
TransactionQueryForDeposits.SumOfOtherTransaction
FROM
DepositMonthlyQuery INNER JOIN TransactionQueryForDeposits
ON DepositMonthlyQuery.mydate = TransactionQueryForDeposits.mydate
GROUP BY
DepositMonthlyQuery.mydate,
DepositMonthlyQuery.DepositDate,
DepositMonthlyQuery.RentSum,
DepositMonthlyQuery.UtilSum,
DepositMonthlyQuery.LDSum,
DepositMonthlyQuery.IntSum,
DepositMonthlyQuery.SecuritySum,
DepositMonthlyQuery.OtherSum,
DepositMonthlyQuery.TotalSum,
TransactionQueryForDeposits.mydate,
TransactionQueryForDeposits.TransactionDate,
TransactionQueryForDeposits.SumOfRentTransaction,
TransactionQueryForDeposits.SumOfUtilityTransaction,
TransactionQueryForDeposits.SumOfLongDistanceTransaction,
TransactionQueryForDeposits.SumOfInternetTransaction,
TransactionQueryForDeposits.SumOfDepositTransaction,
TransactionQueryForDeposits.SumOfOtherTransaction;


In the Record Source for the Report if I do not select a source the report will load fine (no data of course), however if I use the previous query I get the error message listed above.

I am new to both Access 2013 and have only taken an intro course to database so any help would be greatly appreciated. If I posted in the wrong section I am sorry, I felt this was more of a query issue then a reporting issue.

Thanks in advance!
Eric
 
fields with spaces in the must be enclosed with square braces [ ]
TransactionQueryForDeposits.[SumOfDepositTransactio n]
 
either that, or you should remove the space because it looks like it shouldn't be there ^_^
 
For some reason that was a typo in the copy/save. I read the email while I was in class and I felt so stupid. I just sat down and double checked and there are no spaces in my query like was pasted above. I have no idea how that happened. If only it was that simple of a resolution hahaha, I appreciate the fast response.

I tried to copy and paste it again, looked fine in the "message" window, but as soon as I do a "preview post" it adds the spaces. Just imagine the query without those strange spaces in the "Transaction" words? hahah I don't know!

Thanks for the previous fast response, and I look forward to more ideas to try.

Thanks,
Eric
 
No sir. This is an application used at my boss's apartment complex, and I have the joy of learning it and implementing new features.
 
Could you explain to me why you are grouping by all of those values without the use of an aggregate function in the selection?
 
Basically what I was trying to initially was have 2 queries running to fill out a report. I realized I couldn't have 2 queries on one source for my report so I started to fiddle around with joining the two queries to see if I could make that work.

The first query gets all transactions(transactions table) that were made on a specific date range and sums up each of the days. I then needed the second query to sum up the deposits(deposits table) made in that date range.

On the form I need to display a row detailing each individual column for transactions, and a row below it to display all the columns for deposits. In the end its being used to easily show that the deposit totals match the transaction totals.

As far as not using aggregation, I am just using the tools that access gives me to create these queries and trying to fill in gaps by looking through the web (the group by was auto selected when I chose the "totals" option in access so I left them). If there is an easier way to do this that I have overlooked, I am open for suggestions. I apologize for my newness to SQL and Access, but I will look at aggregate functions in the mean time.
 
I apologize for bumping up the post, but I am dead in the water on this. I have been trying to see if changing around my two queries before joining them would change anything, but no go. Again sorry to bump, but I am curious if anyone has any ideas/resolution. I don't understand when I "run" the joint query I can get back the datasheet results fine, but it won't allow me to open my report view without getting the error while including the query as its record source.

Thanks in advance.
Eric
 
Like i said, try removing the group bys and only adding the ones you need.
 
My new joint query is as follows (once again ignore the strange spaces in the queries, copy/paste into notepad++ and my message view look normal. I can't change the preview):

SELECT
DepositMonthlyQuery.DepositDate,
DepositMonthlyQuery.RentSum,
DepositMonthlyQuery.UtilSum,
DepositMonthlyQuery.LDSum,
DepositMonthlyQuery.IntSum,
DepositMonthlyQuery.SecuritySum,
DepositMonthlyQuery.OtherSum,
TransactionQueryForDeposits.SumOfRentTransaction,
TransactionQueryForDeposits.SumOfDepositTransaction,
TransactionQueryForDeposits.SumOfUtilityTransaction,
TransactionQueryForDeposits.SumOfLongDistanceTransaction,
TransactionQueryForDeposits.SumOfInternetTransaction,
TransactionQueryForDeposits.SumOfOtherTransaction
FROM
DepositMonthlyQuery INNER JOIN TransactionQueryForDeposits
ON
DepositMonthlyQuery.mydate = TransactionQueryForDeposits.mydate;

Once again the datasheet view pulls up fine with all the daily totals of each individual transaction, but I get the error:

"The expression is typed incorrectly, or it is too complex to be evaluated."

I am going to also post the individual queries that this pulls form in case I have issues there. They each have either 1 or 2 "group bys".

DepositMonthlyQuery:

SELECT
Format([tblDeposits].[DepositDate],"yyyy-mm-dd") AS mydate,
tblDeposits.DepositDate, Sum(tblDeposits.RentDeposit) AS RentSum,
Sum(tblDeposits.UtilitiesDeposit) AS UtilSum,
Sum(tblDeposits.LongDistanceDeposit) AS LDSum,
Sum(tblDeposits.InternetDeposit) AS IntSum,
Sum(tblDeposits.SecurityDeposit) AS SecuritySum,
Sum(tblDeposits.OtherDeposit) AS OtherSum
FROM
tblDeposits
GROUP BY
tblDeposits.DepositDate
HAVING (((tblDeposits.DepositDate)>=Format([Forms]![frmMonthlyDepositReprot]![StartDate]) And (tblDeposits.DepositDate)<=Format([Forms]![frmMonthlyDepositReprot]![EndDate])));

TransactionQueryForDeposits:

SELECT
Format([tblTransactions].[TransactionDate],"yyyy-mm-dd") AS mydate,
tblTransactions.TransactionDate,
tblTransactions.TransactionType,
Sum(tblTransactions.RentTransaction) AS SumOfRentTransaction,
Sum(tblTransactions.DepositTransaction) AS SumOfDepositTransaction,
Sum(tblTransactions.UtilityTransaction) AS SumOfUtilityTransaction,
Sum(tblTransactions.LongDistanceTransaction) AS SumOfLongDistanceTransaction,
Sum(tblTransactions.InternetTransaction) AS SumOfInternetTransaction,
Sum(tblTransactions.OtherTransaction) AS SumOfOtherTransaction
FROM
tblTransactions
GROUP BY
tblTransactions.TransactionDate,
tblTransactions.TransactionType
HAVING (((tblTransactions.TransactionDate)>=Format([Forms]![frmMonthlyDepositReprot]![StartDate]) And (tblTransactions.TransactionDate)<=Format([Forms]![frmMonthlyDepositReprot]![EndDate])) AND ((tblTransactions.TransactionType)='P'));

Sorry if I didn't understand your post about removing the group bys the first time. I thought I had to have them in to group the days, but I was able to do this on the individual queries instead. I really wish now that I would have taken more database courses instead of web design courses hahah.

Thanks for the help,
Eric
 
They both work properly when run independantly. The datasheet shows each of the columns sum'd for all transactions or deposits categories for each day.

My datasheet for the joint query shows these two individual queries combined on a single datasheet like it is supposed to.
 
greate a new query design, select the two of those queries to show
create two fields.
First, choose the query as the table, then choose * as the field.
To the same for the second query as the second field.

Then test it.
 
I assume that for the field it needs to be:

DepositMonthlyQuery.*
and not just the *

I got an error when I chose the query as the table and only input * as the field.

When I run it this way it pulls back each record per date, but it is giving me a copy of them. So for each date I have 2 identical records (sum values for each column) instead of just one per day.
 
I just wanted to thank BlueIshDan for helping. It turns out after we got the query working properly that I needed to just recreate the report. I'm not sure why it fixed my problem, but thanks for the help on my query!
 

Users who are viewing this thread

Back
Top Bottom