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.

 

[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.

 

[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,

 

[raskew]

“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 personally don’t see nested Iif() statements as being the best solution—the coding becomes extremely error prone. It would seem that, given a date (which can be any date in the current month), you just need a way of determining:
a. The third Tuesday of the prior month.
b. The third Monday of the current month.

The following function (copy/paste to a new module) will do that for you:

Public Function NthXDay(pDate As Variant, pWDay As Integer, pIncrement As Integer) As Date
'*******************************************
'Name:      NthXDay (Function)
'Purpose:   Find the nth occurence of a weekday
'           within any given month, with Sunday = 1
'           through Saturday = 7
'Inputs:    (1) To find the 3rd Thursday of  Sep 2003
'           ? NthXDay(#9/24/03#, 5, 3)
'           (2) Specify pIncrement as 6 to return the 'last'
'           occurence within a month
'           To find the last Thursday of Sep 2003
'           ? NthXDay(#9/24/03#, 5, 6)
'Output:    (1) 9/18/03
'           (2) 9/25/03
'*******************************************

Dim dteDate As Date, newDate As Date, Msg As String

'find the first day of the month or if pIncrement is 6
'(to indicate the last pWDay of the month) the first day
'of the following month

dteDate = DateSerial(Year(DateValue(pDate)), _
Month(DateValue(pDate)) + IIf(pIncrement = 6, 1, 0), 1)

'move to the first pWDay
newDate = dteDate - WeekDay(dteDate) + pWDay + IIf(WeekDay(dteDate) > pWDay, 7, 0)

'move to the specified 7* (pIncrement -1) occurence of pWDay or
'if pIncrement is 6, the preceding occurence (-7) of pWDay
newDate = DateAdd("d", IIf(pIncrement = 6, -7, 7 * (pIncrement - 1)), newDate)

'if pIncrement is 5 and there are not 5 occurences of PWDay in
'the specified month, return an error message, else return the
'calculated date

If Month(newDate) > Month(dteDate) Then
   Msg = "There are only four " & Format(WeekDay(pWDay), "dddd") & "'s in " & Format(Month(dteDate), "mmmm")
   MsgBox Msg, vbInformation, "Error"
   Exit Function
Else
   NthXDay = newDate
End If

End Function

To test how this would work within a query:
(1) Create a link to Northwind’s Orders table.
(2) Create a new query and copy/paste this SQL:

PARAMETERS [enter a date] DateTime;
SELECT Orders.OrderID, Orders.OrderDate, Format(Weekday([orderdate]),"dddd") AS Expr1, [enter a date] AS MyInputDate
FROM Orders
WHERE (((Orders.OrderDate) Between nthxday(DateAdd("m",-1,[enter a date]),3,3) And nthxday([enter a date],2,3)));

(3) Run the query and, when prompted to [enter a date], enter a date between 1-Sep-94 and 30-Apr-96 (the effective range of the Orders table, for the purposes of this illustration). You should be returned all records from the third Tuesday of the month prior to [enter a date] up to the month of the [enter a date] third Monday.

To avoid confusion between date formats, suggest you initially use the Medium Date format shown above.

HTH-

Bob

 

[st3ve]

Date Serial (cont)

Pat Hartman,

Thanks for some helpful comments, inc. re: dates in ccyy instead of just yy format. However, all my dates are in ccyy format. The problem I am seeing is that the date (dd part) seems to be being reflected directly into the yy part of the result:

"If DateOfBirth is,say, 29/09/2003, SQL gives me 01/09/2003, but the module version gives me 01/09/2029".


Note the 29 th (Sept) appears in the output year 2029. If I set the source date as say 19th of the month, the output .year becomes 2019 !, but only in the module; SQL expression correctly shows the input year. This seems to only occur for years 2000 to 2029.

I've just tried 31/08/2003 to find 1st day of month and the result was 01/08/1931! ... which would seem to further support the theory that its all something to do with the Microsoft date algolrithm, albeit being applied in the wrong place?