Select date query

  • Thread starter Thread starter Abay
  • Start date Start date
A

Abay

I have problems using dates as parameters in select queries. I changed the
date format from mmddyyyy to ddmmyyyy. Initially my parameter query where I
select data for a particular month (format mmddyyyy) was:

SELECT Qtech_hours_2.Wo_no, Qtech_hours_2.Notes, Qtech_hours_2.Cname,
Qtech_hours_2.Tech_hrs, Qtech_hours_2.Wo_return_date,
Qtech_hours_2.Tech_name, Qtech_hours_2.Job_date
FROM Qtech_hours_2
WHERE (((Qtech_hours_2.Job_date) Like [Enter Month] & "*"));

Now with the format changed to ddmmyyyy I am having a problem. I am trying
to select the month of November but get transactions dated 11/10/2005 (from
October) included with the following sql code:

SELECT Qtech_hours_2.Wo_no, Qtech_hours_2.Notes, Qtech_hours_2.Cname,
Qtech_hours_2.Tech_hrs, Qtech_hours_2.Wo_return_date,
Qtech_hours_2.Tech_name, Qtech_hours_2.Job_date
FROM Qtech_hours_2
WHERE (((Qtech_hours_2.Job_date) Like "*" & [Enter Month] & "*"));

Any help would be much appreciated.

Abay
 
There are too many places where this can go wrong, e.g. you have:
- a field which is (presumably) a Date/Time type;
- an undeclared parameter (which could therefore be anything at all);
- the Like operator (which is going to give you a string comparision);
- wildcards so it could pick up numbers anywhere in the string;
- an expectation that the formatting of the field affects the contents
(which is not the case).

To fix all that, we need to ensure Access is matching the data types
correctly, that the paremter is correct, and the comparsion is applied on
the right part of the field. The suggestion below expects the parameter to
be entered as a month number (i.e. 1 to 12), and then compares that to the
month part of the date. Note that this will retrieve records that match that
month, regardless of year.

1. In a fresh column in the Field row in query design, enter:
Month([Job_Date])
In the Criteria row under this field, enter:
[Enter Month Number]

2. Choose Parameters on the Query menu
Access opens a dialog.
Enter:
[Enter Month Number] Integer


For general suggestions on working with dates in Access in a country that
uses the d/m/y date format, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
 
Many thanks for your reply .. I have altered the code as you suggested and
of course it works like a charm. Thank you for the link to your tips on
International Dates in Access, they are very informative.

Also you rightly pointed out that I am extracting on "month" regardless of
year. I would like to have the selection default to the current year & some
suggestions in that area would be much appreciated. Or failing that I would
like to be able to be able to enter the month and year as "mmyy" as an
answer to the parameter prompt, so far I have come up with the following
(sample code) which uses two parameter prompts and requires the full yyyy to
be entered.

SELECT Qtech_hours_1.Wo_no, Qtech_hours_1.Notes, Qtech_hours_1.Cname,
Qtech_hours_1.Tech_hrs, Qtech_hours_1.Wo_return_date,
Qtech_hours_1.Tech_name, Qtech_hours_1.Job_date, Qtech_hours_1.Tech_init,
Qtech_hours_1.Wo_back
FROM Qtech_hours_1
WHERE (((Qtech_hours_1.Wo_no)>"w") AND ((Qtech_hours_1.Tech_init)<>"cn" And
(Qtech_hours_1.Tech_init)<>"3p") AND ((Qtech_hours_1.Wo_back)=True) AND
((Month([Job_date]))=[Enter Month]) AND
((DatePart("yyyy",[Job_date]))=[Enter Year]));


Again many thanks for your help which is much appreciated ...

Abay




Allen Browne said:
There are too many places where this can go wrong, e.g. you have:
- a field which is (presumably) a Date/Time type;
- an undeclared parameter (which could therefore be anything at all);
- the Like operator (which is going to give you a string comparision);
- wildcards so it could pick up numbers anywhere in the string;
- an expectation that the formatting of the field affects the contents
(which is not the case).

