|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
August 25th, 2008, 09:47 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Date query problem
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. 
|
|
#2
August 25th, 2008, 10:18 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Date query problem
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())
|
|
#3
August 25th, 2008, 10:32 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Date query problem
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.
|
|
#4
August 25th, 2008, 11:09 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
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 ... 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())
|
|
#5
August 25th, 2008, 11:10 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
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 ... 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.
|
|
#6
August 25th, 2008, 11:35 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Date query problem
One more thing,
I removed the option: "(Table1.name)='myname'" But it doesn't help. "Yosi" wrote in message ... I understand that you want me to reverse the query, I did that and nothing changed, same results ... Thank's anyway. wrote in message ... 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())
|
|
#7
August 26th, 2008, 12:30 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
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 ... I understand that you want me to reverse the query, I did that and nothing changed, same results ... Thank's anyway. wrote in message ... 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())
|
|
#8
August 26th, 2008, 01:16 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
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 ... 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 ... I understand that you want me to reverse the query, I did that and nothing changed, same results ... Thank's anyway. wrote in message ... 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())
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
