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

Select Qry - Join on date - returns no records

P: 294
My primary key for the NAV_Tbl is the ending date of each quarter, and it has a NetAssetValue (dollar amount) associated with it, which determines a units' value during each quarter. (Units * NetAssetValue = Value).
(NAV_Date = 12/31/2013 | NetAssetValue = $2,000)

The AwardTbl has a field (AwardNAV) that is the quarter end date of when the awards were given (AwardDate).
(AwardDate = 1/20/2014 -> AwardNAV = 3/31/2014)

I am trying to build a query that returns the value of each award based on the AwardDate, however when I join the AwardTbl and the NAV_Tbl on AwardNAV and NAV_Date, it returns no records. When I don't join them, it works fine, however I don't get the value of each award.

Here is my SQL:
Expand|Select|Wrap|Line Numbers
  1. SELECT AwardTbl.EmployeeID, AwardTbl.PlanID, PlanTbl.PlanDesc, AwardTbl.AwardDate, AwardTbl.AwardUnits, AwardTbl.AwardNAV
  2. FROM 
  3. PlanTbl
  5. (NAV_Tbl INNER JOIN AwardTbl ON NAV_Tbl.NAV_Date = AwardTbl.AwardNAV) 
  6. ON PlanTbl.PlanID = AwardTbl.PlanID;
Anyone have any ideas or see something that jumps out at them as wrong? Thanks.
Jan 23 '14 #1
Share this Question
Share on Google+
5 Replies

P: 294
I found the error. The value in my update query that is putting the AwardNAV in the AwardTbl is a Date/Time and formatted as Short Date, but it keeps putting in the Time also. Anyone know how to fix this? I went to the query and updated the property sheet to reflect just a short date, and likewise in the AwardTbl. Not sure what else to do.

Solved it.

Expand|Select|Wrap|Line Numbers
  1. Format( Date, "Short Date")
Jan 23 '14 #2

Expert Mod 5K+
P: 5,397
Access does some fairly strange things with dates and times
International Dates in Access and even if the dateserial doesn't have a decimal portion, Access will often assume that theres a 00h00 implied depending on the situation.

my update query
You don't provide this information thus all I can offer is that one thing to keep in mind that the Now() will always include the current date and time whereas DateValue() should only insert the date portion.
Jan 23 '14 #3

Expert Mod 15k+
P: 31,492
The DateValue() function will always return the date part of any date/time value you pass it. You can use this to produce the correct value.

Relying on the display format of a field to control the value isn't a good idea. That's all it is - the display format. It has no effect on the value.

The Format() function can be used to return the date part, but frankly it's a kludge and converts it to string, from which you'd have to convert it back into date/time.
Jan 24 '14 #4

P: 294
Ah, okay. I did not know that. Dates are definitely behaving fairly oddly in Access. This is my last Access project, though. Thankfully..
Jan 24 '14 #5

Expert Mod 15k+
P: 31,492
That's why we're here Mark. We explain things that might not be obvious to everyone :-)
Jan 25 '14 #6

Post your reply

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