hai rich and other friends please help me out (1 Viewer)

[ramesh]

hi friends
i did not get the query

assume that there is table called BIRTHDAY which has two fields
empid (datatype as text) and dates (data type as date/time)

there are two records in that like as follows

2000001 12/05/1948
2000444 28/05/1984
2000448 01/05/1976

now given two dates as 1/5/2002 and 31/5/2002

i need to write a query where i should get the above records.
note : both dates inclusive

can anyone write a query ... as it very important for me.

 

[raskew]

If your intent is to return all records in a particular month, regardless of year (e.g. 'Who has a birthday in May?') you could try the following: (I used Northwind's Orders table for the example). In response to the prompt, enter an integer (1 - 12))

SELECT Orders.OrderID, Orders.OrderDate, Month([OrderDate]) AS MyMonth
FROM Orders
WHERE (((Month([OrderDate]))=[Enter Month]));

 

[ramesh]

sorry not that ... i dont wnated for the single month i will give a date as 4/5/2002 to 26/7/2002 also
what i mean is any two given date ...they may be in the same month or not/

ramesh

 

[raskew]

OK-

You could try this. To avoid confusion over the date entry formats, I formatted [MyOrderDate] as Medium Date, something everyone should be able to understand. (On the query grid, highlight the calculated field, right-click and select Properties. Enter Medium Date in the format box.) So, using your example, you'd enter 4-May-02 at the Start Date prompt and 26-Jul-02 for the End Date.

PARAMETERS [Enter start date] DateTime, [Enter end date] DateTime;
SELECT Orders.OrderID, Orders.OrderDate, DateSerial(Year(Now()),Month([OrderDate]),Day([OrderDate])) AS MyOrderDate
FROM Orders
WHERE (((DateSerial(Year(Now()),Month([OrderDate]),Day([OrderDate]))) Between [Enter start date] And [Enter end date]))
ORDER BY DateSerial(Year(Now()),Month([OrderDate]),Day([OrderDate]));


P.S. - Entering the same question multiple times on this site does nothing but trash the site! Stick with one thread per question.

 

[ramesh]

hai raskew

your answer is not suiting me... though appreciate for ur effort..

and i also wanted to thank you for reminding me for sending this messages more than once..acutally i need an answer to it... as my project is pending with that...

if you can create a table of like that and try to execute the query ... it would be help ful to me.

expecting an early reply from u


ramesh.s

 

[raskew]

We all need answers from time to time, but spamming the site with multiple copies of the same question isn't the way to get them. (Just noticed you're now hitting the General topics site.)

It's not necessary to build a table 'just like yours' to test the concept. All that's needed is a table with a populated date field, e.g., Northwind's Orders table.

You haven't specified why the response isn't suiting you so it's difficult to offer any advice. A question, however: You did test the response in Northwind, didn't you? What was it that didn't work for you?

 

[raskew]

You just don't get it, do you?

As I write this, I see on this site:

2 postings in the General forum
1 posting in the Tables forum
5 postings in the Queries forum
2 postings in the Forum forum
1 posting in the Macros forum

That, Ramesh, is a total of 11 postings and,
THEY ALL SAY THE SAME THING!!

Let's look at this:
(1) I provided you a tested solution to your original scenario.
(2) You changed the scenario
(3) I provided you a tested solution to your revised scenario.
(4) You responded that 'your answer is not suiting me', with no further explanation
(5) At lease three veteran responders pointed out to you that junk mailings would not help you to get the 'urgent' response you are looking for (and apparently getting paid to provide).
(6) And still you persist.

Ramesh, what's the problem?

 

[raskew]

Fizzio -

You are my hero!

