Re: problem with 2003 reports not matching

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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?

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:eNMV1F1hFHA.2444@xxxxxxxxxxxxxxxxxxxxxxx
> 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.
>
>


.

Relevant Pages

  • Re: problem with 2003 reports not matching
    ... Assuming NextAssess is a Date/Time type field, ... The Like operator performs a *string* comparision. ... >> I am trying to determine where in his setup that is different then mind. ...
    (microsoft.public.access.conversion)
  • Phone Numbers
    ... String, mid, etc, but I was hoping there was an easier ... Please keep in mind the table we are using is read- ... so it must be changed in a query or report. ...
    (microsoft.public.access.gettingstarted)
  • Re: Comparing char * with string literal
    ... Here "string" is an array of characters. ... So shouldn't the compiler ... The only way for the comparision to be true is if `a` points ...
    (comp.lang.c)
  • Re: How ">=" Work on Varchar field?
    ... I think that is how SQL Server should look ... Will the same concept will be applied even on the non-binary comparision? ... > compares the ASCII code of the first character of one string to the ASCII ...
    (microsoft.public.sqlserver.programming)
  • Re: Compare Two Strings
    ... Even as it's not very clear what you want the comparision will fail, ... word document is a binary file, whether html is a text file, beside the ... and the second string a html file is there.now i want to compare to ...
    (microsoft.public.dotnet.languages.csharp)