Trouble with my end result (1 Viewer)

yvesdouville

Registered User.
Local time
Today, 12:33
Joined
May 21, 2014
Messages
24
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

  • Capture all years.PNG
    Capture all years.PNG
    37.6 KB · Views: 105
  • without 2018.PNG
    without 2018.PNG
    29.4 KB · Views: 97
  • error messagge.PNG
    error messagge.PNG
    6.5 KB · Views: 93
  • result needed.PNG
    result needed.PNG
    25.1 KB · Views: 99

theDBguy

I’m here to help
Staff member
Local time
Today, 12:33
Joined
Oct 29, 2018
Messages
21,358
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.
Local time
Today, 12:33
Joined
May 21, 2014
Messages
24
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
Staff member
Local time
Today, 12:33
Joined
Oct 29, 2018
Messages
21,358
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

MVP / VIP
Local time
Today, 19:33
Joined
Jan 14, 2017
Messages
18,186
I'm confused. The error message refers to 2017.
Your first screenshot shows data for both 2017 & 2018
 

yvesdouville

Registered User.
Local time
Today, 12:33
Joined
May 21, 2014
Messages
24
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
 

yvesdouville

Registered User.
Local time
Today, 12:33
Joined
May 21, 2014
Messages
24
Here you go!


Client 111 will work individually id you enter '111' in the criteria, but not the other ones..






Yes, if you can do that, I think it would make things easier.
 

Attachments

  • Database-help1.accdb
    488 KB · Views: 94

theDBguy

I’m here to help
Staff member
Local time
Today, 12:33
Joined
Oct 29, 2018
Messages
21,358
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.
Local time
Today, 12:33
Joined
May 21, 2014
Messages
24
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

  • Capture.PNG
    Capture.PNG
    6.5 KB · Views: 88

theDBguy

I’m here to help
Staff member
Local time
Today, 12:33
Joined
Oct 29, 2018
Messages
21,358

Attachments

  • design.PNG
    design.PNG
    5.4 KB · Views: 224
  • result.PNG
    result.PNG
    8 KB · Views: 221

theDBguy

I’m here to help
Staff member
Local time
Today, 12:33
Joined
Oct 29, 2018
Messages
21,358
WOW!!!

Then what is wrong?
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.
 

yvesdouville

Registered User.
Local time
Today, 12:33
Joined
May 21, 2014
Messages
24
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
Staff member
Local time
Today, 12:33
Joined
Oct 29, 2018
Messages
21,358
OK, i mis-cominicate,

Just realized.

It works in QUERY but not on the REPORT.
It is the report that provide the error.à
Okay, that makes sense. See the attached modified version of your db and open the report.
 

Attachments

  • Database-help1.zip
    31.7 KB · Views: 80

theDBguy

I’m here to help
Staff member
Local time
Today, 12:33
Joined
Oct 29, 2018
Messages
21,358
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.
Local time
Today, 12:33
Joined
May 21, 2014
Messages
24
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
Staff member
Local time
Today, 12:33
Joined
Oct 29, 2018
Messages
21,358
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

Top Bottom