Solved

Access Reports not returning accurate data

Posted on 2014-11-11
36
158 Views
Last Modified: 2014-11-12
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;
0
Comment
Question by:regsamp
  • 21
  • 9
  • 5
  • +1
36 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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;
0
 

Author Comment

by:regsamp
Comment Utility
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;
0
 

Author Comment

by:regsamp
Comment Utility
What the Report looks like

There should be Holiday and Vacation boxes with a Yes.
0
 

Author Comment

by:regsamp
Comment Utility
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;
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 

Author Comment

by:regsamp
Comment Utility
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.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 

Author Comment

by:regsamp
Comment Utility
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
0
 

Author Comment

by:regsamp
Comment Utility
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;
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
0
 

Author Comment

by:regsamp
Comment Utility
"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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
0
 

Author Comment

by:regsamp
Comment Utility
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?
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
It's the ControlSource property of these.

/gustav
0
 

Author Comment

by:regsamp
Comment Utility
In here?
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
Comment Utility
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
0
 

Author Comment

by:regsamp
Comment Utility
And have the same for vacation? =IIf([VacationCheck],'YES','NO') ?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Yes.

/gustav
0
 

Author Comment

by:regsamp
Comment Utility
Okay, let me try it here. Here goes.
0
 

Author Comment

by:regsamp
Comment Utility
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!
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
Comment Utility
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.
0
 

Author Comment

by:regsamp
Comment Utility
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.
0
 

Author Comment

by:regsamp
Comment Utility
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
0
 

Author Comment

by:regsamp
Comment Utility
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.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Yes that's the trap, it may seem correct, but that is pure luck.
Sorting has to be specified in Grouping and Sorting.

/gustav
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 

Author Comment

by:regsamp
Comment Utility
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.
0
 

Author Comment

by:regsamp
Comment Utility
I did the Yes/No format on the Holiday and Vacation on a test machine and it did not work.
0
 

Author Comment

by:regsamp
Comment Utility
Untitled.jpg
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
You have to remove the expression from the controlsource.  The controlsource must be bound to the native Boolean field.
0
 

Author Comment

by:regsamp
Comment Utility
Okay, let me try that.
0
 

Author Comment

by:regsamp
Comment Utility
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.
0
 

Author Closing Comment

by:regsamp
Comment Utility
Great help and great patience worthing with me on both parts.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now