To fix all that, we need to ensure Access is matching the data types
correctly, that the paremter is correct, and the comparsion is applied on
the right part of the field. The suggestion below expects the parameter to
be entered as a month number (i.e. 1 to 12), and then compares that to the
month part of the date. Note that this will retrieve records that match
that month, regardless of year.

1. In a fresh column in the Field row in query design, enter:
Month([Job_Date])
In the Criteria row under this field, enter:
[Enter Month Number]

2. Choose Parameters on the Query menu
Access opens a dialog.
Enter:
[Enter Month Number] Integer


For general suggestions on working with dates in Access in a country that
uses the d/m/y date format, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Abay said:
I have problems using dates as parameters in select queries. I changed
the date format from mmddyyyy to ddmmyyyy. Initially my parameter query
where I select data for a particular month (format mmddyyyy) was:

SELECT Qtech_hours_2.Wo_no, Qtech_hours_2.Notes, Qtech_hours_2.Cname,
Qtech_hours_2.Tech_hrs, Qtech_hours_2.Wo_return_date,
Qtech_hours_2.Tech_name, Qtech_hours_2.Job_date
FROM Qtech_hours_2
WHERE (((Qtech_hours_2.Job_date) Like [Enter Month] & "*"));

Now with the format changed to ddmmyyyy I am having a problem. I am
trying to select the month of November but get transactions dated
11/10/2005 (from October) included with the following sql code:

SELECT Qtech_hours_2.Wo_no, Qtech_hours_2.Notes, Qtech_hours_2.Cname,
Qtech_hours_2.Tech_hrs, Qtech_hours_2.Wo_return_date,
Qtech_hours_2.Tech_name, Qtech_hours_2.Job_date
FROM Qtech_hours_2
WHERE (((Qtech_hours_2.Job_date) Like "*" & [Enter Month] & "*"));

Any help would be much appreciated.

Abay
Click to expand...
Click to expand...
 
I would probably do something like the following, if I were going to do this
with a parameter query.

SELECT Qtech_hours_1.Wo_no, Qtech_hours_1.Notes, Qtech_hours_1.Cname,
Qtech_hours_1.Tech_hrs, Qtech_hours_1.Wo_return_date,
Qtech_hours_1.Tech_name, Qtech_hours_1.Job_date, Qtech_hours_1.Tech_init,
Qtech_hours_1.Wo_back
FROM Qtech_hours_1
WHERE Qtech_hours_1.Wo_no>"w" AND Qtech_hours_1.Tech_init<>"cn" And
Qtech_hours_1.Tech_ini<>"3p" AND Qtech_hours_1.Wo_back=True AND
Job_Date Between DateSerial([Enter Year],[Enter Month],1) and DateSerial([Enter
Year], [Enter Month]+1,0)

With date serial you can enter two-digit years (or 4-digit) and the function
will guess at the century (>=30 = 20th century; <30 21st Century). The actual
year number is settable as a system parameter, but the default is 30.

Many thanks for your reply .. I have altered the code as you suggested and
of course it works like a charm. Thank you for the link to your tips on
International Dates in Access, they are very informative.

Also you rightly pointed out that I am extracting on "month" regardless of
year. I would like to have the selection default to the current year & some
suggestions in that area would be much appreciated. Or failing that I would
like to be able to be able to enter the month and year as "mmyy" as an
answer to the parameter prompt, so far I have come up with the following
(sample code) which uses two parameter prompts and requires the full yyyy to
be entered.

SELECT Qtech_hours_1.Wo_no, Qtech_hours_1.Notes, Qtech_hours_1.Cname,
Qtech_hours_1.Tech_hrs, Qtech_hours_1.Wo_return_date,
Qtech_hours_1.Tech_name, Qtech_hours_1.Job_date, Qtech_hours_1.Tech_init,
Qtech_hours_1.Wo_back
FROM Qtech_hours_1
WHERE (((Qtech_hours_1.Wo_no)>"w") AND ((Qtech_hours_1.Tech_init)<>"cn" And
(Qtech_hours_1.Tech_init)<>"3p") AND ((Qtech_hours_1.Wo_back)=True) AND
((Month([Job_date]))=[Enter Month]) AND
((DatePart("yyyy",[Job_date]))=[Enter Year]));

