Re: problem with 2003 reports not matching
- From: "Chris" <chris.cowan@xxxxxxxxxxxx>
- Date: Fri, 15 Jul 2005 10:01:57 -0400
Thanks it works greta now.
"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:%23KeeSeAiFHA.3568@xxxxxxxxxxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxx> wrote in message
> news:eyl8UK%23hFHA.1048@xxxxxxxxxxxxxxxxxxxxxxx
> > 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?
> >
> > "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.
> >>
> >>
> >
> >
>
>
.
- References:
- problem with 2003 reports not matching
- From: Chris Cowa
- Re: problem with 2003 reports not matching
- From: Chris
- Re: problem with 2003 reports not matching
- From: Allen Browne
- Re: problem with 2003 reports not matching
- From: Chris
- Re: problem with 2003 reports not matching
- From: Allen Browne
- problem with 2003 reports not matching
- Prev by Date: Re: Variables being reset after conversion from 97 to 2000
- Next by Date: Access 2000 to Access 2003
- Previous by thread: Re: problem with 2003 reports not matching
- Next by thread: Support for Jet databse engine
- Index(es):
Relevant Pages
|
Loading
