Access Reports not returning accurate data

We have a SQL 2008 R2 database that functions for an Intranet Rolodex that we run Microsoft Access reports off of through an ODBC connection and we are getting inaccurate information from.

For example we have a section that we send Holiday cards off to Clients if a check mark is marked on the Intranet Site. It will be marked as 1 for positive in the Holiday column in the database for that client and it will show 1 when you run Open on the query in Access.

But the report is not showing a positive response when running the Access Report. It shows the check box for that client as negative. Where is the disconnect? Any assistance would be appreciated. I have provided the query below:


SELECT dbo_Contacts.FirstName, dbo_Contacts.LastName, dbo_Contacts.Company, dbo_Contacts.JobTitle, dbo_Contacts.BusinessStreet, dbo_Contacts.BusinessFax, dbo_Contacts.BusinessPhone, dbo_Contacts.EmailAddress, dbo_Contacts.BusinessCity, dbo_Contacts.BusinessState, dbo_Contacts.BusinessPostalCode, dbo_Contacts.BusinessPhone, dbo_Contacts.CarPhone, dbo_Contacts.BusinessPhone2, dbo_Contacts.MobilePhone, dbo_Contacts.Pager, dbo_Contacts.Birthday, dbo_Contacts.Notes, dbo_Contacts.Holiday, dbo_Contacts.Vacation, dbo_Contacts.Status, dbo_Contacts.Department, dbo_Contacts.BusinessStreet2, dbo_Contacts.BusinessStreet3, dbo_Contacts.BusinessCountry
FROM dbo_Contacts
ORDER BY Company, LastName;
regsampAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
I believe you are missing that condition from your query that should be like the one below:


SELECT dbo_Contacts.FirstName, dbo_Contacts.LastName, dbo_Contacts.Company, dbo_Contacts.JobTitle, dbo_Contacts.BusinessStreet, dbo_Contacts.BusinessFax, dbo_Contacts.BusinessPhone, dbo_Contacts.EmailAddress, dbo_Contacts.BusinessCity, dbo_Contacts.BusinessState, dbo_Contacts.BusinessPostalCode, dbo_Contacts.BusinessPhone, dbo_Contacts.CarPhone, dbo_Contacts.BusinessPhone2, dbo_Contacts.MobilePhone, dbo_Contacts.Pager, dbo_Contacts.Birthday, dbo_Contacts.Notes, dbo_Contacts.Holiday, dbo_Contacts.Vacation, dbo_Contacts.Status, dbo_Contacts.Department, dbo_Contacts.BusinessStreet2, dbo_Contacts.BusinessStreet3, dbo_Contacts.BusinessCountry
 FROM dbo_Contacts
 WHERE dbo_Contacts.Holiday = 1
 ORDER BY Company, LastName;
regsampAuthor Commented:
I must not have the right query because when I did the suggested one it changed the wrong report.  

Here are the other two querys:
SELECT dbo_Contacts.FirstName, dbo_Contacts.LastName, dbo_Contacts.Company, dbo_Contacts.JobTitle, dbo_Contacts.BusinessStreet, dbo_Contacts.BusinessFax, dbo_Contacts.BusinessPhone, dbo_Contacts.EmailAddress, dbo_Contacts.BusinessCity, dbo_Contacts.BusinessState, dbo_Contacts.BusinessPostalCode, dbo_Contacts.BusinessPhone, dbo_Contacts.CarPhone, dbo_Contacts.BusinessPhone2, dbo_Contacts.MobilePhone, dbo_Contacts.Pager, dbo_Contacts.Birthday, dbo_Contacts.Notes, dbo_Contacts.Holiday, dbo_Contacts.Vacation, dbo_Contacts.Status, dbo_Contacts.Department, dbo_Contacts.BusinessStreet2, dbo_Contacts.BusinessStreet3, dbo_Contacts.BusinessCountry
FROM dbo_Contacts
WHERE Holiday=1
ORDER BY Company, LastName;

SELECT dbo_Contacts.FirstName, dbo_Contacts.LastName, dbo_Contacts.Company, dbo_Contacts.JobTitle, dbo_Contacts.BusinessStreet, dbo_Contacts.BusinessFax, dbo_Contacts.BusinessPhone, dbo_Contacts.EmailAddress, dbo_Contacts.BusinessCity, dbo_Contacts.BusinessState, dbo_Contacts.BusinessPostalCode, dbo_Contacts.BusinessPhone, dbo_Contacts.CarPhone, dbo_Contacts.BusinessPhone2, dbo_Contacts.MobilePhone, dbo_Contacts.Pager, dbo_Contacts.Birthday, dbo_Contacts.Notes, dbo_Contacts.Holiday, dbo_Contacts.Vacation, dbo_Contacts.Status, dbo_Contacts.Department, dbo_Contacts.BusinessStreet2, dbo_Contacts.BusinessStreet3, dbo_Contacts.BusinessCountry
FROM dbo_Contacts
WHERE Vacation=1
ORDER BY Company, LastName;
regsampAuthor Commented:
What the Report looks like

There should be Holiday and Vacation boxes with a Yes.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

