Date Range from Entry of Month + Year

[shaggy]

I'm trying to select records based on a date range which is automatically computed based on entry of only the month and year. The format is YYYYMMDD. I want to add a few days before the beginning of the month and a few after so I chose the 25th and the 5th as the days.


If the month =1 and the year = 2002 the range should be
>= "20011225" and <= "20020205"

If the month =12 and the year = 2002 the range should be
>= "20021125" and <= "20030105"

This is what I've written, but it doesn't return any records. If I enter just the date range it works.
I'm hoping it's just a syntax issue.

>=IIf([Month]="1",([Year]-1) & "1225",IIf([Month]<"11",[Year] & "0" & ([Month]-1) & "25",[Year] & ([Month]-1) & "25")) And <=IIf([Month]="12",([Year]+1) & "0105",IIf([Month]<"9",[Year] & "0" & ([Month]+1) & "05",[Year] & ([Month]+1) & "05"))

Any help would be appreciated.

 

[shaggy]

I took the quotation marks out right after the IIf statements and it seems to be working. I won't know for sure until I can test it at work Monday.

>=IIf([Month]=1,([Year]-1) & "1225",IIf([Month]<11,[Year] & "0" & ([Month]-1) & "25",[Year] & ([Month]-1) & "25")) And <=IIf([Month]=12,([Year]+1) & "0105",IIf([Month]<9,[Year] & "0" & ([Month]+1) & "05",[Year] & ([Month]+1) & "05"))

Thanks for the help.

 

[raskew]

If your date field is in date/time data type (vs. text) then this criteria
should return the desired date range, regardless of how your field
is formatted. Note: when prompted for [Enter mm/yyyy], enter
something like 12/1994:

Between DateSerial(Right([enter mm/yyyy],4),Left([enter mm/yyyy],2)-1,25) 
And DateSerial(Right([enter mm/yyyy],4),Left([enter mm/yyyy],2)+1,5)

You can test it in Northwind with this query:

SELECT Orders.*, Orders.OrderDate
FROM Orders
WHERE (((Orders.OrderDate) Between 
DateSerial(Right([enter mm/yyyy],4),Left([enter mm/yyyy],2)-1,25) 
And DateSerial(Right([enter mm/yyyy],4),Left([enter mm/yyyy],2)+1,5)));

 

[shaggy]

The date field is an 8 digit numeric field YYYYMMDD.

 

[raskew]

Using a numeric field to hold a date negates
the benefit of all of the date related functions
already in Access and would seem to invite
unnecessary programming every time the user
needed to do anything date-related.

Were it my table, I'd add a new field, e.g.
MyDteDate, and use an update query to
populate the field using data in the numeric
field. Example

UPDATE tblMyTbl SET tblMyTbl.mydtedate = DateValue(Mid([numdate],5,2) & "/" & Right([numdate],2) & "/" & Left([numdate],4));

Once done, you can format the field to display
as yyyymmdd if desired.

 

[shaggy]

I agree with you about the date, but I didn't create the database. It's been created per the specifications of an application we are using and the application isn't customizable.