Re: Compare two date into the same table
- From: "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
- Date: Wed, 5 Oct 2005 13:18:24 -0400
Hi,
Can you check the WHERE clause when you insert the condition (to a computed
alias). It should read
.... WHERE .... CDate(Nz([pres_1].[referencedate],#1/1/2005#)) = #1/1/2005#
.....
I suspect it has somehow been written like:
.... WHERE ... [pres_1].[referencedate] = #1/1/2005# ...
which makes your elusive record to not be kept in the result.
Hoping it may help,
Vanderghast, Access MVP
"Alex_Firenze" <alessandro.caforio@xxxxxxxxx> wrote in message
news:1128436232.734954.173300@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Hi Michael,
> I think you are right. It is something related to the fact that in my
> query I didn' take into account Null values in the join. (by the way,
> now I know the NZ function)
>
> However, the query you advice me is not working...
> If I remove the WHERE condition on the second date and I display the NZ
> result:
>
> SELECT
> pres_1.referencedate,
> pres.location,
> pres.class,
> pres.npeople,
> Val(Nz([pres_1].[npeople],0)) AS nz_people,
> CDate(Nz([pres_1].[referencedate],#1/1/2005#)) AS nzdate
> FROM
> pres LEFT JOIN pres AS pres_1 ON (pres.class = pres_1.class) AND
> (pres.location = pres_1.location)
> WHERE (((pres.referencedate)=#2/1/2005#));
>
> I get this result:
>
> referencedate location class npeople nz_people nzdate
> 01/02/2005 london math 90 90 1/2/2005
> 01/01/2005 london math 90 100 1/1/2005
> 01/02/2005 london finance 85 85 1/2/2005
> 01/01/2005 london finance 85 80 1/1/2005
> 01/02/2005 london statistics 40 40 1/2/2005
> 01/01/2005 london statistics 40 50 1/1/2005
> 01/02/2005 brighton statistics 12 12 1/2/2005
> 01/01/2005 brighton statistics 12 15 1/1/2005
> 01/02/2005 brighton math 15 15 1/2/2005
>
> If i insert now the condition nzdate=#01/01/2005# I get the same stuff
> as before... Brighton-Math-15 desappear...
> So, it seems that it is not possible to do it in a "clean
> way"...Probably I should first build a temporary table with the result
> at 01/01/2005 and then run the query with an outer join...
>
> Any idea?
>
.
- References:
- Compare two date into the same table
- From: Alex_Firenze
- Re: Compare two date into the same table
- From: Michel Walsh
- Re: Compare two date into the same table
- From: Alex_Firenze
- Compare two date into the same table
- Prev by Date: Re: Fuzzy query - find almost matching
- Next by Date: Re: Access should retain page layout settings for tables and queries.
- Previous by thread: Re: Compare two date into the same table
- Next by thread: Re: Query very slow with UNION
- Index(es):
Relevant Pages
|
