Trouble with my end result (1 Viewer)

yvesdouville

Registered User
Joined
May 21, 2014
Messages
23
Hello,


I'm trying to output some sort of a filled form with the monthly sales of a particular client.


You can look at the end result that i provided with the post.


Right now, if there is no data at all during a particular year, i get an error message.


Can anyone help me gigure it out? If i did'nt post in the correct discussion, i'm sorry.


Thank you
 

Attachments

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,845
Hi. It would have been nice if you translated the error message in English for some of us who don't speak your language. Since you posted an image, I couldn't even copy and paste it into Google Translate. Have you tried using the Nz() function to see if you can avoid the error? Just curious...
 

yvesdouville

Registered User
Joined
May 21, 2014
Messages
23
It says that it cannot do the query since there is no data in 2018


Sorry about that.






Hi. It would have been nice if you translated the error message in English for some of us who don't speak your language. Since you posted an image, I couldn't even copy and paste it into Google Translate. Have you tried using the Nz() function to see if you can avoid the error? Just curious...
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,845
It says that it cannot do the query since there is no data in 2018

Sorry about that.
Hi. No worries. Thanks for the clarification. Can you please post the SQL statement of the query that's producing this error?
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,365
I'm confused. The error message refers to 2017.
Your first screenshot shows data for both 2017 & 2018
 

yvesdouville

Registered User
Joined
May 21, 2014
Messages
23
It refer to the slide 'Without 2018'


Maybe i shoud have make a sample database to send you guys. Woud that be easier ?



I'm confused. The error message refers to 2017.
Your first screenshot shows data for both 2017 & 2018
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,845
Here you go!

Client 111 will work individually id you enter '111' in the criteria, but not the other ones..
Hi. Thanks. I added a parameter prompt to your crosstab query's design; unfortunately, it doesn't matter what client number I enter, I don't get any errors. Am I doing it correctly?
 

yvesdouville

Registered User
Joined
May 21, 2014
Messages
23
When you are entering 9136 in the client save it and run the query, is it working?






Hi. Thanks. I added a parameter prompt to your crosstab query's design; unfortunately, it doesn't matter what client number I enter, I don't get any errors. Am I doing it correctly?
 

Attachments

yvesdouville

Registered User
Joined
May 21, 2014
Messages
23
OK, i mis-cominicate,


Just realized.



It works in QUERY but not on the REPORT.
It is the report that provide the error.à








The only thing I could think of is double-check your Sortie table and make sure there are no missing values in either the Annee or Mois column.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,845
WOW You are a magicien.

What did i do wrong?
Hi. Not necessarily that you did something wrong but more like you expected Access to work in a way it can't. If I were you, I would leave the query without a criteria and simply open the report with your criteria using code. For example,
Code:
DoCmd.OpenReport "ReportName", , , "client=9136"
The reason why you were getting the error when you open the report was because crosstab queries can produce columns dynamically; whereas, reports can't do that.
 

yvesdouville

Registered User
Joined
May 21, 2014
Messages
23
I will try this.


But what if i want to have a standardise report with the years in the column and all the 12 month showing all at onece in the rows even if there is no data for that particular client.



It would be easier to read when you are going thru record and doing comparaison.



Hi. Not necessarily that you did something wrong but more like you expected Access to work in a way it can't. If I were you, I would leave the query without a criteria and simply open the report with your criteria using code. For example,
Code:
DoCmd.OpenReport "ReportName", , , "client=9136"
The reason why you were getting the error when you open the report was because crosstab queries can produce columns dynamically; whereas, reports can't do that.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,845
I will try this.

But what if i want to have a standardise report with the years in the column and all the 12 month showing all at onece in the rows even if there is no data for that particular client.

It would be easier to read when you are going thru record and doing comparaison.
Hi. The bottom line is this - when you design a report, you tell it how many columns to show and where to get those information from. But, if the source of the report is changing dynamically, then the report will complain if it can't find what you told it to use from the changed source data. There is a long thread discussing how to create a "dynamic" report to work with dynamic data source. I'll see if I can find a link for you.


Another option is to design the report to expect a fixed number of columns with permanent data source. Those data source could be blank, at least the report won't complain that it can't find them. They are still available in the data source, except they're blanks. That's what I did with your report/query by specifying the fixed columns I want the query to return.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom