problem with 2003 reports not matching

[Chris Cowa]

I have an unusual problem. I have a DB that is split. It was created
originally in Access 2000. We are now a mixed house of 2000 and 2003. The BE
is on a server; wheras the MDB/MDE is on the users PC.

Here is the problem.

I have a report that runs that selects if a date is over due. In 2000 it
shows 7 records, in 2003 it shows 5 records. Now here is the funny part. I
have a test machine with XP OS and Access 2003 on it. It shows all 7
records. whereas I have a Technician build 50 others that is similar. The
report runs good on mine (7 records).

I am trying to determine where in his setup that is different then mind.
It's blowing my mind.

I can take the same MDB and place on my 2000 system, my 2003 system and
someone elses 2003 system. In the first 2 I get 7 records; in someone elses
I get 5 records.

Help

Chris

 

[Chris Mills]

Sometimes it's necessary, from A2000 on and not before, to put Trim(field) in
query criteria. This may not apply to you, and it does not make sense to me,
nevertheless I see what I see (and fixed it) which was somewhat like your
description (variable results).

A Date field may contain the date and time. Maybe you should restrict it to
just date before comparing.

Regardless, it's up to you to identify a single record which fails/doesn't
fail, and then you can play with the criteria until you find what the cause
is. Standard diagnostics really.

If you don't get anywhere, I'd suggest posting the WHERE clause of your query,
and how the criteria field is stored. Maybe someone might see some trap there.

Because I have struck "similar", I'm not really that surprised.
How boring if Access did the same ol' thing every time!
Chris

 

[Allen Browne]

First thing to check is that the machines are all connected to the same data
file. You would not be the first to strike this kind of issue when some
machines are connected to a different back end file.

Next, open the back end, and try a repair:
Tools | Database Utilities | Compact And Repair
If the date field is indexed, and the index is damaged, this should fix the
problem.

There is actually a hotfix out that addresses records missing in a report in
A2003 after SP1 has been applied. Not sure this sounds like your issue, but
details at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;840656

If that does not solve the issue, double-check that the Name AutoCorrect
boxes are unchecked in all databases (the back end, and each front end)
under:
Tools | Options | General
It is possible for Access to misunderstand the basic names in the query if
this misfeature is on. More info:
http://allenbrowne.com/bug-03.html
Then compact again.

If the problem persists, the next thing to look for is different versions of
JET on the machines that return different results. JET is the query engine
in Access. Locate the file msjet40.dll, typically in \windows\system32.
Right-click it, and on the Version tab you should see 4.0.8xxx.0. The xxx
digits don't matter, but if you don't see the 8, download JET 4 SP 8 from:
http://support.microsoft.com/gp/sp

