Select Qry - Join on date - returns no records

[mcupito]

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:

SELECT AwardTbl.EmployeeID, AwardTbl.PlanID, PlanTbl.PlanDesc, AwardTbl.AwardDate, AwardTbl.AwardUnits, AwardTbl.AwardNAV
FROM 
PlanTbl
INNER JOIN 
(NAV_Tbl INNER JOIN AwardTbl ON NAV_Tbl.NAV_Date = AwardTbl.AwardNAV) 
ON PlanTbl.PlanID = AwardTbl.PlanID;

Anyone have any ideas or see something that jumps out at them as wrong? Thanks.

[mcupito]

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.

Format( Date, "Short Date")

[zmbd]

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.

[NeoPa]

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.

[mcupito]

Ah, okay. I did not know that. Dates are definitely behaving fairly oddly in Access. This is my last Access project, though. Thankfully..

[NeoPa]

That's why we're here Mark. We explain things that might not be obvious to everyone :-)