regsampAuthor Commented:
I checked with the source query for the report above and it is definitely the query below. How do I alter the query to show if the Holiday and Vacation boxes are yes if they are 1 in the database?

SELECT dbo_Contacts.FirstName, dbo_Contacts.LastName, dbo_Contacts.Company, dbo_Contacts.JobTitle, dbo_Contacts.BusinessStreet, dbo_Contacts.BusinessFax, dbo_Contacts.BusinessPhone, dbo_Contacts.EmailAddress, dbo_Contacts.BusinessCity, dbo_Contacts.BusinessState, dbo_Contacts.BusinessPostalCode, dbo_Contacts.BusinessPhone, dbo_Contacts.CarPhone, dbo_Contacts.BusinessPhone2, dbo_Contacts.MobilePhone, dbo_Contacts.Pager, dbo_Contacts.Birthday, dbo_Contacts.Notes, dbo_Contacts.Holiday, dbo_Contacts.Vacation, dbo_Contacts.Status, dbo_Contacts.Department, dbo_Contacts.BusinessStreet2, dbo_Contacts.BusinessStreet3, dbo_Contacts.BusinessCountry
FROM dbo_Contacts
ORDER BY Company, LastName;
PatHartmanCommented:
If you run the query, what do you see in the columns?

Why do I see "no" above the empty checkboxes?  Are you sure the controls are bound to the correct columns?

Did you look at the entire report?  At least find a record you know to contain a 1 and look at it in the report.
regsampAuthor Commented:
If I run the query I will see 1 in some of the columns where the client has been marked off in the check box on our Intranet site for Holiday or Vacation.

You are seeing "no" above the empty check boxes because for some reason the report is only shooting back "no" when it should be "yes" in some instances. That is the main problem.

I have found records that I know to contain a 1 and when I run the query it shows it in the table but when I run the report it still shows "no"

The only thing that changed when this stopped working was we moved from an old server that had SQL 2000 to a new server with SQL 2008 R2. For giggles I ran the same old report on the old server with the old database with the same query that is not working and it returned "yes" with records that had a 1.
PatHartmanCommented:
Access uses -1 as the True value and 0 as the False value.  SQL Server uses 1 and 0 but I have never had any problem with SQL Server tables.  Take a look at the ODBC driver you are using and try using a different one if you have one available.  I think SQL Server Native Client 10.0 or newer might solve the problem.
regsampAuthor Commented:
Okay, I will download SQL Server Native Client 10.0 and try it and then update here. It will be a little while as I don't have access to it until the morning but I will update.
Gustav BrockCIOCommented:
The ODBC driver translates the 0 and 1 for False and True in SQL Server to VBA False and True where the numeric equivalents are 0 and -1.

Thus, you have two options:

    WHERE Holiday = -1
or:
    WHERE Holiday = True

Changing the driver wouldn't change this behaviour.

/gustav
regsampAuthor Commented:
Hmmm, okay gustav. The query use to always work fine in SQL 2000 but if I have to change it what will it look like below if I want to account for the Holiday and Vacation too?

SELECT dbo_Contacts.FirstName, dbo_Contacts.LastName, dbo_Contacts.Company, dbo_Contacts.JobTitle, dbo_Contacts.BusinessStreet, dbo_Contacts.BusinessFax, dbo_Contacts.BusinessPhone, dbo_Contacts.EmailAddress, dbo_Contacts.BusinessCity, dbo_Contacts.BusinessState, dbo_Contacts.BusinessPostalCode, dbo_Contacts.BusinessPhone, dbo_Contacts.CarPhone, dbo_Contacts.BusinessPhone2, dbo_Contacts.MobilePhone, dbo_Contacts.Pager, dbo_Contacts.Birthday, dbo_Contacts.Notes, dbo_Contacts.Holiday, dbo_Contacts.Vacation, dbo_Contacts.Status, dbo_Contacts.Department, dbo_Contacts.BusinessStreet2, dbo_Contacts.BusinessStreet3, dbo_Contacts.BusinessCountry
FROM dbo_Contacts
ORDER BY Company, LastName;
Gustav BrockCIOCommented:
Oh, I see it is not a question about filtering but displaying Yes or No for the checkboxes.

When you run the query as is, it will display the values returned for Holiday and Vacation.
Perhaps they are returned as 0 and 1 (because of the origin from SQL Server 2000).
Still, the checkboxes should understand these as False/True or unchecked/checked.
If the Yes/No textbox is set with "Yes/No" as the Format property, these should also display correctly.

/gustav
regsampAuthor Commented:
"Oh, I see it is not a question about filtering but displaying Yes or No for the checkboxes."

Exactly. I can see in the table many ones that have 0 and 1 but the Yes is not coming through for any of them.  Where exactly can I check the Format Property? I go into the table but I just see the Holiday and Vacation columns with 1's or 0's.
Gustav BrockCIOCommented:
It's not the table but the report to study.

If it won't adopt, you can modify the query to force things in place:

