Date query problem

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.
 
K

KARL DEWEY

It is hard to believe you are getting those two records when you have
criteria for Table1.name='myname'.
 
Y

Yosi

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...
 
Y

Yosi

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...
 
Y

Yosi

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...
 
K

KARL DEWEY

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...
 
Y

Yosi

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query that returns results from two tables for one employee 2
Query to count time range 4
working with time date in a query 4
Query Help - Access 2003 4
query construct question 7
Use table fields as records 5
Transpose 1
Obtain Month & Year from a Text formatted Date field using Left Fu 2

Top