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;
Microsoft AccessMicrosoft SQL ServerProgramming

Avatar of undefined
Last Comment
regsamp

8/22/2022 - Mon
lcohan

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

ASKER
What the Report looks like

There should be Holiday and Vacation boxes with a Yes.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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;
PatHartman

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.
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PatHartman

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.
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.
Gustav Brock

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
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;
Gustav Brock

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
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gustav Brock

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
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?
Gustav Brock

It's the ControlSource property of these.

/gustav
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
regsamp

ASKER
In here?
SOLUTION
Gustav Brock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
regsamp

ASKER
And have the same for vacation? =IIf([VacationCheck],'YES','NO') ?
Gustav Brock

Yes.

/gustav
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
regsamp

ASKER
Okay, let me try it here. Here goes.
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!
Gustav Brock

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
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.
Gustav Brock

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
Gustav Brock

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

/gustav
PatHartman

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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.
regsamp

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

ASKER
Untitled.jpg
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PatHartman

You have to remove the expression from the controlsource.  The controlsource must be bound to the native Boolean field.
regsamp

ASKER
Okay, let me try that.
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
regsamp

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