SELECT
dbo_Contacts.FirstName, dbo_Contacts.LastName, dbo_Contacts.Company, dbo_Contacts.JobTitle, dbo_Contacts.BusinessStreet, dbo_Contacts.BusinessFax, dbo_Contacts.BusinessPhone, dbo_Contacts.EmailAddress, dbo_Contacts.BusinessCity, dbo_Contacts.BusinessState, dbo_Contacts.BusinessPostalCode, dbo_Contacts.BusinessPhone, dbo_Contacts.CarPhone, dbo_Contacts.BusinessPhone2, dbo_Contacts.MobilePhone, dbo_Contacts.Pager, dbo_Contacts.Birthday, dbo_Contacts.Notes, dbo_Contacts.Holiday, dbo_Contacts.Vacation, dbo_Contacts.Status, dbo_Contacts.Department, dbo_Contacts.BusinessStreet2, dbo_Contacts.BusinessStreet3, dbo_Contacts.BusinessCountry,
CBool(dbo_Contacts.Holiday) As HolidayCheck,
CBool(dbo_Contacts.Vacation) As VacationCheck
FROM dbo_Contacts;

Ordering has been removed. Always define that in the report itself.

Now, in your report, bind your checkboxes and textboxes to the new *Check fields.

/gustav
regsampAuthor Commented:
Let me try that query and see and this is somewhat new for me but how do I bind the checkboxes and textboxes to the new fields?
Gustav BrockCIOCommented:
It's the ControlSource property of these.

/gustav
regsampAuthor Commented:
In here?
Gustav BrockCIOCommented:
Well, sort of. However, coming this far, you could also just modify the expression.
As you can see, it checks for -1 which your original never returned.
So you could either have:

=IIf([Holiday]=1,'YES','NO')

or, with the new field:

=IIf([HolidayCheck]=-1,'YES','NO')

or simply:

=IIf([HolidayCheck],'YES','NO')

/gustav
regsampAuthor Commented:
And have the same for vacation? =IIf([VacationCheck],'YES','NO') ?
Gustav BrockCIOCommented:
Yes.

/gustav
regsampAuthor Commented:
Okay, let me try it here. Here goes.
regsampAuthor Commented:
That seems to have worked! I checked known records that I know are marked as 1 in the table either for Holiday or Vacation and they show us "Yes" on the report. I suppose the conversion from SQL 2000 to SQL 2008 R2 had a problem with the original query. Thank you so much!
Gustav BrockCIOCommented:
Great!
Don't know what the cause could be. Both 2000 and 2008 supports the bit data type:

http://www.connectionstrings.com/sql-server-data-types-reference/

/gustav
PatHartmanCommented:
You don't need the expression at all.  Simply bind the control to the field and then change the property on the text box to show Yes/No.Yes/No format
PS - your question was about the checkbox and there is a checkbox on the report.  I'm not sure why you also are showing Yes or No.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
regsampAuthor Commented:
We are showing Yes or No check boxes because if the client has Holiday as 1 and Vacation as 1 the checkboxes will have Yes above both. We need to have the proper Yes or No above the box when it is a 1 or 0.  

P, you think the Format setting would have done the same thing? Let see what it is at.
regsampAuthor Commented:
I should have explained it better about the Yes and No above the check boxes. It has been awhile since I have done Reports. If the Format Yes/No would have worked then I would like to give both credit.
Gustav BrockCIOCommented:
I suggested that previously, but even if it should work, it would display Yes or No and not YES or NO.

If it works now, I would leave it as is.

Did you get the ordering right? As I noted, no sorting should take place in the query but be specified in the report as it in any case will ignore the ordering in the query.

/gustav
regsampAuthor Commented:
Sorry, I missed that previous suggestion. The ordering seems correct as it appears as it always was. I am going to try on another machine with the Format Yes/No to see what it does.
Gustav BrockCIOCommented:
Yes that's the trap, it may seem correct, but that is pure luck.
Sorting has to be specified in Grouping and Sorting.

/gustav
PatHartmanCommented:
Checkboxes are different controls from text boxes.  When you use a checkbox to display a Boolean, you get a check for true and it is empty for false.  There is no format property.  Text boxes have format properties.  If you don't set a format, then you see the raw value which is -1 or 0.  If you set the format property you can choose Yes/No or True/False which ever makes more sense for the data you are displaying.

It isn't necessary to change the point awards.  However, I would probably remove the expression and use the format.  My approach to Access is to use properties whenever possible, then queries, then expressions, then code.  Expressions are actually my least favorite method since there is no good way to see them short of clicking on each control, one at a time.
regsampAuthor Commented:
I see what you are saying Pat. I am trying the format option on another test machine right now that has the same access database with an odbc connection to the sql database and see how that responds.
regsampAuthor Commented:
I did the Yes/No format on the Holiday and Vacation on a test machine and it did not work.
regsampAuthor Commented:
Untitled.jpg
PatHartmanCommented:
You have to remove the expression from the controlsource.  The controlsource must be bound to the native Boolean field.
regsampAuthor Commented:
Okay, let me try that.
regsampAuthor Commented:
That did work. Thank you both for the patience and help. I really would like to give points to both and no one has an objection. Both have worked hard to help.
regsampAuthor Commented:
Great help and great patience worthing with me on both parts.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.