If you are still stuck, does the source query for the report return the
correct number of records?
- No: the problem is with JET drawing in the data. Post the SQL statement.
Check the service packs on the server.
- Yes: the problem is with the report presenting the data (e.g. code in the
report's events.)

 

[Chris Mills]

First thing to check is that the machines are all connected to the same data

That is so simple and obvious, it never even crossed my bumblebee mind before
going through the windscreen!!

Nevertheless, I struck one minor similar issue between A97 and A2k, for the
same query in apparently all other respects.

Cheers
Chris
(not the poster)

 

[Chris]

Here is the SQL query that is used.

SELECT StrConv([LName] & ", " & [FName] & IIf(IsNull([MI]),""," " & [MI] &
"."),3) AS Client, tblClient.SSN, tblClient.DOB,
IIf(IsNull([DOB]),"Unknown",CalcAgeString([DOB])) AS Age, StrConv(([CMFName]
& " " & [CMLName]),3) AS CM, tblCSCs_LU.CMLName, tblClient.NextAssess,
IIf([NextAssess]<Date(),"OVERDUE","") AS OverDue
FROM tblCSCs_LU INNER JOIN tblClient ON tblCSCs_LU.CaseMgrID =
tblClient.CSCID
WHERE (((tblClient.NextAssess)<=Date() Or (tblClient.NextAssess) Like
DatePart("m",Date()) & "/*/" & DatePart("yyyy",Date())) AND
((tblClient.StatusID)=1) AND
((tblClient.CSCID)=[Forms]![frmReports]![cboCSC]))
ORDER BY tblCSCs_LU.CMLName;

What is happening is in Access 2000 the result shows 7 records; in Access
2003 it shows 5 records. Not sure why the 2 are dropped. Not sure if the
DatePart is the issue. HELP

 

[Allen Browne]

Assuming NextAssess is a Date/Time type field, this condition probably is
the problem.

The Like operator performs a *string* comparision. The format of the string
is unreliable (depending on leading zeros, person's regional settings, etc),
so the string comparision will yield different results on different
machines. It is also very inefficient--unable to take advantage of any index
on the NextAccess field.

I think you are asking for any date in the current month? To do that as a
date comparsion, try something like this:
OR tblClient.NextAccess Between DateSerial(Year(Date()), Month(Date()),
1)
And DateSerial(Year(Date()), Month(Date())+1,0)

You could also use:
Month(tblClient.NextAccess) = Month(Date())
AND Year(tblClient.NextAccess) = Year(Date())
This numeric comparison should be reliable, but is still inefficient (cannot
use the index.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here is the SQL query that is used.

SELECT StrConv([LName] & ", " & [FName] & IIf(IsNull([MI]),""," " & [MI] &
"."),3) AS Client, tblClient.SSN, tblClient.DOB,
IIf(IsNull([DOB]),"Unknown",CalcAgeString([DOB])) AS Age,
StrConv(([CMFName]
& " " & [CMLName]),3) AS CM, tblCSCs_LU.CMLName, tblClient.NextAssess,
IIf([NextAssess]<Date(),"OVERDUE","") AS OverDue
FROM tblCSCs_LU INNER JOIN tblClient ON tblCSCs_LU.CaseMgrID =
tblClient.CSCID
WHERE (((tblClient.NextAssess)<=Date() Or (tblClient.NextAssess) Like
DatePart("m",Date()) & "/*/" & DatePart("yyyy",Date())) AND
((tblClient.StatusID)=1) AND
((tblClient.CSCID)=[Forms]![frmReports]![cboCSC]))
ORDER BY tblCSCs_LU.CMLName;

What is happening is in Access 2000 the result shows 7 records; in Access
2003 it shows 5 records. Not sure why the 2 are dropped. Not sure if the
DatePart is the issue. HELP

I have an unusual problem. I have a DB that is split. It was created
originally in Access 2000. We are now a mixed house of 2000 and 2003.
The BE is on a server; wheras the MDB/MDE is on the users PC.

Here is the problem.

I have a report that runs that selects if a date is over due. In 2000 it
shows 7 records, in 2003 it shows 5 records. Now here is the funny part.
I have a test machine with XP OS and Access 2003 on it. It shows all 7
records. whereas I have a Technician build 50 others that is similar. The
report runs good on mine (7 records).

I am trying to determine where in his setup that is different then mind.
It's blowing my mind.

I can take the same MDB and place on my 2000 system, my 2003 system
and someone elses 2003 system. In the first 2 I get 7 records; in someone
elses I get 5 records.

 

[Chris]

Allen

Thanks that fixed most of the issues. Now the only problem I have is that
anything that was to show up for tomorrow through the rest of the month
don't show. They do in the Access 2000 app though.

Here is the current coding

SELECT StrConv([LName] & ", " & [FName] & IIf(IsNull([MI]),""," " & [MI] &
"."),3) AS Client, tblClient.SSN, tblClient.DOB,
IIf(IsNull([DOB]),"Unknown",CalcAgeString([DOB])) AS Age, StrConv(([CMFName]
& " " & [CMLName]),3) AS CM, tblCSCs_LU.CMLName, tblClient.NextAssess,
IIf([NextAssess]<Date(),"OVERDUE","") AS OverDue
FROM tblCSCs_LU INNER JOIN tblClient ON tblCSCs_LU.CaseMgrID =
tblClient.CSCID

WHERE (((tblClient.NextAssess)<=Date()) AND ((tblClient.StatusID)=1) AND
((tblClient.CSCID)=[Forms]![frmReports]![cboCSC])) OR
(((tblClient.NextAssess)=DateSerial(Year(Date()),Month(Date()),1) And
(tblClient.NextAssess)=DateSerial(Year(Date()),Month(Date())+1,0))

AND ((tblClient.StatusID)=1) AND
((tblClient.CSCID)=[Forms]![frmReports]![cboCSC]))
ORDER BY tblCSCs_LU.CMLName;

The WHERE Clause is where I changed the information. So how can I get it to
show all the due items for the rest of the month?

Assuming NextAssess is a Date/Time type field, this condition probably is
the problem.

The Like operator performs a *string* comparision. The format of the string
is unreliable (depending on leading zeros, person's regional settings, etc),
so the string comparision will yield different results on different
machines. It is also very inefficient--unable to take advantage of any index
on the NextAccess field.

I think you are asking for any date in the current month? To do that as a
date comparsion, try something like this:
OR tblClient.NextAccess Between DateSerial(Year(Date()), Month(Date()),
1)
And DateSerial(Year(Date()), Month(Date())+1,0)

You could also use:
Month(tblClient.NextAccess) = Month(Date())
AND Year(tblClient.NextAccess) = Year(Date())
This numeric comparison should be reliable, but is still inefficient (cannot
use the index.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here is the SQL query that is used.

SELECT StrConv([LName] & ", " & [FName] & IIf(IsNull([MI]),""," " & [MI] &
"."),3) AS Client, tblClient.SSN, tblClient.DOB,
IIf(IsNull([DOB]),"Unknown",CalcAgeString([DOB])) AS Age,
StrConv(([CMFName]
& " " & [CMLName]),3) AS CM, tblCSCs_LU.CMLName, tblClient.NextAssess,
IIf([NextAssess]<Date(),"OVERDUE","") AS OverDue
FROM tblCSCs_LU INNER JOIN tblClient ON tblCSCs_LU.CaseMgrID =
tblClient.CSCID
WHERE (((tblClient.NextAssess)<=Date() Or (tblClient.NextAssess) Like
DatePart("m",Date()) & "/*/" & DatePart("yyyy",Date())) AND
((tblClient.StatusID)=1) AND
((tblClient.CSCID)=[Forms]![frmReports]![cboCSC]))
ORDER BY tblCSCs_LU.CMLName;

What is happening is in Access 2000 the result shows 7 records; in Access
2003 it shows 5 records. Not sure why the 2 are dropped. Not sure if the
DatePart is the issue. HELP

I have an unusual problem. I have a DB that is split. It was created
originally in Access 2000. We are now a mixed house of 2000 and 2003.
The BE is on a server; wheras the MDB/MDE is on the users PC.

Here is the problem.

I have a report that runs that selects if a date is over due. In 2000 it
shows 7 records, in 2003 it shows 5 records. Now here is the funny part.
I have a test machine with XP OS and Access 2003 on it. It shows all 7
records. whereas I have a Technician build 50 others that is similar. The
report runs good on mine (7 records).

I am trying to determine where in his setup that is different then mind.
It's blowing my mind.

I can take the same MDB and place on my 2000 system, my 2003 system
and someone elses 2003 system. In the first 2 I get 7 records; in someone
elses I get 5 records.

 

[Allen Browne]

Looks like you used = where I suggested the Between operator.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen

Thanks that fixed most of the issues. Now the only problem I have is that
anything that was to show up for tomorrow through the rest of the month
don't show. They do in the Access 2000 app though.

Here is the current coding

SELECT StrConv([LName] & ", " & [FName] & IIf(IsNull([MI]),""," " & [MI] &
"."),3) AS Client, tblClient.SSN, tblClient.DOB,
IIf(IsNull([DOB]),"Unknown",CalcAgeString([DOB])) AS Age,
StrConv(([CMFName]
& " " & [CMLName]),3) AS CM, tblCSCs_LU.CMLName, tblClient.NextAssess,
IIf([NextAssess]<Date(),"OVERDUE","") AS OverDue
FROM tblCSCs_LU INNER JOIN tblClient ON tblCSCs_LU.CaseMgrID =
tblClient.CSCID

WHERE (((tblClient.NextAssess)<=Date()) AND ((tblClient.StatusID)=1) AND
((tblClient.CSCID)=[Forms]![frmReports]![cboCSC])) OR
(((tblClient.NextAssess)
= '<====== here
DateSerial(Year(Date()),Month(Date()),1) And
(tblClient.NextAssess)=DateSerial(Year(Date()),Month(Date())+1,0))

AND ((tblClient.StatusID)=1) AND
((tblClient.CSCID)=[Forms]![frmReports]![cboCSC]))
ORDER BY tblCSCs_LU.CMLName;

The WHERE Clause is where I changed the information. So how can I get it
to
show all the due items for the rest of the month?

Assuming NextAssess is a Date/Time type field, this condition probably is
the problem.

The Like operator performs a *string* comparision. The format of the string
is unreliable (depending on leading zeros, person's regional settings, etc),
so the string comparision will yield different results on different
machines. It is also very inefficient--unable to take advantage of any index
on the NextAccess field.

I think you are asking for any date in the current month? To do that as a
date comparsion, try something like this:
OR tblClient.NextAccess Between DateSerial(Year(Date()), Month(Date()),
1)
And DateSerial(Year(Date()), Month(Date())+1,0)

You could also use:
Month(tblClient.NextAccess) = Month(Date())
AND Year(tblClient.NextAccess) = Year(Date())
This numeric comparison should be reliable, but is still inefficient (cannot
use the index.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here is the SQL query that is used.

SELECT StrConv([LName] & ", " & [FName] & IIf(IsNull([MI]),""," " &
[MI] &
"."),3) AS Client, tblClient.SSN, tblClient.DOB,
IIf(IsNull([DOB]),"Unknown",CalcAgeString([DOB])) AS Age,
StrConv(([CMFName]
& " " & [CMLName]),3) AS CM, tblCSCs_LU.CMLName, tblClient.NextAssess,
IIf([NextAssess]<Date(),"OVERDUE","") AS OverDue
FROM tblCSCs_LU INNER JOIN tblClient ON tblCSCs_LU.CaseMgrID =
tblClient.CSCID
WHERE (((tblClient.NextAssess)<=Date() Or (tblClient.NextAssess) Like
DatePart("m",Date()) & "/*/" & DatePart("yyyy",Date())) AND
((tblClient.StatusID)=1) AND
((tblClient.CSCID)=[Forms]![frmReports]![cboCSC]))
ORDER BY tblCSCs_LU.CMLName;

What is happening is in Access 2000 the result shows 7 records; in Access
2003 it shows 5 records. Not sure why the 2 are dropped. Not sure if
the
DatePart is the issue. HELP


I have an unusual problem. I have a DB that is split. It was created
originally in Access 2000. We are now a mixed house of 2000 and 2003.
The BE is on a server; wheras the MDB/MDE is on the users PC.

Here is the problem.

I have a report that runs that selects if a date is over due. In 2000 it
shows 7 records, in 2003 it shows 5 records. Now here is the funny part.
I have a test machine with XP OS and Access 2003 on it. It shows all 7
records. whereas I have a Technician build 50 others that is similar. The
report runs good on mine (7 records).

I am trying to determine where in his setup that is different then mind.
It's blowing my mind.

I can take the same MDB and place on my 2000 system, my 2003 system
and someone elses 2003 system. In the first 2 I get 7 records; in someone
elses I get 5 records.

 

[Chris]

Thanks it works greta now.

Looks like you used = where I suggested the Between operator.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen

Thanks that fixed most of the issues. Now the only problem I have is that
anything that was to show up for tomorrow through the rest of the month
don't show. They do in the Access 2000 app though.

Here is the current coding

SELECT StrConv([LName] & ", " & [FName] & IIf(IsNull([MI]),""," " & [MI] &
"."),3) AS Client, tblClient.SSN, tblClient.DOB,
IIf(IsNull([DOB]),"Unknown",CalcAgeString([DOB])) AS Age,
StrConv(([CMFName]
& " " & [CMLName]),3) AS CM, tblCSCs_LU.CMLName, tblClient.NextAssess,
IIf([NextAssess]<Date(),"OVERDUE","") AS OverDue
FROM tblCSCs_LU INNER JOIN tblClient ON tblCSCs_LU.CaseMgrID =
tblClient.CSCID

WHERE (((tblClient.NextAssess)<=Date()) AND ((tblClient.StatusID)=1) AND
((tblClient.CSCID)=[Forms]![frmReports]![cboCSC])) OR
(((tblClient.NextAssess)
= '<====== here
DateSerial(Year(Date()),Month(Date()),1) And
(tblClient.NextAssess)=DateSerial(Year(Date()),Month(Date())+1,0))

AND ((tblClient.StatusID)=1) AND
((tblClient.CSCID)=[Forms]![frmReports]![cboCSC]))
ORDER BY tblCSCs_LU.CMLName;

The WHERE Clause is where I changed the information. So how can I get it
to
show all the due items for the rest of the month?

Assuming NextAssess is a Date/Time type field, this condition probably is
the problem.

The Like operator performs a *string* comparision. The format of the string
is unreliable (depending on leading zeros, person's regional settings, etc),
so the string comparision will yield different results on different
machines. It is also very inefficient--unable to take advantage of any index
on the NextAccess field.

I think you are asking for any date in the current month? To do that as a
date comparsion, try something like this:
OR tblClient.NextAccess Between DateSerial(Year(Date()), Month(Date()),
1)
And DateSerial(Year(Date()), Month(Date())+1,0)

You could also use:
Month(tblClient.NextAccess) = Month(Date())
AND Year(tblClient.NextAccess) = Year(Date())
This numeric comparison should be reliable, but is still inefficient (cannot
use the index.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here is the SQL query that is used.

SELECT StrConv([LName] & ", " & [FName] & IIf(IsNull([MI]),""," " &
[MI] &
"."),3) AS Client, tblClient.SSN, tblClient.DOB,
IIf(IsNull([DOB]),"Unknown",CalcAgeString([DOB])) AS Age,
StrConv(([CMFName]
& " " & [CMLName]),3) AS CM, tblCSCs_LU.CMLName, tblClient.NextAssess,
IIf([NextAssess]<Date(),"OVERDUE","") AS OverDue
FROM tblCSCs_LU INNER JOIN tblClient ON tblCSCs_LU.CaseMgrID =
tblClient.CSCID
WHERE (((tblClient.NextAssess)<=Date() Or (tblClient.NextAssess) Like
DatePart("m",Date()) & "/*/" & DatePart("yyyy",Date())) AND
((tblClient.StatusID)=1) AND
((tblClient.CSCID)=[Forms]![frmReports]![cboCSC]))
ORDER BY tblCSCs_LU.CMLName;

What is happening is in Access 2000 the result shows 7 records; in Access
2003 it shows 5 records. Not sure why the 2 are dropped. Not sure if
the
DatePart is the issue. HELP


I have an unusual problem. I have a DB that is split. It was created
originally in Access 2000. We are now a mixed house of 2000 and 2003.
The BE is on a server; wheras the MDB/MDE is on the users PC.

Here is the problem.

I have a report that runs that selects if a date is over due. In

2000
it

shows 7 records, in 2003 it shows 5 records. Now here is the funny part.
I have a test machine with XP OS and Access 2003 on it. It shows all 7
records. whereas I have a Technician build 50 others that is

similar.
The

report runs good on mine (7 records).

I am trying to determine where in his setup that is different then mind.
It's blowing my mind.

I can take the same MDB and place on my 2000 system, my 2003 system
and someone elses 2003 system. In the first 2 I get 7 records; in someone
elses I get 5 records.