Date Criteria

[Sheila.deJesus]

Good day!

I have a query that filters records which production starts at the first day of the year and ends a day prior to the current month.

I used this criteria:
Between [Enter: First day of the year] And [Enter: Date prior to current month]

I need a criteria which will filter records automatically. I mean, without having to key in required dates.

Thanks a lot in advance.


Sheila

 

[raskew]

Sheila -

Would this work -- today is 7-Aug-06?

From the debug (immediate) window:

? "between " & dateserial(year(date), 1, 1) & " and " & dateserial(year(date), month(date), 1 -1)

between 1/1/06 and 7/31/06

...to actually use it, try this in the criteria cell of your date field:

Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),Month(Date()),1-1)

HTH - Bob

added: Just noted that this wouldn't work if you tried to implement it during the month of January. What would your plan be?

 

[Sheila.deJesus]

Raskew,

It works perfectly!

Thank you!!!


Sheila

 

[Sheila.deJesus]

Raskew,

I tried to base my other date criteria from what you have posted. Forgive my ignorance, but i just can't get the other criteria works..

Whenever a user wants to generate a Production Report of Insurance Renewals for a specific month, the user is required to key in the last day of that particular month.

Here's the scenario:

Once i click on the command button, the db would ask me to enter the parameter value [Enter: Current Month/Year].

If i enter "31-Jul-06" as the current month/year, i would like the following criterias to be met automatically:

