Re: problem with 2003 reports not matching
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Wed, 13 Jul 2005 10:20:33 +0800
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@xxxxxxxxxxxx> wrote in message
news:elRG6WxhFHA.1412@xxxxxxxxxxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxx> wrote in message
> news:uM9Q%23ilhFHA.2424@xxxxxxxxxxxxxxxxxxxxxxx
>> 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.
.
- Follow-Ups:
- Re: problem with 2003 reports not matching
- From: Chris
- Re: problem with 2003 reports not matching
- References:
- problem with 2003 reports not matching
- From: Chris Cowa
- Re: problem with 2003 reports not matching
- From: Chris
- problem with 2003 reports not matching
- Prev by Date: Re: conversion massage at open
- Next by Date: Re: conversion massage at open
- Previous by thread: Re: problem with 2003 reports not matching
- Next by thread: Re: problem with 2003 reports not matching
- Index(es):
Relevant Pages
|
