Solved Print Report Error when using DateAdd()

[Saphirah]

Hello everyone, i am creating a report which displays customers and their orders.
For this i am using a report containing a subreport, which lists all the orders.

Whenever i open the page preview everything works fine, the orders is listed the correct way.

But whenever i try to print or open the report in report view i get the following error

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

So i noticed when i remove the field "DueDate" from my expression the code works fine. A little context:
The order has a date field called "CreationDate" and the Customer has a numeric field called PayTarget, which correspons to the amount of days the customer has to pay his bills.
For the "DueDate" i am using the DateAdd("d";PayTarget;CreationDate). This should calculate the day the customer has to pay the bill.

Then i am FILTERING the Report on the DueDate Field using 2 Conditions:
[DueDate] >= #01/09/2021# And [DueDate] <= #03/09/2021#

Is this the correct way to do it? Is there a problem with the filter? Is there maybe a different way to calculate the due date?

Thank you very much for your help!

 

[CJ_London]

you will get that error if paytarget or creationdate does not have a value, i.e. is null. So I would check your data

also your dates need to be in the format of mm/dd/yyyy (which they may be, can't tell)

so your dates at the moment are being interpreted a 9th Jan and 9th March

 

[Saphirah]

In this case the following should work right?

Fällig: DateAdd("d";Nz([PayTarget];0);Nz([CreationDate];Date()))

I am still getting the error though. Does it maybe have something to do with the filter?

Another thing that may cause the issue: I am setting a filter in the "Open Report" event. Does the OpenReport Event not work in Print Mode? Does the "set filter" in the Supreports "Open Report" Event may cause the issue?

 

[CJ_London]

it should - as a query, does it work OK?

re the filter I would set it as a parameter for docmd.openreport rather than in the report, or even include it in your query

the open event occurs when the report is opened so should not make a difference if opened in print mode or preview mode. But temporarily disable the code and see if it then works

and if either of the fields are null, I would exclude them since the alternative is just guesswork

You are use ; rather than , in your functions - I presume this is a language requirement?

You can break this down into two parts - check the data is valid by looking a the resultant query, once happy that is OK, apply the filter

 

[Gasman]

I would set the filter from the Openreport command?
You could also calculate the DueDate in the source query?

 

[Saphirah]

it should - as a query, does it work OK?

re the filter I would set it as a parameter for docmd.openreport rather than in the report, or even include it in your query

the open event occurs when the report is opened so should not make a difference if opened in print mode or preview mode. But temporarily disable the code and see if it then works

and if either of the fields are null, I would exclude them since the alternative is just guesswork

You are use ; rather than , in your functions - I presume this is a language requirement?

You can break this down into two parts - check the data is valid by looking a the resultant query, once happy that is OK, apply the filter

Okay after further testing i can confirm it is not the filter. I removed all filters and the Open Report Event, the error still persists.
It is definitely coming from the subreport, because once removed the error does not occur anymore, when printing.

The query is running fine, and when looking at the Report in Page Preview it does display the data correctly without any errors.
But the moment i switch to report view or try to print it, the error pops up again. And i am running out of ideas here....

I can not calculate the due date in the source query, because the due date uses CreationDate, which is independent for every bill, so it can only be calculated in the subreport.

The problem is, i can not send you the database because the report depends on multiple tables in our backend, the language and documentation is german and it may contain company-intern informations.

Here is the SQL String for the subreport though. Maybe this helps.

SELECT tbl_Transactions.TransactionID, tbl_Transactions.CustomerID_F, tbl_Transactions.CreationDate, tbl_Transactions.Tax, Nz([tbl_Transactions.Price],0)*IIf([Typ]="Credit",-1,1) AS Price, Nz([tbl_Transactions.Price],0)*IIf([Typ]="Credit",-1,1)*(1+[tbl_Transactions.Tax]/100) AS PriceIncTax, tbl_Transactions.Typ, tbl_Transactions.PositionNumber, Date()-Nz([CreationDate],Date()) & " Days" AS Days, DateAdd("d",Nz([PayTarget],0),Nz([CreationDate],Date())) AS DueDate
FROM tbl_Customer INNER JOIN tbl_Transactions ON tbl_Customer.CustomerID = tbl_Transactions.CustomerID_F
WHERE ((Not (tbl_Transactions.CreationDate) Is Null) AND ((tbl_Transactions.TransactionDate) Is Null))
ORDER BY tbl_Transactions.Typ, tbl_Transactions.TransactionID;

 

[Gasman]

I can not calculate the due date in the source query, because the due date uses CreationDate, which is independent for every bill, so it can only be calculated in the subreport.

Surely if you have it in your source for the report, you can use it before the report is opened?

You even show it in the SQL?
You can bring in the PayTarget from the Customer table?

 

[Saphirah]

Surely if you have it in your source for the report, you can use it before the report is opened?

You even show it in the SQL?
You can bring in the PayTarget from the Customer table?

Oh my bad, i thought you meant calculating the due date in the parent report source. Yeah i am calculating all the values in the source.

 

[Gasman]

Oh my bad, i thought you meant calculating the due date in the parent report source. Yeah i am calculating all the values in the source.

So put the function in the query.? That way you can also check it is correct by what is displayed?

 

[Saphirah]

The DateAdd Function is inside the query. Wait, let me quickly reformat it, to make it visible better...

SELECT tbl_Transactions.TransactionID, tbl_Transactions.CustomerID_F, tbl_Transactions.CreationDate, tbl_Transactions.Tax,
Nz([tbl_Transactions.Price],0)*IIf([Typ]="Credit",-1,1) AS Price,
Nz([tbl_Transactions.Price],0)*IIf([Typ]="Credit",-1,1)*(1+[tbl_Transactions.Tax]/100) AS PriceIncTax,
tbl_Transactions.Typ, tbl_Transactions.PositionNumber, Date()-Nz([CreationDate],Date()) & " Days" AS Days,
DateAdd("d",Nz([PayTarget],0),Nz([CreationDate],Date())) AS DueDate
FROM tbl_Customer INNER JOIN tbl_Transactions ON tbl_Customer.CustomerID = tbl_Transactions.CustomerID_F
WHERE ((Not (tbl_Transactions.CreationDate) Is Null) AND ((tbl_Transactions.TransactionDate) Is Null))
ORDER BY tbl_Transactions.Typ, tbl_Transactions.TransactionID;

Like i said, the data in the query is correct, there is no error when you run the query, all the fields are filled and no field is showing any type of #Error or similar.
But the moment you try to print the above mentioned error pops up...

 

[Minty]

Remove the ordering - it will serve no purpose in the report, as the reports grouping and sort orders are what Access will use.
You will need to apply the desired ordering in the report (or sub report).

It possibly won't solve your problem but it might save you scratching your head when things aren't in the order you expect.

 

[Gasman]

Now you need no expression for DueDate, just use the query field value by that name?