Date query problem

  • Thread starter Thread starter Yosi
  • Start date Start date
Y

Yosi

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.
 
It is hard to believe you are getting those two records when you have
criteria for Table1.name='myname'.
 
I understand that you want me to reverse the query,
I did that and nothing changed, same results ...
Thank's anyway.

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.
Click to expand...

SELECT...
FROM...
WHERE Month([YourDateField])=Month(Date())
Click to expand...
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
One more thing,
I removed the option: "(Table1.name)='myname'"
But it doesn't help.

Yosi said:
I understand that you want me to reverse the query,
I did that and nothing changed, same results ...
Thank's anyway.

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.
Click to expand...

SELECT...
FROM...
WHERE Month([YourDateField])=Month(Date())
Click to expand...
Click to expand...
 
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 said:
One more thing,
I removed the option: "(Table1.name)='myname'"
But it doesn't help.

Yosi said:
I understand that you want me to reverse the query,
I did that and nothing changed, same results ...
Thank's anyway.

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())
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
One more thing,
I removed the option: "(Table1.name)='myname'"
But it doesn't help.

Yosi said:
I understand that you want me to reverse the query,
I did that and nothing changed, same results ...
Thank's anyway.

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())
Click to expand...
Click to expand...
Click to expand...
 
Back
Top