Again many thanks for your help which is much appreciated ...

Abay

Allen Browne said:
There are too many places where this can go wrong, e.g. you have:
- a field which is (presumably) a Date/Time type;
- an undeclared parameter (which could therefore be anything at all);
- the Like operator (which is going to give you a string comparision);
- wildcards so it could pick up numbers anywhere in the string;
- an expectation that the formatting of the field affects the contents
(which is not the case).

To fix all that, we need to ensure Access is matching the data types
correctly, that the paremter is correct, and the comparsion is applied on
the right part of the field. The suggestion below expects the parameter to
be entered as a month number (i.e. 1 to 12), and then compares that to the
month part of the date. Note that this will retrieve records that match
that month, regardless of year.

1. In a fresh column in the Field row in query design, enter:
Month([Job_Date])
In the Criteria row under this field, enter:
[Enter Month Number]

2. Choose Parameters on the Query menu
Access opens a dialog.
Enter:
[Enter Month Number] Integer


For general suggestions on working with dates in Access in a country that
uses the d/m/y date format, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Abay said:
I have problems using dates as parameters in select queries. I changed
the date format from mmddyyyy to ddmmyyyy. Initially my parameter query
where I select data for a particular month (format mmddyyyy) was:

SELECT Qtech_hours_2.Wo_no, Qtech_hours_2.Notes, Qtech_hours_2.Cname,
Qtech_hours_2.Tech_hrs, Qtech_hours_2.Wo_return_date,
Qtech_hours_2.Tech_name, Qtech_hours_2.Job_date
FROM Qtech_hours_2
WHERE (((Qtech_hours_2.Job_date) Like [Enter Month] & "*"));

Now with the format changed to ddmmyyyy I am having a problem. I am
trying to select the month of November but get transactions dated
11/10/2005 (from October) included with the following sql code:

SELECT Qtech_hours_2.Wo_no, Qtech_hours_2.Notes, Qtech_hours_2.Cname,
Qtech_hours_2.Tech_hrs, Qtech_hours_2.Wo_return_date,
Qtech_hours_2.Tech_name, Qtech_hours_2.Job_date
FROM Qtech_hours_2
WHERE (((Qtech_hours_2.Job_date) Like "*" & [Enter Month] & "*"));

Any help would be much appreciated.

Abay
Click to expand...
Click to expand...
Click to expand...
 
Abay, John's answer is definately the way to go.

If you have an index on the Job_Date field, JET will be able to use that
index to find dates between the 2 literal values generated by DateSerial(),
so that is much more efficient than the Month() function.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

John Spencer said:
I would probably do something like the following, if I were going to do
this
with a parameter query.

SELECT Qtech_hours_1.Wo_no, Qtech_hours_1.Notes, Qtech_hours_1.Cname,
Qtech_hours_1.Tech_hrs, Qtech_hours_1.Wo_return_date,
Qtech_hours_1.Tech_name, Qtech_hours_1.Job_date, Qtech_hours_1.Tech_init,
Qtech_hours_1.Wo_back
FROM Qtech_hours_1
WHERE Qtech_hours_1.Wo_no>"w" AND Qtech_hours_1.Tech_init<>"cn" And
Qtech_hours_1.Tech_ini<>"3p" AND Qtech_hours_1.Wo_back=True AND
Job_Date Between DateSerial([Enter Year],[Enter Month],1) and
DateSerial([Enter
Year], [Enter Month]+1,0)

With date serial you can enter two-digit years (or 4-digit) and the
function
will guess at the century (>=30 = 20th century; <30 21st Century). The
actual
year number is settable as a system parameter, but the default is 30.

Many thanks for your reply .. I have altered the code as you suggested
and
of course it works like a charm. Thank you for the link to your tips on
International Dates in Access, they are very informative.

Also you rightly pointed out that I am extracting on "month" regardless
of
year. I would like to have the selection default to the current year &
some
suggestions in that area would be much appreciated. Or failing that I
would
like to be able to be able to enter the month and year as "mmyy" as an
answer to the parameter prompt, so far I have come up with the following
(sample code) which uses two parameter prompts and requires the full yyyy
to
be entered.

