Link to home
Get AccessLog in
Avatar of regsamp
regsamp

asked on

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;
Avatar of lcohan
lcohan
Flag of Canada image

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;
Avatar of regsamp
regsamp

ASKER

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;
Avatar of regsamp

ASKER

User generated image

There should be Holiday and Vacation boxes with a Yes.
Avatar of regsamp

ASKER

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;
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.
Avatar of regsamp

ASKER

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.
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.
Avatar of regsamp

ASKER

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.
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
Avatar of regsamp

ASKER

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;
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
Avatar of regsamp

ASKER

"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.
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
Avatar of regsamp

ASKER

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?
It's the ControlSource property of these.

/gustav
Avatar of regsamp

ASKER

User generated image
SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of regsamp

ASKER

And have the same for vacation? =IIf([VacationCheck],'YES','NO') ?
Yes.

/gustav
Avatar of regsamp

ASKER

Okay, let me try it here. Here goes.
Avatar of regsamp

ASKER

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!
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of regsamp

ASKER

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.
Avatar of regsamp

ASKER

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.
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
Avatar of regsamp

ASKER

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.
Yes that's the trap, it may seem correct, but that is pure luck.
Sorting has to be specified in Grouping and Sorting.

/gustav
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.
Avatar of regsamp

ASKER

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.
Avatar of regsamp

ASKER

I did the Yes/No format on the Holiday and Vacation on a test machine and it did not work.
Avatar of regsamp

ASKER

User generated image
You have to remove the expression from the controlsource.  The controlsource must be bound to the native Boolean field.
Avatar of regsamp

ASKER

Okay, let me try that.
Avatar of regsamp

ASKER

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.
Avatar of regsamp

ASKER

Great help and great patience worthing with me on both parts.