I left this alone for an entire day, fully expecting to come to find some "politically correct 'bleeding heart'" chastising me for being so hard on poor, pathetic, confused (but it's not his fault)Ramesh.

Instead, we find that you've covered all of the bases!

Thanks again!

Bob

P.S. Ramesh, we're praying that your physician will soon get your dosage adjusted and everything will then be back on an even keel.

 

[ramesh]

hai

raskew is good and replies me promptly and tries to make me understand and BOB is a fool and an idiot. i dont care about you bob anymore

hai raskew,

i am sorry for doing that... i dont have the northwind mdb file and my problem is not solved. please let me know that if you have checked this using the northwind mdb file.

ramesh.s

 

[ramesh]

hai raskew,

one day u asked me the query u gave y is not working.
from the order table of the northwind database.

because there are u will have the date time field but in my case year is not to be considered.
please help me if u can or please leave me alone
.

ramesh.s

 

[raskew]

I give up! You win!

Best of luck in all your future endeavors.

Bob

 

[ramesh]

hai raskew

i am sorry that i have used some profane words.

please let me know that raskew and bob are the same person..
if they are same.. then i am extremely sorry anyway it was raskew who helped me out.
can u give me u mail id ... so that u could help me out in the problem.
if u are in need i can send u the mdb file to ur mail account and then we can discuss.

i once again apologise for what all i have said.


ramesh.s

 

[raskew]

Ah Ramesh,

Bob and raskew are indeed one in the same. It is far too late to apoligize. I can only wish you 72 virgins to do with as you please (and perhaps you'll tell us what becomes of the unfortunate 72, once you're done with them).

Best wishes,

Bob

 

[ramesh]

do u have a mail account or an id in yahoo chat please send it to me so that i can talk or chat with you.

raemsh.s

 

[aziz rasul]

Ramesh please stop your antics. This forum is for sensible people who have genuine problems AND go about it the right way AND are grateful to those people who help out FREE of charge. If you can't conduct yourself in like manner in the future, please leave this and other forums alone.



[This message has been edited by aziz rasul (edited 05-23-2002).]

 

[The_Doc_Man]

Ramesh,

Let me add that some of what you are seeing is frustration because when we look at your data and your query, we can't easily understand WHY those particular records should be returned. Is it because thay all have the same month? If so, then just state that in your question. Remember that the less you tell us, the more likely you are to get a guess that is off the mark. Don't get surly because we cannot understand your problem. Communication is a TWO-way street.

The answer you are looking for (assuming I have guessed correctly about the month) is the DatePart function (with a first parameter of 'm' I think) in your query.

Something like SELECT * FROM mytable WHERE DatePart('m',[mydate]) = DatePart('m', [otherdate]);

Now how you go about figuring out the way to get the right dates together is up to you. But I'll bet that DatePart will be productive in this situation.

 

[ramesh]

no doubt the datepart is productive

sir there is a table which has one field and it contains some employees birthdays..


say 19/05/1948
21/05/1948
14/06/1974

in my office the hr dept will cut birthday cakes for each month.now they have changed the policy that if many birthdays are coming frequently... they will choose two dates such as say 10/05/2002 to 31/05/2002 and find out who are the persons who's birthdays fall in these days... for example take the dates i have given u (19/05/1948 and 21/05/1948) these two dates of birth of employees fall between (10/5/2002 to 31/05/2002) .... some of the members in access group say that... they dont come.. a birth day is celebrated every year .... we take in to consideration only the month and the date . here also like that only it works.

now i should write a query that given two days i should get a list of birthdates in between them and including both the dates.

I hope this time i have explained very clearly
any more clarifications will be surely welcomed.

ramesh srinivasan..

 

[antomack]

Maybe this will solve your problem, build a query with the following SQL, substitute 'a' with your table name, 'Agent_no' with your employee id field and 'Appt_date' with your birthday field.

SELECT a.Agent_no, a.Appt_Date, Day([appt_date]) AS BDay, Month([appt_date]) AS BMonth FROM a WHERE (((Day([appt_date])) Between Day([Enter Start Date?]) And Day([Enter End Date?])) AND ((Month([appt_date])) Between Month([Enter Start Date?]) And Month([Enter End Date?])));

The solution that raskew provided would also do the same thing in that he converts the birthday to the current years equivalent and finds the dates that fall between the two dates entered.

 

[ramesh]

what u have sent is working for.... if both the startdate and endate is in the same month

if i give the dates as 21/4/2002 and 25/5/2002.... it is giving me the list of birthdays in the days 21,22,23,24,25 only...
though i have birthdays on 17,30,4,9 etc

if possible try out
ramesh.s