SELECT Qtech_hours_1.Wo_no, Qtech_hours_1.Notes, Qtech_hours_1.Cname,
Qtech_hours_1.Tech_hrs, Qtech_hours_1.Wo_return_date,
Qtech_hours_1.Tech_name, Qtech_hours_1.Job_date, Qtech_hours_1.Tech_init,
Qtech_hours_1.Wo_back
FROM Qtech_hours_1
WHERE (((Qtech_hours_1.Wo_no)>"w") AND ((Qtech_hours_1.Tech_init)<>"cn"
And
(Qtech_hours_1.Tech_init)<>"3p") AND ((Qtech_hours_1.Wo_back)=True) AND
((Month([Job_date]))=[Enter Month]) AND
((DatePart("yyyy",[Job_date]))=[Enter Year]));

Again many thanks for your help which is much appreciated ...

Abay

Allen Browne said:
There are too many places where this can go wrong, e.g. you have:
- a field which is (presumably) a Date/Time type;
- an undeclared parameter (which could therefore be anything at all);
- the Like operator (which is going to give you a string comparision);
- wildcards so it could pick up numbers anywhere in the string;
- an expectation that the formatting of the field affects the contents
(which is not the case).

To fix all that, we need to ensure Access is matching the data types
correctly, that the paremter is correct, and the comparsion is applied
on
the right part of the field. The suggestion below expects the parameter
to
be entered as a month number (i.e. 1 to 12), and then compares that to
the
month part of the date. Note that this will retrieve records that match
that month, regardless of year.

1. In a fresh column in the Field row in query design, enter:
Month([Job_Date])
In the Criteria row under this field, enter:
[Enter Month Number]

2. Choose Parameters on the Query menu
Access opens a dialog.
Enter:
[Enter Month Number] Integer


For general suggestions on working with dates in Access in a country
that
uses the d/m/y date format, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

I have problems using dates as parameters in select queries. I changed
the date format from mmddyyyy to ddmmyyyy. Initially my parameter
query
where I select data for a particular month (format mmddyyyy) was:

SELECT Qtech_hours_2.Wo_no, Qtech_hours_2.Notes, Qtech_hours_2.Cname,
Qtech_hours_2.Tech_hrs, Qtech_hours_2.Wo_return_date,
Qtech_hours_2.Tech_name, Qtech_hours_2.Job_date
FROM Qtech_hours_2
WHERE (((Qtech_hours_2.Job_date) Like [Enter Month] & "*"));

Now with the format changed to ddmmyyyy I am having a problem. I am
trying to select the month of November but get transactions dated
11/10/2005 (from October) included with the following sql code:

SELECT Qtech_hours_2.Wo_no, Qtech_hours_2.Notes, Qtech_hours_2.Cname,
Qtech_hours_2.Tech_hrs, Qtech_hours_2.Wo_return_date,
Qtech_hours_2.Tech_name, Qtech_hours_2.Job_date
FROM Qtech_hours_2
WHERE (((Qtech_hours_2.Job_date) Like "*" & [Enter Month] & "*"));

Any help would be much appreciated.

Abay
Click to expand...
Click to expand...
Click to expand...
 
Thank you both again and I will follow your advise, seems like the way to
go.

Abay

Allen Browne said:
Abay, John's answer is definately the way to go.

If you have an index on the Job_Date field, JET will be able to use that
index to find dates between the 2 literal values generated by
DateSerial(), so that is much more efficient than the Month() function.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

John Spencer said:
I would probably do something like the following, if I were going to do
this
with a parameter query.

SELECT Qtech_hours_1.Wo_no, Qtech_hours_1.Notes, Qtech_hours_1.Cname,
Qtech_hours_1.Tech_hrs, Qtech_hours_1.Wo_return_date,
Qtech_hours_1.Tech_name, Qtech_hours_1.Job_date, Qtech_hours_1.Tech_init,
Qtech_hours_1.Wo_back
FROM Qtech_hours_1
WHERE Qtech_hours_1.Wo_no>"w" AND Qtech_hours_1.Tech_init<>"cn" And
Qtech_hours_1.Tech_ini<>"3p" AND Qtech_hours_1.Wo_back=True AND
Job_Date Between DateSerial([Enter Year],[Enter Month],1) and
DateSerial([Enter
Year], [Enter Month]+1,0)

