Access Report Not Matching Query - Visitor Forum


	Back to Visitor Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Access Report Not Matching Query

Alex Crews

13 months ago

I have a query which lets me filter several categories for our orders and also links with our customer credit status.

In our setup some customers do not have credit status so in order to pull it in I use in the query
GTS: IIf([GoodtoShip]=-1,"Y",IIf([GoodtoShip]=0,"N","None"))

This puts up a Y if we can ship, a N if not and puts a None when there is no info and i can apply some conditional formatting.

This works perfectly in my form

However I also made a report that is based on the same query for the data source YET the report changes anything Null to N. I can open the query at the same time and it is working but the report still makes all null fields N instead of none. This totally ruins the report as we don't stop shipments for a none. To be clear this occurs when that customer has no entry at all in our credit list.

Why would the report evaluate the same query differently then the form...

We are running access 2003.

	Sami Shamma @Reply 13 months ago
Unfortunately, not many of us have 2003 versions of Access to test what's happening with your database. Perhaps Richard has one. However and meanwhile, can you please post a screenshot of the report in design view showing the property of GoodtoShip?

	Alex Hedley @Reply 13 months ago
`GTS` is a Field in your Query? Does that have any Nulls showing?

	Matt Hall @Reply 13 months ago
I wonder if this Null video might help.

Alex Crews OP @Reply

13 months ago

Response to questions:

Here is the properties of GoodToShip
https://imgur.com/a/9GmA7Mm

I also did a screen shot of the report and the query that was run at the same time.
https://imgur.com/a/xEGA0dm

It is lined up, so you can see the report lines 3 and 4 show No but they should be Null or None. It works just fine in my form but the report does it differently...

The record source for the report is the query shown.

	Alex Hedley @Reply 13 months ago
What is `GTSNull`?

	Alex Crew OP @Reply 13 months ago
Response to post: `GTSNull: IsNull([GoodtoShip])` That was just me testing how the isnull was evaluating. Its not necessary for this report just me troubleshooting.

	Alex Hedley @Reply 13 months ago
Legacy Tip: NULL v. Zero Length String Tech Help: Zero Length

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Visitor Forum.

Next Unseen

New Feature: Comment Live View