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.BusinessStree
FROM dbo_Contacts
ORDER BY Company, LastName;
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.BusinessStree
FROM dbo_Contacts
ORDER BY Company, LastName;
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.BusinessStree
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.BusinessStree
FROM dbo_Contacts
WHERE Vacation=1
ORDER BY Company, LastName;
Here are the other two querys:
SELECT dbo_Contacts.FirstName, dbo_Contacts.LastName, dbo_Contacts.Company, dbo_Contacts.JobTitle, dbo_Contacts.BusinessStree
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.BusinessStree
FROM dbo_Contacts
WHERE Vacation=1
ORDER BY Company, LastName;
ASKER
There should be Holiday and Vacation boxes with a Yes.
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.BusinessStree
FROM dbo_Contacts
ORDER BY Company, LastName;
SELECT dbo_Contacts.FirstName, dbo_Contacts.LastName, dbo_Contacts.Company, dbo_Contacts.JobTitle, dbo_Contacts.BusinessStree
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.
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.
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.
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.
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
Thus, you have two options:
WHERE Holiday = -1
or:
WHERE Holiday = True
Changing the driver wouldn't change this behaviour.
/gustav
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.BusinessStree
FROM dbo_Contacts
ORDER BY Company, LastName;
SELECT dbo_Contacts.FirstName, dbo_Contacts.LastName, dbo_Contacts.Company, dbo_Contacts.JobTitle, dbo_Contacts.BusinessStree
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
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
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.
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.BusinessStree
CBool(dbo_Contacts.Holiday
CBool(dbo_Contacts.Vacatio
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
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.BusinessStree
CBool(dbo_Contacts.Holiday
CBool(dbo_Contacts.Vacatio
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
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
/gustav
ASKER
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
And have the same for vacation? =IIf([VacationCheck],'YES'
Yes.
/gustav
/gustav
ASKER
Okay, let me try it here. Here goes.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
P, you think the Format setting would have done the same thing? Let see what it is at.
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
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
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
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.
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.
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.
ASKER
I did the Yes/No format on the Holiday and Vacation on a test machine and it did not work.
ASKER
You have to remove the expression from the controlsource. The controlsource must be bound to the native Boolean field.
ASKER
Okay, let me try that.
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.
ASKER
Great help and great patience worthing with me on both parts.
SELECT dbo_Contacts.FirstName, dbo_Contacts.LastName, dbo_Contacts.Company, dbo_Contacts.JobTitle, dbo_Contacts.BusinessStree
FROM dbo_Contacts
WHERE dbo_Contacts.Holiday = 1
ORDER BY Company, LastName;