With date serial you can enter two-digit years (or 4-digit) and the
function
will guess at the century (>=30 = 20th century; <30 21st Century). The
actual
year number is settable as a system parameter, but the default is 30.

Many thanks for your reply .. I have altered the code as you suggested
and
of course it works like a charm. Thank you for the link to your tips on
International Dates in Access, they are very informative.

Also you rightly pointed out that I am extracting on "month" regardless
of
year. I would like to have the selection default to the current year &
some
suggestions in that area would be much appreciated. Or failing that I
would
like to be able to be able to enter the month and year as "mmyy" as an
answer to the parameter prompt, so far I have come up with the
following
(sample code) which uses two parameter prompts and requires the full
yyyy to
be entered.

SELECT Qtech_hours_1.Wo_no, Qtech_hours_1.Notes, Qtech_hours_1.Cname,
Qtech_hours_1.Tech_hrs, Qtech_hours_1.Wo_return_date,
Qtech_hours_1.Tech_name, Qtech_hours_1.Job_date,
Qtech_hours_1.Tech_init,
Qtech_hours_1.Wo_back
FROM Qtech_hours_1
WHERE (((Qtech_hours_1.Wo_no)>"w") AND ((Qtech_hours_1.Tech_init)<>"cn"
And
(Qtech_hours_1.Tech_init)<>"3p") AND ((Qtech_hours_1.Wo_back)=True) AND
((Month([Job_date]))=[Enter Month]) AND
((DatePart("yyyy",[Job_date]))=[Enter Year]));

Again many thanks for your help which is much appreciated ...

Abay

There are too many places where this can go wrong, e.g. you have:
- a field which is (presumably) a Date/Time type;
- an undeclared parameter (which could therefore be anything at all);
- the Like operator (which is going to give you a string comparision);
- wildcards so it could pick up numbers anywhere in the string;
- an expectation that the formatting of the field affects the contents
(which is not the case).

To fix all that, we need to ensure Access is matching the data types
correctly, that the paremter is correct, and the comparsion is applied
on
the right part of the field. The suggestion below expects the
parameter to
be entered as a month number (i.e. 1 to 12), and then compares that to
the
month part of the date. Note that this will retrieve records that
match
that month, regardless of year.

1. In a fresh column in the Field row in query design, enter:
Month([Job_Date])
In the Criteria row under this field, enter:
[Enter Month Number]

2. Choose Parameters on the Query menu
Access opens a dialog.
Enter:
[Enter Month Number] Integer


For general suggestions on working with dates in Access in a country
that
uses the d/m/y date format, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

I have problems using dates as parameters in select queries. I
changed
the date format from mmddyyyy to ddmmyyyy. Initially my parameter
query
where I select data for a particular month (format mmddyyyy) was:

SELECT Qtech_hours_2.Wo_no, Qtech_hours_2.Notes, Qtech_hours_2.Cname,
Qtech_hours_2.Tech_hrs, Qtech_hours_2.Wo_return_date,
Qtech_hours_2.Tech_name, Qtech_hours_2.Job_date
FROM Qtech_hours_2
WHERE (((Qtech_hours_2.Job_date) Like [Enter Month] & "*"));

Now with the format changed to ddmmyyyy I am having a problem. I am
trying to select the month of November but get transactions dated
11/10/2005 (from October) included with the following sql code:

SELECT Qtech_hours_2.Wo_no, Qtech_hours_2.Notes, Qtech_hours_2.Cname,
Qtech_hours_2.Tech_hrs, Qtech_hours_2.Wo_return_date,
Qtech_hours_2.Tech_name, Qtech_hours_2.Job_date
FROM Qtech_hours_2
WHERE (((Qtech_hours_2.Job_date) Like "*" & [Enter Month] & "*"));

Any help would be much appreciated.

Abay
Click to expand...
Click to expand...
Click to expand...
 
Back
Top