Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / November 2005

Tip: Looking for answers? Try searching our database.

Select date query

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Abay - 05 Nov 2005 07:38 GMT
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
Allen Browne - 05 Nov 2005 08:16 GMT
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

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have problems using dates as parameters in select queries.  I changed the
>date format from mmddyyyy to ddmmyyyy.  Initially my parameter query where
[quoted text clipped - 19 lines]
>
> Abay
Abay - 05 Nov 2005 21:08 GMT
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;
[quoted text clipped - 50 lines]
>>
>> Abay
John Spencer - 05 Nov 2005 23:19 GMT
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
[quoted text clipped - 81 lines]
> >>
> >> Abay
Allen Browne - 06 Nov 2005 03:02 GMT
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.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I would probably do something like the following, if I were going to do
> this
[quoted text clipped - 108 lines]
>> >>
>> >> Abay
Abay - 06 Nov 2005 07:33 GMT
Thank you both again and I will follow your advise, seems like the way to
go.

Abay

> Abay, John's answer is definately the way to go.
>
[quoted text clipped - 118 lines]
>>> >>
>>> >> Abay
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.