automatic date range problem

[Knowbody]

I'm having trouble getting Access to perform a query involving a date range. Ultimately, I am trying to get it to automatically pick a range of dates based on what the system date is. The query pulls its information from a table called "reports" and the date field is called "RepPer". For test purposes in the statement I have used, false statements return a set date, but will probably return something different when I've got this problem sorted. True statements should return the range associated with the system date.

IIf((Date()>#5/19/2003#) And (Date()<#6/17/2003#),[reports.RepPer]=(([reports].[RepPer]) Between #5/20/2003# And ([reports].[RepPer])<#6/16/2003#),#9/4/2003#)

Can anyone see where I'm going wrong?

In the end, I'm going to have to put something like this into an array in order to make the statement evaluate itself correctly for like the next 18 months. I want to make sure I understand what I'm doing for a single month first though.

I have looked through the archives already and quite much time has been spent on the help file. I guess I don't have enough of a grasp on Access though.

Any help or alternate suggestions would be greatly appreciated.

Erich

 

[Fizzio]

Have you tried this?

IIf((Date()>#5/19/2003#) And (Date()<#6/17/2003#),[reports.RepPer]=Between #5/20/2003# And #6/16/2003#,#9/4/2003#)

 

[Knowbody]

yes, I tried it that way first. I had to add parenthesis because it gave me an invalid syntax error. Even after fixing for the syntax, it still does not pull any records. There are definitely records during that period also.

Anybody have any other suggestions?

 

[Mile-O]

Remove the = before the Between

 

[Mile-O]

Is this a criteria? If so, it shouldn't work.

 

[Knowbody]

Is this a criteria? If so, it shouldn't work.

well, it was a criteria Is there another way I should be trying to insert this statement into this query? The help file doesn't tell me where I can and can't use certain statements, so I'm winging it. Right now I'm looking real hard at the "Select Case" statement as I think it may be a better fit for being able to evaluate a range of dates over the next year as opposed to a long error prone IIf statement.

This query will be the basis for a report that is run once a month. Based on the date it is run, I want it to choose an appropriate range of dates records could have been entered to be included in the report. For instance, if we assume I run it: 09/16/2003
It should then pull all records entered on the dates 08/19/2003 through 09/15/2003. If it were run on 07/15/2003, it would return records entered from 06/17/2003 through 07/14/2003.

Basically, it is meant to be run every third Tuesday and pull all records from the previous period which would be the third tuesday of the prior month up to the current month's third Monday. I couldn't find a way to make Access understand X Tuesday through Y Monday, so I figured giving it explicit instructions on what any date it could be run on would equate to would solve my problem. Then I ended up with the problem of making it understand me.

So, I'm open to anyone's suggestion on a better way to have Access determine what records are ok to pull and what records aren't. If anyone else thinks Select Case will work, as I'm hoping it will, can you please help me out with the proper formatting of the statement and where exactly it should go (if not the criteria box).

Erich

 

[Fizzio]

Sorry, I just assumed it was in the field box. Try the same statement in the Field box rather than in the Criteria box.

 

[Pat Hartman]

You can use an IIf() as criteria but you need to understand what condition you are ultimately testing.

The IIf() is a function and as a function it will return a single value. Do you want that value to

be a boolean True or False or do you want it to be a value such as a particular date? You can have

your criteria in one of two formats:
1. Where II(...) = True ' requires IIf() to return true or false

2. Where SomeField = IIf(..) ' requires IIf() to return some value compatable with SomeField

The IIf() you posted is a mish-mash. If it worked, (which it doesn't because of syntax errors), in

some cases it wouldl return True and in others it would return a date. That won't work as selection

criteria.

To solve your problem, I would write a user defined function that calculates the necessary date range

given the current date.

I've attached a sample db (A2K) with date functions that I use to get you started. Just for you, I added a

special function that calculates the date of the first instance of a specific weekday. Since you want

the third instance, you would add 14 to the date that my function returns.

I uploaded a new version of this db. It contains a function that calculates differences between dates and returns a formatted string plus six individual fields.

 

[Knowbody]

Thank you all for your feedback so far.

Mile-O-Phile, thanks for telling me where it doesn't go.

Fizzio, I have never heard of putting anything like this in the Field box. I'll certainly try it today. I've always been told that it goes in the criteria box, but then too, I don't often interact with many who have the experience of those of you on this forum.

Pat, I'm going to play around with these date funtions you've posted and see if I can find a solution.

Erich

 

[EMP]

I have never heard of putting anything like this in the Field box. ....... I've always been told that it goes in the criteria box

You can put the criteria in a column like this:-

Field: IIf(Date()>#5/19/2003# And Date()<#6/17/2003#, [reports].[RepPer] Between #5/20/2003# And #6/16/2003#, [reports].[RepPer] = #9/4/2003#)

Show: uncheck

Criteria: <>False

 

[Knowbody]

THANK YOU!!!

It works. Now, can someone point me to an explanation of why it worked? I would have never placed any of that in the field box.

 

[Fizzio]

Essentially, the QBE grid is a graphical representation of the SQL statement but presented in an easier to understand fashion. You have bypassed that by essentially putting the what access would do normally into the Field part of the QBE grid. Access cannot quite decipher the IIF in the field box and split it into a graphical representation. The SQL that will actually be placed in the query will be either
[reports].[RepPer] Between #5/20/2003# And #6/16/2003#
or
[reports].[RepPer] = #9/4/2003#)
depending on how the IIf evaluates.

I'm not sure if this gives clarity but it is the best I can do - hopefully Pat will dive in.........

 

[Knowbody]

your explanation does help some. I guess I need to really begin focusing on learning SQL. I've been relying on the built in functions in Access for a while since most of what I do has been simple up to this point. I'm getting into more areas where I want something more complex but I'm still pretty clueless on the structure and syntax of both SQL and Visual Basic. Reading through here has been a major help for me. You all are great.

 

[Pat Hartman]

No, Knowbody, it doesn't work. You need to refine your testing procedures.

[reports].[RepPer] Between #5/20/2003# And #6/16/2003#
or
[reports].[RepPer] = #9/4/2003#)

- sorry but that is incorrect.

Condition:
IIf(Date()>#5/19/2003# And Date()<#6/17/2003#
True Path:
[reports].[RepPer] Between #5/20/2003# And #6/16/2003#
False Path:
[reports].[RepPer] = #9/4/2003#)

The true path will be evaluated strangely. This is the result of the expression as printed in the immediate window:

print now() between date() and date()+1
9/25/2003 8:33:11 PM 37888

It seems to print the contents of RepPer followed by a space followed by the serial number of date() -1?

The false path will be evaluated to true if the dates are equal and false if the RepPer is <> to the hard coded date.

I reformulated the query slightly to work with the MSysObjects table so you can copy it to your db and work with it. I changed the dates to work with the change dates in my database. I also added a where clause simply to reduce the sample resultset I am posting.

SELECT MSysObjects.Name, MSysObjects.DateUpdate, 
IIf(Date()>#5/19/2003# And Date() < #10/17/2003#, 
       [MSysObjects].[DateUpdate] Between #9/1/2003# And #10/1/2003#,[MSysObjects].
       [DateUpdate]=#4/4/2003#) AS field
FROM MSysObjects
WHERE (((MSysObjects.DateUpdate)>#4/5/2003#));

In any event, placing the IIf() as a selected field, does NOT provide any selection criteria.

 

[EMP]

When you add Date() Between Date() And Date()+1 in the Select Clause of any query, it will return -1, signifying True. So will Now() Between Date() And Date()+1.   After all, both Date() and Now() are always between Date() and Date()+1.

Perhaps they work differently in a query and the immediate window!!


Putting an IIF expression in the Field: cell together with an <>False in the Criteria: cell in a column in the query grid signifies that the IIF expression in the Field: cell is a criterion. It is easy to see when you switch to query SQL View as the IIF expression will be displayed in the Where Clause. Of course, if you have checked Show: in the query grid, the IIF expression will be displayed in the Select Clause as well.

If you directly type the IIF expression in the Where Clause in SQL View, you don't need to type <>False at its end.

The important thing is remember to include the field name in both the true part and the false part:

IIf(Date()>#5/19/2003# And Date()<#6/17/2003#, [reports].[RepPer] Between #5/20/2003# And #6/16/2003#, [reports].[RepPer] = #9/4/2003#)

 

[Fizzio]

Thanks Pat and EMP for correcting me.
I obviously had an erroneus understanding of the behaviour of IIf in the Field: cell of the QBE grid.
My explanation made sense to me (although incorrect) but Pat's and EMP's explanations, although I understand both, seem to contradict one another re: the IIf providing selection criteria if placed in the Field: cell.

 

[EMP]

The IIF in the Field: cell plus the <>False in the Criteria: cell provides selection criteria.

The IIF in the Field: cell alone does not.

 

[Fizzio]

Thanks

 

[st3ve]

Date Serial : Query vs Module

I have been experimenting with the DateSerial function (using Pat Hartman's 'usefuldatefunctions' posted here recently) using the two methods below: I get different results! If DateOfBirth is,say, 29/09/2003, SQL gives me 01/09/2003, but the module version gives me 01/09/2029. The yy in the result changes according to the dd in the original DateOfBirth expression! This appears to only happen when the DateOfBirth is between 2001 and 2029.

SQL: SELECT tbl007A.DateOfBirth, DateSerial(Year([DateOfBirth]),Month([DateOfBirth]),1) AS FstDayOfMth
FROM tbl007A;

Module: Public Function FstDayOfMth(DateOfBirth As Date) As MonthDoBStart
FstDayOfMth = DateSerial(Year(DateOfBirth), Month(DateOfBirth), 1)
End Function

I am using Acc97 and also Acc2K on W98. This almost sounds like it may? have something? to do with the assumptions made for 'yy only' dates being between 00 and 29 being mapped as 2000 and yy and those from 30 to 99 mapped as 1900 and yy,

 

[Pat Hartman]

1. There was no need to change the parameter name in the function.
2. The function needs to return a Date datatype.

Module: Public Function FstDayOfMth(DateOfBirth As Date) As Date 
FstDayOfMth = DateSerial(Year(DateOfBirth), Month(DateOfBirth), 1) 
End Function

3. Yes, the problem is the assumption made when the date entered is only two digits. To prevent this problem, you'll need to force the user to enter 4 digits for year. Your code should also disallow future dates as birthdates. Depending on your application, you also may be able to reject dates outside of a particular range. Perhaps birthdates need to between theCurrentDate - 20 years and theCurrentDate - 70 years. You can give the user an override ability but at least throwing an error warns the user that he may have mis-entered the date.