MS Access data conversion - problem with 2003 reports not matching

Author

problem with 2003 reports not matching

Chris Cowa

2005-07-11, 8:24 pm

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

2005-07-11, 8:24 pm

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

"Chris Cowa" <chris.cowan@waynegov.com> wrote in message
news:uM9Q%23ilhFHA.2424@TK2MSFTNGP09.phx.gbl...
> 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
>
>

Allen Browne

2005-07-12, 3:24 am

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/defaul...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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chris Cowa" <chris.cowan@waynegov.com> wrote in message
news:uM9Q%23ilhFHA.2424@TK2MSFTNGP09.phx.gbl...
>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

2005-07-12, 3:24 am

> 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

2005-07-12, 8:24 pm

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

"Chris Cowa" <chris.cowan@waynegov.com> wrote in message
news:uM9Q%23ilhFHA.2424@TK2MSFTNGP09.phx.gbl...
> 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
>
>

Allen Browne

2005-07-13, 3:24 am

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chris" <chris.cowan@waynegov.com> wrote in message
news:elRG6WxhFHA.1412@TK2MSFTNGP09.phx.gbl...[color=darkred]
> 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
>
>
> "Chris Cowa" <chris.cowan@waynegov.com> wrote in message
> news:uM9Q%23ilhFHA.2424@TK2MSFTNGP09.phx.gbl...

Chris

2005-07-13, 8:25 pm

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)=DateSeri
al(Year(Date()),Mont
h(Date()),1) And
(tblClient. NextAssess)=DateSeri
al(Year(Date()),Mont
h(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?

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:eNMV1F1hFHA.2444@tk2msftngp13.phx.gbl...
> 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()),[color
=darkred]
> 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[/color]
(cannot
> use the index.)
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Chris" <chris.cowan@waynegov.com> wrote in message
> news:elRG6WxhFHA.1412@TK2MSFTNGP09.phx.gbl...
&[color=darkred]
Access[color=darkred
]
it[color=darkred]
part.[color=darkred]
The[color=darkred]
mind.[color=darkred]
someone[color=darkre
d]
>
>

Allen Browne

2005-07-13, 8:25 pm

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Chris" <chris.cowan@waynegov.com> wrote in message
news:eyl8UK%23hFHA.1048@tk2msftngp13.phx.gbl...
> 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)=DateSeri
al(Year(Date()),Mont
h(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?
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:eNMV1F1hFHA.2444@tk2msftngp13.phx.gbl...
> string
> etc),
> index
> Month(Date()),
> (cannot
> &
> Access
> it
> part.
> The
> mind.
> someone
>
>

Chris

2005-07-15, 9:24 am

Thanks it works greta now.
"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:%23KeeSeAiFHA.3568@tk2msftngp13.phx.gbl...
> Looks like you used = where I suggested the Between operator.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Chris" <chris.cowan@waynegov.com> wrote in message
> news:eyl8UK%23hFHA.1048@tk2msftngp13.phx.gbl...
that[color=darkred]
&[color=darkred]
here[color=darkred]
is[color=darkred]
a[color=darkred]
tblClient. NextAssess,[color=da
rkred]
2003.[color=darkred]
2000[color=darkred]
7[color=darkred]
similar.[color=darkred]
>
>

Sponsored Links