1. sales during the same period last year = "31-Jul-05"
2. check for outside sources (The db will compare current month's sold policies against the whole period of last year). Between "31-Jan-05" And "31-Dec-05"
3. Early Renewals (This would generate how many customers renewed their insurance policies prior to it's supposed production month) = Between "31-Jan-06" And "30-Jun-06"
4. Late Renewals (Number of policies sold last year which did not renew on or before its supposed production month) = Between "31-Jan-05" And "30-Jun-05"


Although, my report/query provides the output i need, it bothers me and the users to key in so many in between dates.

Thank you!


Sheila

 

[raskew]

Sheila -

Would you post your current code /query.sql. It'd make it a lot easier to determine what you're currently doing.

Best wishes - Bob

 

[Sheila.deJesus]

Hi Bob!

Thanks for your reply.

Hope this could give you a clear picture of my query.

I also attached a sample report.

SELECT [UsysSources - Current Month Production_Crosstab].[ISE / MP], [UsysSources - Current Month Production_Crosstab].[Total Of Bus Type], [UsysSources - Previous Month/Year Production_Crosstab].[Total Of Bus Type], [UsysSources - RB Renewals_Crosstab].[Total Of Plate / CS No], [UsysSources - ISE Early Renewals_Crosstab].[Total Of Plate / CS No], [UsysSources - RB Lost Accounts_Crosstab].[Total Of Plate / CS No], [UsysSources - Allocated Client List_Crosstab].[Total Of Plate / CS No], [UsysSources - NB Renewals_Crosstab].[Total Of Plate / CS No], [UsysSources - NB Renewed Early_Crosstab].[Total Of Plate / CS No], [UsysSources - NB Lost Accounts_Crosstab].[Total Of Plate / CS No], [UsysSources - ISE Outside Sources_Crosstab].[Total Of Plate / CS No], [UsysSources - Late_Crosstab].[Total Of Bus Type], [UsysSources - Advanced_Crosstab].[Total Of Bus Type]
FROM (((((((((([UsysSources - Current Month Production_Crosstab] LEFT JOIN [UsysSources - Previous Month/Year Production_Crosstab] ON [UsysSources - Current Month Production_Crosstab].[ISE / MP] = [UsysSources - Previous Month/Year Production_Crosstab].[ISE / MP]) LEFT JOIN [UsysSources - RB Renewals_Crosstab] ON [UsysSources - Current Month Production_Crosstab].[ISE / MP] = [UsysSources - RB Renewals_Crosstab].[ISE / MP]) LEFT JOIN [UsysSources - ISE Early Renewals_Crosstab] ON [UsysSources - Current Month Production_Crosstab].[ISE / MP] = [UsysSources - ISE Early Renewals_Crosstab].[ISE / MP]) LEFT JOIN [UsysSources - RB Lost Accounts_Crosstab] ON [UsysSources - Current Month Production_Crosstab].[ISE / MP] = [UsysSources - RB Lost Accounts_Crosstab].[ISE / MP]) LEFT JOIN [UsysSources - Allocated Client List_Crosstab] ON [UsysSources - Current Month Production_Crosstab].[ISE / MP] = [UsysSources - Allocated Client List_Crosstab].[Assigned to (ISE)]) LEFT JOIN [UsysSources - NB Renewals_Crosstab] ON [UsysSources - Allocated Client List_Crosstab].[Assigned to (ISE)] = [UsysSources - NB Renewals_Crosstab].[ISE / MP]) LEFT JOIN [UsysSources - NB Renewed Early_Crosstab] ON [UsysSources - Allocated Client List_Crosstab].[Assigned to (ISE)] = [UsysSources - NB Renewed Early_Crosstab].[ISE / MP]) LEFT JOIN [UsysSources - NB Lost Accounts_Crosstab] ON [UsysSources - Allocated Client List_Crosstab].[Assigned to (ISE)] = [UsysSources - NB Lost Accounts_Crosstab].[Assigned to (ISE)]) LEFT JOIN [UsysSources - ISE Outside Sources_Crosstab] ON [UsysSources - Current Month Production_Crosstab].[ISE / MP] = [UsysSources - ISE Outside Sources_Crosstab].[ISE / MP]) LEFT JOIN [UsysSources - Late_Crosstab] ON [UsysSources - Current Month Production_Crosstab].[ISE / MP] = [UsysSources - Late_Crosstab].[ISE / MP]) LEFT JOIN [UsysSources - Advanced_Crosstab] ON [UsysSources - Current Month Production_Crosstab].[ISE / MP] = [UsysSources - Advanced_Crosstab].[ISE / MP];

Thank you in advance!

Sheila

 

[raskew]

Sheila -

Hang in there, I haven't forgotten you. Your situation is complex and trying to come up with a helpful suggestion takes a while. Appears you have at least 12 cross-tab queries, and who knows how many underlying queries.

Think the key is the cross-tab / queries that prompt for a start-date/end-date. Every one of them, as you've described, can be resolved if we just know the mm/yyyy (e.g. "07/2006").

If you can shed any more light, it'll be helpful

Best wishes,

Bob

 

[Sheila.deJesus]

Finally!!! finally!!! I figured out the date criterias i needed!!! Thanks to you!!

Like what i've mentioned, i always use the last day of the month to generate my report. Say, today is "31-Jul-06"...

1. how to filter records during the same period last year = Year([Enter: Current Month])-1

2. how to filter records from the beginning of the year up to the month prior to desired production month = Between DateSerial(Year([Enter: Current Month]),1,1) And DateSerial(Year([Enter: Current Month]),Month([Enter: Current Month]),1-1)

3. how to filter records from the beginning of the year up to the current month = Year([Enter: Current Month])

4. how to filter records during the the whole year prior = Year([Enter: Current Month])-1

I still have concerns, though... I still can't figure out how to retrieve records that falls under these scenarios:

If current month is "31-Oct-05":
1. Between "31-Jan-04" and "30-Sep-04"
2. Between ""30-Nov-04" and "31-Dec-04"

Thanks again Bob!

Sheila

 

[raskew]

Hi Sheila-

If current month is "31-Oct-05":

'1. Between "31-Jan-04" and "30-Sep-04"

x = #31-Oct-05#
? "between #" & dateserial(year(x)-1,2,0) & "# and #" & dateserial(year(x)-1, month(x),0) & "#"
between #1/31/04# and #9/30/04#

'2. Between "30-Nov-04" and "31-Dec-04"

? "between #" & dateserial(year(x)-1, month(x) + 2, 0) & "# and #" & dateserial(year(x) ,1,0) & "#"
between #11/30/04# and #12/31/04#

HTH

 

[Sheila.deJesus]

Hi Bob!

Thank you so much for the inputs!!!

I hope this is not too much to ask, but, (forgive me for my ignorance) how do i use the codes in query?

Sheila