Default Year to Date or DateBox to Date (1 Viewer)

Hi,

I'm trying to use SQL coding in the WHERE criteria to get a query to either
A: evaluate a series of formulas from a table based on information from the date entered in a datebox

OR

B: evaluate a series of formulas from a table based on the year to date by default if the datebox is left empty

I just lost the SQL code for what I was doing, which wasn't working anyway. Does anyone know how to do this in VBA or in SQL? I have all the formulas and other criteria working; I just don't know how to insert this date function.

It sounds like your beginning date in either of your two options is the first of the year. Is that correct?

It sounds like if a date is specified on the form that date is considered the ending date and if it is not specified, today's date is considered the ending date. Is that correct?

If my guesses are correct, your WHERE clause should look like this (you will have to substitute your date field name as well as the form name and name of the control on the form (where the date is entered)

WHERE dteOrder Between DateSerial(Year(Date()),1,1) And IIf(IsNull([forms]![formname]![formcontrolname]),Date(),[forms]![formname]![formcontrolname])

Actually, the reverse:

I want the end date to be today regardless, and the start date to be specified or default to not the first of the year, but today - 1 year (so today would return results from 8/8/2011 - 8/8/2012). I took your SQL and tried to twist it to apply to this:


WHERE ((([Project Master Table].[Team Assigned Name])="CONSULTNG") AND [Project Master Table].[Date Requested] Between Iif(IsNull([Forms]![[Manager Report Filter]![txtDate], DateSerial(Year(Date()) - 1, Month(Date()), Day(Date())), [forms]![Manager Report Filter]![txtDate]) AND DateSerial(Year(Date()), Month(Date()), Day(Date())))

But I'm getting a syntax error about a missing operator, however SQL highlights the GROUP BY statement after the end of my WHERE clause, so I can't tell where. Also, because this might affect things, I have a sum and count function in my SELECT clause, which I know can cause some odd problems.

What I typically do is have a non-aggregate query (no sums, counts group by) to extract the data based on the criteria & then base the aggregate query on that query

It looks like you might be missing a parenthesis. Also, why bother using the date serial for the current date just use date()

Iif(IsNull([Forms]![[Manager Report Filter]![txtDate]), DateSerial(Year(Date()) - 1, Month(Date()), Day(Date())), [forms]![Manager Report Filter]![txtDate]) AND DateSerial(Year(Date()), Month(Date()), Day(Date())))

How about this instead:

Iif(IsNull([Forms]![[Manager Report Filter]![txtDate]), DateSerial(Year(Date()) - 1, Month(Date()), Day(Date())), [forms]![Manager Report Filter]![txtDate]) AND Date())

Also, you might run into problems with your first date expression. Let's say that today is 2/29/12. If you just subtract 1 year from the year portion of the dateserial() function, you will end up with 2/29/11 which does not exist!

I would recommend this:

dateserial (year(dateadd("yyyy",-1,date()), month(dateadd("yyyy",-1,date()), day(dateadd("yyyy",-1,date()))

Well, the good news is I can get the non-aggregate to run. The bad news is it returns no results, though it should. There are a total of six fields being returned and only these two criteria, which I know should return results. Any ideas?

WHERE ((([Project Master Tbl].[Team Assigned Name])="CONSLTING ANL") AND [Project Master Tbl]![Date Requested] Between Iif(IsNull([Forms]![Manager Report Filter]![txtDate]), DateSerial(Year(dateadd("yyyy",-1,date())), Month(dateadd("yyyy",-1,Date())), Day(dateadd("yyyy",-1,Date()))), [forms]![Manager Report Filter]![txtDate]) AND Date());

Is this string spelled correctly: "CONSLTING ANL" ?

Run the query with no criteria. Do you get results? If not then the problem lies in the join between the tables.

Then add one criteria back in. Do you get results? If not then the problem lies in the criteria.

Add the second criteria in. Do you get results? If not then the problem lies in the second criteria.

Well, CONSLING ANL was supposed to be CONSLTNG ANL, so there's that one,

Then I plugged in and they didn't work one by one...

Then I plugged in and they did work one by one and now together. In any case, thanks so much for your help!

You're welcome.