Field not displayed on pdf version of report

[ML!]

This is wierd but when I generate a report in Access (2007), everything looks fine but when I save as pdf, the data in one of the fields is not displayed. Attached are snippets from the access version and the pdf version.




Both the Price per unit and cost fields are in a subreport. The cost field displays fine.

Has anyone run into this...and more importantly, does anyone have a resolution?

TIA

 

[vbaInet]

Is the field a calculated field?

 

[ML!]

Yes...and no. The field is calculated in the source query but as far as the report is concerned, no.

Exact same thing with the extended cost field that does display.

 

[ML!]

OK this is even wierder, I thought maybe the Microsoft pdf writer was translating the field incorrectly so I opened the report and created a pdf using an outside pdf writer and same thing happened. I thought that was my backup plan...

 

[boblarson]

Can you upload a copy of the database (with any sensitive information stripped)? I'd like to see if it does it on my machine too.

 

[ML!]

I found the culprit...

Bob...before I strip the db of sensitive info, can you review the following to see if you can spot the problem? It's probably my clunky logic.

There is a weekly price and a daily price available for items in a catalogue. The user selects whether a quote will be based on weekly or daily pricing.

The recordsource query checks whether it should use the daily or weekly price in the PerUnit calculation:

Price: IIf([dailyorweek]="weekly",[weeklyprice],[dailyprice])

Both the PerUnit price and the Cost fields are results of calculations in the same recordsource query.

The PerUnit value is a result of price less applicable discounts.

PerUnit: CLng(nz([Price]-(nz([LineDiscount£],([LineDiscount%]*[Price]))),nz([Price],0)))

Cost: [qty]*[PerUnit]

The Cost is an extension of the result of PerUnit calc * quantity.

If there is no price available in the tblCatalogue db for the item, the query returns a 0 on the PerUnit and Cost calculations.

We don't want to show a 0 in the quote but rather suggest to the recipient that price is available on request. (POA = Price on Application) So I if txtCost is 0, I change the visible property on the text boxes to false and make the POA label visible.

This is the code...

Select Case Me.txtCost
 
Case 0
  Me.txtCost.Visible = False
  Me.lblPOA.Visible = True
  [COLOR=red]Me.txtPerUnit.Visible = False[/COLOR]
 
Case Else
  Me.txtCost.Visible = True
  Me.lblPOA.Visible = False
 
End Select

When I comment out the red coloured line, the pdf shows the values (including a 0.00 in the price per unit column of the offending item.

Any suggestions or should I package the db for you?

Even if my calcs and/or code are flawed, it doesn't really explain why the field data is stripped from the pdf when the report in native Access views show fine...

Thanks in advance Bob

 

[ML!]

I just figured something else out, when I view in Layout view the txtPerUnit values show but not in print preview which explains the outside pdf writer anomaly.

 

[vbaInet]

Have you tried applying that same logic in the query (using an IIF() function and setting it to Null for 0 values) instead of hiding the controls?

 

[ML!]

vbaInet,

I took your advice...at least what I interpreted to be your advice . I don't think I totally got it. But it seems to work now.

This is the new PerUnit calc:

PerUnit: IIf(IsNull([Price]),Null,IIf(IsNull([LineDiscount£]),IIf(IsNull([LineDiscount%]),[Price],([Price]-([Price]*[LineDiscount%]))),([Price]-[LineDiscount£])))

The Detail Format code had to be revised:

Select Case Me.txtCost
 
  Case Is > 0
    Me.txtCost.Visible = True
    Me.lblPOA.Visible = False
  Case Else
    Me.txtCost.Visible = False
    Me.lblPOA.Visible = True
    Me.txtPerUnit.Visible = False
End Select

I think part of my problem was not really understanding the new layout view in 2007, which I've now researched.

Thanks for your help. If anyone has any other feedback - would love to hear it.

ML

 

[vbaInet]

Great!!! Glad you reached a solution.

But let me explain what I actually meant. Since the borders of your textboxes are not visible, you don't need to hide the control. If the value is Null then it wouldn't show anyway. Meaning you don't need your code at all.

Also, you could improve performance if you move your code below:

PerUnit: IIf(IsNull([Price]),Null,IIf(IsNull([LineDiscount£]),IIf(IsNull([LineDiscount%]),[Price],([Price]-([Price]*[LineDiscount%]))),([Price]-[LineDiscount£])))

to your query instead. So just copy and paste it into the query that your report is based on and set the Control Source of the textbox on the report to PerUnit.

 

[ML!]

But let me explain what I actually meant. Since the borders of your textboxes are not visible, you don't need to hide the control. If the value is Null then it wouldn't show anyway. Meaning you don't need your code at all.

Duh! I'm not very bright am I? Of course a null value wouldn't show up because it's...well...null!

BTW, the PerUnit calculation is in the query, I just wrapped it in code tags for ease of reading on one line.

Thanks for your advice vbaInet, I really appreciate it.

 

[vbaInet]

We've been there at some point in our life

Glad we could help.