I’ve got a table that I need to store date and time in form of “3/1/2011 10:41:36 AM”. I know that I can select this through SQL with “format([date], ‘mm/dd/yy’”. However how can I run criteria against these formated dates? Where date()-1 and so fourth. I cannot get any results.
do not store formatted dates, use the DATETIME datatype instead
that’s the only way you will get good results from date calculations etc.
Unfortunately I’m limited by the data I receive. I have no say in how its stored. I did manage to figure out how to do what Im looking for the most part.
Is there a way to do a complete outer join in access? Im trying to do a count on some fields that are completed within the last week, some may have some counts, some may not. Im either looking at a full outter join between 3 tables (which I have no idea how to do) or looking at creating an expression that uses access’ date function to populate the dates of those 7 days in the week.
there’s a lot going on here that i don’t understand 
first, you say you “need to” store formatted dates, suggesting that the output formatting is what you want, now you say you have no say in how it is stored
yes, there is a way to do a full outer join in access, but that’s not what you want, it sounds like you need only a left outer join, assuming you can join a table of desired dates with your data based on this formatted date
[small]Edit: see http://www.sitepoint.com/forums/showthread.php?t=736408[/small]
“within the last week” is harder to do if you are dealing with VARCHARs that contain strings that look like dates, because you cannot do date arithmetic with strings
so you are unfortunately down to creating a match based on a list of seven formatted date strings which you would then compare to your data based on the leftmost 8-10 characters (to allow for the difference in string length between 3/1/2011 and 03/01/2011) so as to ignore the time portion of the string
Seems to be alot of confusion here. Firstly The dates are stored in DATETIME format. Which I just realized I don’t need to format at all to run datdiff() against.
But Im still left with the join issue. I have 3 tables. All three may have dates that do not have corresponding dates on the other. But None the less I need to merge all dates into one column on the left.
you are right, there is a lot of confusion
let’s eliminate it by having you show us the actual table layouts (column names and datatypes) for all tables involved