|
|
|
date: Mon, 25 Aug 2008 23:47:16 +0300,
group: microsoft.public.access.queries
back
Re: Date query problem
I understand that you want me to reverse the query,
I did that and nothing changed, same results ...
Thank's anyway.
wrote in message
news:f1ad3163-7155-4ad9-886c-6cc42378fc68@2g2000hsn.googlegroups.com...
> On Aug 25, 3:47 pm, "Yosi" wrote:
>> Hi,
>> I have access database with date column and number of rows with same date
>> but with different time,
>> I'm trying to view names from database that has the month of today date,
>> My query is:
>>
>> SELECT Table1.*
>> FROM Table1
>> WHERE (((Table1.name)='myname') AND
>> ((Year(Date()))=Year([Table1].[Date1]))
>> AND ((Month(Date()))=Month([Table1].[Date1])));
>>
>> But i got only the rows that its date appears only one time,
>> For example, this is my table:
>>
>> | name | date |
>> time |
>> Tony 05/07/08 12:10:00
>> Mike 10/08/08 13:15:00
>> Tony 10/08/08 12:10:00
>> Alice 11/08/08 22:23:00
>> Jack 11/08/08 12:10:00
>> Edi 13/08/08 16:45:00
>> Mike 10/08/08 19:15:00
>> Tony 17/08/08 12:35:00
>>
>> If the date is 25/08/08 and run the query above i got this results:
>>
>> | name | date |
>> time |
>> Edi 13/08/08 16:45:00
>> Tony 17/08/08 12:35:00
>>
>> Any idea why ?
>>
>> Thanks,
>> Yosi.
>
> SELECT...
> FROM...
> WHERE Month([YourDateField])=Month(Date())
date: Tue, 26 Aug 2008 01:09:22 +0300
author: Yosi
Re: Date query problem
My mistake,
The original query doesn't has the criteria of name,
But the problem remain, you can see for your self,
You can build this database and add this query and you will get only two
results.
"KARL DEWEY" wrote in message
news:E2C15F6D-D903-4547-BB61-A3D40921E0A3@microsoft.com...
> It is hard to believe you are getting those two records when you have
> criteria for Table1.name='myname'.
>
>
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "Yosi" wrote:
>
>> Hi,
>> I have access database with date column and number of rows with same date
>> but with different time,
>> I'm trying to view names from database that has the month of today date,
>> My query is:
>>
>> SELECT Table1.*
>> FROM Table1
>> WHERE (((Table1.name)='myname') AND
>> ((Year(Date()))=Year([Table1].[Date1]))
>> AND ((Month(Date()))=Month([Table1].[Date1])));
>>
>> But i got only the rows that its date appears only one time,
>> For example, this is my table:
>>
>> | name | date |
>> time |
>> Tony 05/07/08
>> 12:10:00
>> Mike 10/08/08
>> 13:15:00
>> Tony 10/08/08
>> 12:10:00
>> Alice 11/08/08
>> 22:23:00
>> Jack 11/08/08
>> 12:10:00
>> Edi 13/08/08
>> 16:45:00
>> Mike 10/08/08
>> 19:15:00
>> Tony 17/08/08
>> 12:35:00
>>
>> If the date is 25/08/08 and run the query above i got this results:
>>
>> | name | date |
>> time |
>> Edi 13/08/08
>> 16:45:00
>> Tony 17/08/08
>> 12:35:00
>>
>> Any idea why ?
>>
>> Thanks,
>> Yosi.
>>
>>
>>
>>
date: Tue, 26 Aug 2008 01:10:45 +0300
author: Yosi
Re: Date query problem
One more thing,
I removed the option: "(Table1.name)='myname'"
But it doesn't help.
"Yosi" wrote in message
news:OK3RF9vBJHA.4200@TK2MSFTNGP06.phx.gbl...
>I understand that you want me to reverse the query,
> I did that and nothing changed, same results ...
> Thank's anyway.
>
> wrote in message
> news:f1ad3163-7155-4ad9-886c-6cc42378fc68@2g2000hsn.googlegroups.com...
>> On Aug 25, 3:47 pm, "Yosi" wrote:
>>> Hi,
>>> I have access database with date column and number of rows with same
>>> date
>>> but with different time,
>>> I'm trying to view names from database that has the month of today date,
>>> My query is:
>>>
>>> SELECT Table1.*
>>> FROM Table1
>>> WHERE (((Table1.name)='myname') AND
>>> ((Year(Date()))=Year([Table1].[Date1]))
>>> AND ((Month(Date()))=Month([Table1].[Date1])));
>>>
>>> But i got only the rows that its date appears only one time,
>>> For example, this is my table:
>>>
>>> | name | date |
>>> time |
>>> Tony 05/07/08 12:10:00
>>> Mike 10/08/08 13:15:00
>>> Tony 10/08/08 12:10:00
>>> Alice 11/08/08 22:23:00
>>> Jack 11/08/08 12:10:00
>>> Edi 13/08/08 16:45:00
>>> Mike 10/08/08 19:15:00
>>> Tony 17/08/08 12:35:00
>>>
>>> If the date is 25/08/08 and run the query above i got this results:
>>>
>>> | name | date |
>>> time |
>>> Edi 13/08/08 16:45:00
>>> Tony 17/08/08 12:35:00
>>>
>>> Any idea why ?
>>>
>>> Thanks,
>>> Yosi.
>>
>> SELECT...
>> FROM...
>> WHERE Month([YourDateField])=Month(Date())
>
>
date: Tue, 26 Aug 2008 01:35:17 +0300
author: Yosi
Re: Date query problem
Try running this query to see what you get --
SELECT Table1, Year([Table1].[Date1]) AS CkYear, Month([Table1].[Date1]) As
CkMonth
FROM Table1;
--
KARL DEWEY
Build a little - Test a little
"Yosi" wrote:
> One more thing,
> I removed the option: "(Table1.name)='myname'"
> But it doesn't help.
>
> "Yosi" wrote in message
> news:OK3RF9vBJHA.4200@TK2MSFTNGP06.phx.gbl...
> >I understand that you want me to reverse the query,
> > I did that and nothing changed, same results ...
> > Thank's anyway.
> >
> > wrote in message
> > news:f1ad3163-7155-4ad9-886c-6cc42378fc68@2g2000hsn.googlegroups.com...
> >> On Aug 25, 3:47 pm, "Yosi" wrote:
> >>> Hi,
> >>> I have access database with date column and number of rows with same
> >>> date
> >>> but with different time,
> >>> I'm trying to view names from database that has the month of today date,
> >>> My query is:
> >>>
> >>> SELECT Table1.*
> >>> FROM Table1
> >>> WHERE (((Table1.name)='myname') AND
> >>> ((Year(Date()))=Year([Table1].[Date1]))
> >>> AND ((Month(Date()))=Month([Table1].[Date1])));
> >>>
> >>> But i got only the rows that its date appears only one time,
> >>> For example, this is my table:
> >>>
> >>> | name | date |
> >>> time |
> >>> Tony 05/07/08 12:10:00
> >>> Mike 10/08/08 13:15:00
> >>> Tony 10/08/08 12:10:00
> >>> Alice 11/08/08 22:23:00
> >>> Jack 11/08/08 12:10:00
> >>> Edi 13/08/08 16:45:00
> >>> Mike 10/08/08 19:15:00
> >>> Tony 17/08/08 12:35:00
> >>>
> >>> If the date is 25/08/08 and run the query above i got this results:
> >>>
> >>> | name | date |
> >>> time |
> >>> Edi 13/08/08 16:45:00
> >>> Tony 17/08/08 12:35:00
> >>>
> >>> Any idea why ?
> >>>
> >>> Thanks,
> >>> Yosi.
> >>
> >> SELECT...
> >> FROM...
> >> WHERE Month([YourDateField])=Month(Date())
> >
> >
>
>
>
date: Mon, 25 Aug 2008 16:30:11 -0700
author: KARL DEWEY
Re: Date query problem
Thanks,
Your answer very help me to find the problem,
This was the results: ( doesn't show irrelevant columns )
CkYear
CkMonth
date1
time1
2008
3
3/8/2008
3:05:44 AM
2008
3
3/8/2008
3:05:44 AM
2008
3
3/8/2008
3:05:44 AM
2008
3
3/8/2008
3:05:44 AM
2008
3
3/8/2008
3:05:44 AM
2008
3
3/8/2008
3:05:44 AM
2008
3
3/8/2008
3:05:44 AM
2008
3
3/8/2008
3:05:44 AM
2008
8
8/13/2008
3:08:44 AM
2008
3
3/8/2008
3:05:44 AM
2008
5
5/8/2008
12:54:21 AM
2008
5
5/8/2008
9:48:48 PM
2008
7
7/31/2008
4:12:48 PM
2008
3
3/8/2008
3:05:44 AM
2008
3
3/8/2008
3:05:44 AM
2008
12
12/8/2008
5:04:46 PM
2008
12
12/8/2008
10:21:21 PM
2008
8
8/15/2008
2:23:22 PM
2008
8
8/21/2008
10:09:38 PM
Looks like sometimes it find the day as month,
So I add the date from access and not from ASP code ( that is was the
original value came from )
and the problem solved,
Many Thanks,
Yosi.
"KARL DEWEY" wrote in message
news:76C9DCEF-D3B1-468E-A3AD-FAD3B08CFC4F@microsoft.com...
> Try running this query to see what you get --
> SELECT Table1, Year([Table1].[Date1]) AS CkYear, Month([Table1].[Date1])
> As
> CkMonth
> FROM Table1;
>
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "Yosi" wrote:
>
>> One more thing,
>> I removed the option: "(Table1.name)='myname'"
>> But it doesn't help.
>>
>> "Yosi" wrote in message
>> news:OK3RF9vBJHA.4200@TK2MSFTNGP06.phx.gbl...
>> >I understand that you want me to reverse the query,
>> > I did that and nothing changed, same results ...
>> > Thank's anyway.
>> >
>> > wrote in message
>> > news:f1ad3163-7155-4ad9-886c-6cc42378fc68@2g2000hsn.googlegroups.com...
>> >> On Aug 25, 3:47 pm, "Yosi" wrote:
>> >>> Hi,
>> >>> I have access database with date column and number of rows with same
>> >>> date
>> >>> but with different time,
>> >>> I'm trying to view names from database that has the month of today
>> >>> date,
>> >>> My query is:
>> >>>
>> >>> SELECT Table1.*
>> >>> FROM Table1
>> >>> WHERE (((Table1.name)='myname') AND
>> >>> ((Year(Date()))=Year([Table1].[Date1]))
>> >>> AND ((Month(Date()))=Month([Table1].[Date1])));
>> >>>
>> >>> But i got only the rows that its date appears only one time,
>> >>> For example, this is my table:
>> >>>
>> >>> | name | date |
>> >>> time |
>> >>> Tony 05/07/08 12:10:00
>> >>> Mike 10/08/08 13:15:00
>> >>> Tony 10/08/08 12:10:00
>> >>> Alice 11/08/08 22:23:00
>> >>> Jack 11/08/08 12:10:00
>> >>> Edi 13/08/08 16:45:00
>> >>> Mike 10/08/08 19:15:00
>> >>> Tony 17/08/08 12:35:00
>> >>>
>> >>> If the date is 25/08/08 and run the query above i got this results:
>> >>>
>> >>> | name | date |
>> >>> time |
>> >>> Edi 13/08/08 16:45:00
>> >>> Tony 17/08/08 12:35:00
>> >>>
>> >>> Any idea why ?
>> >>>
>> >>> Thanks,
>> >>> Yosi.
>> >>
>> >> SELECT...
>> >> FROM...
>> >> WHERE Month([YourDateField])=Month(Date())
>> >
>> >
>>
>>
>>
date: Tue, 26 Aug 2008 03:16:47 +0300
author: Yosi
|
|
