By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,984 Members | 2,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,984 IT Pros & Developers. It's quick & easy.

searching date from multiple columns

P: 1
Hello:

I would like to search for a date from a table.

I have a table that contains 3 fileds: JobID, JobTitle, and JobDeadlineDate

Through a query I managed to generate 3 more fields, i.e. 1st reminder = JobDeadlineDate - 30, 2nd Reminder = JobDeadlineDate -15, and 3rd Reminder = JobDeadlineDate - 7

Now I would like to generate a list of jobs, that matches the current date with either JobDeadlineDate, 1st Reminder, 2nd Reminder or 3rd Reminder.

The list fetches a date by matching it with the current date or a range of dates and tell me whether the date is a 1st reminder or a 2nd or 3rd or a Deadline.

Can you tell me how can I make this possible?
Oct 19 '06 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
SELECT JobID, JobTitle, JobDeadlineDate, 1st Reminder, 2nd Reminder, 3rd Reminder FROM Query1
WHERE (JobDeadlineDate=#[Forms]![FormName]![DateField]#)
OR (1st Reminder=#[Forms]![FormName]![DateField]#)
OR (2nd Reminder=#[Forms]![FormName]![DateField]#)
OR (3rd Reminder=#[Forms]![FormName]![DateField]#);

This query is based on a date being entered in a form.
Using date ranges would be fairly messy because you have four fields to look at but if you really need to:

change

WHERE (JobDeadlineDate=#[Forms]![FormName]![DateField]#)

to

WHERE (JobDeadlineDate BETWEEN #[Forms]![FormName]![DateField1]# AND #[Forms]![FormName]![DateField2]#)

etc....



Hello:

I would like to search for a date from a table.

I have a table that contains 3 fileds: JobID, JobTitle, and JobDeadlineDate

Through a query I managed to generate 3 more fields, i.e. 1st reminder = JobDeadlineDate - 30, 2nd Reminder = JobDeadlineDate -15, and 3rd Reminder = JobDeadlineDate - 7

Now I would like to generate a list of jobs, that matches the current date with either JobDeadlineDate, 1st Reminder, 2nd Reminder or 3rd Reminder.

The list fetches a date by matching it with the current date or a range of dates and tell me whether the date is a 1st reminder or a 2nd or 3rd or a Deadline.

Can you tell me how can I make this possible?
Oct 20 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.