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

Find first record by date

100+
P: 294
I have a table that holds records of awards by award date.

The awards are connected through an Employee table by EmployeeID as a foreign key.

I am trying to find if any employee received their first award in the time span specified by the user.

I tried something like:

Expand|Select|Wrap|Line Numbers
  1. DCount("AwardDate", "AwardTbl", "AwardDate < [Forms]![PlanFrm]![ReportSelectSbfrm].[Form]![StartDateTxt]") <= 0
to no avail.

If the data looked like this:

1 100100 5 12/31/2010
2 100100 10 2/15/2012

.. and the user entered in "..[between] 9/30/2010 and 3/31/2012" I would expect to only see record # 1. These records would also be excluded if they fell out of the date range.

I know this seems to be a simple logic question, but I cannot seem to figure it out. I also tried Min, First, Select Distinct, etc.

Thanks.
Apr 7 '14 #1

✓ answered by Seth Schrock

If the user literally typed [between] 9/30/2010 and 3/31/2012 in the textbox, then your criteria for your DCount() function would be
Expand|Select|Wrap|Line Numbers
  1. AwardDate < [between] 9/30/2010 and 3/31/2012
There are so many errors in this that I don't know exactly what you are trying to do. If you are wanting the user to type the Between function, then you would need to remove the less than sign in your code. Also, your form reference is inside the double quotes which means that it will never reference the value inside your textbox, but will instead compare a date to the text [Forms]![PlanFrm]!... which doesn't work either.

Also, DCount() can never return a negative value, so there is no reason to compare its result to 0 using the "less than or equal to" comparison. I think that you would really want the "greater than" 1 (the > symbol) to check to see if a reward was found or "=0" if you are wanting to know who didn't get a reward in that time period.

You logic doesn't indicate how you determine that the reward is the person's first reward, so I'm not sure how to help you there. My recommendation would be to have two textboxes for the starting and ending dates if you want to have a selected range or just one textbox if you want everything before a given date or after that date so that all the user has to enter is the dates, not the Between keyword or anything else.

Share this Question
Share on Google+
4 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
If the user literally typed [between] 9/30/2010 and 3/31/2012 in the textbox, then your criteria for your DCount() function would be
Expand|Select|Wrap|Line Numbers
  1. AwardDate < [between] 9/30/2010 and 3/31/2012
There are so many errors in this that I don't know exactly what you are trying to do. If you are wanting the user to type the Between function, then you would need to remove the less than sign in your code. Also, your form reference is inside the double quotes which means that it will never reference the value inside your textbox, but will instead compare a date to the text [Forms]![PlanFrm]!... which doesn't work either.

Also, DCount() can never return a negative value, so there is no reason to compare its result to 0 using the "less than or equal to" comparison. I think that you would really want the "greater than" 1 (the > symbol) to check to see if a reward was found or "=0" if you are wanting to know who didn't get a reward in that time period.

You logic doesn't indicate how you determine that the reward is the person's first reward, so I'm not sure how to help you there. My recommendation would be to have two textboxes for the starting and ending dates if you want to have a selected range or just one textbox if you want everything before a given date or after that date so that all the user has to enter is the dates, not the Between keyword or anything else.
Apr 7 '14 #2

100+
P: 294
Actually, the requirements were changed. I actually have the correct solution.

Thanks for your time, Seth.
Apr 7 '14 #3

Seth Schrock
Expert 2.5K+
P: 2,951
Your second to last paragraph contradicts itself as both statements say before 9/30/2010, but I think that I can interpret what you meant. I think that the first sentence is supposed to AFTER 9/30/2010. If this is correct, then basically, you need your criteria to find the first record in the specified date range as long as they don't have any records before the start date as well. Is this correct?
Apr 7 '14 #4

100+
P: 294
Yes, that's correct. Sorry about the confusion
Apr 7 '14 #5

Post your reply

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