Query criteria based on combo box

[zapzip]

I have a form with a combo box with following
values
i. Custom date (select a range from to)
ii. All dates
iii. All to date
iv. Current month
v. Current year
vi. Current Fiscal year
vii. Month to date
viii. Year to date
ix. Fiscal year to date
x. Previous month
xi. Previous year
xii. Previous Fisical Year
xiii. Last 30 days
xiv. Last 60 days
xv. Last 3 months
xvi. Last 6 months
xvii. Last 12 months
xviii. Next 30 days
xix. Next 60 days
xx. Next 3 months
xxi. Next 6 months
xxii. Next 12 months

How can I tie these to the query's criteria

 

[TheAceMan1]

How are ya zapzip . . .

You need to set [blue]date ranges[/blue] upon selection and stuff them into two hidden textboxes on your form (StartDate/EndDate). Via form referencing or a function, these would be used in your criteria.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[zapzip]

Thank you for pointing me in the right direction.

I created 2 hidden combo boxes on my Form F_FindReplace for the start & end dates
cboHiddenStartDate
cboHiddenEndDate

I then OnExit of cboRangeDate created a series of If’s for example if Custom dates is selected then the start & end dates are populated:
If cboRangeDate = "Custom dates" Then cboHiddenStartDate = cboFromDate
If cboRangeDate = "Custom dates" Then cboHiddenEndDate = cboToDate

Finally in my query I placed the following criteria in the date field
Between [Forms]![F_FindReplace]![cboHiddenStartDate] And [Forms]![F_FindReplace]![cboHiddenEndDate]

Unfortunetly, All dates are returned, not just the range selected.

The Hidden start & End date values are correctly filling the applicable form controls, but the query criteria is NOT working. If I enter Between #1/1/2010# and #2/1/2010# directly in the criteria the correct results are returned. I have also set all the date controls to Short Date format.

Can you point out where I am going wrong?

On a related issue if I want to display ALL DATES- what is the earliest beginning date and latest future date?

 

[TheAceMan1]

zapzip . . .

Including all possible dates in the [blue]Start/End[/blue] date comboboxes seems [blue]absurd[/blue]. All dates should can be calculated within [blue]cboRangeDate[/blue]. For [blue]custom[/blue] you can use an [blue]Inputbox[/blue] presented to the user for the dates. The event to use for the combobox should be [blue]After Update[/blue]. This event triggers on selection of a value! ... the [blue]On Exit[/blue] does not.

[blue]If I enter Between #1/1/2010# and #2/1/2010# directly in the criteria the correct results are returned.[/blue]

Ah Ha! ... a format that works ehhhhhhh! To polish it a little try the following:

[blue]Between "#" & Format([Forms]![F_FindReplace]![[purple][b]cboHiddenStartDate[/b][/purple]],'yyyy/m/d') & "#" And "#" & Format([Forms]![F_FindReplace]![[purple][b]cboHiddenEndDate[/b][/purple]],'yyyy/m/d') & '#'[/blue]

Note: since you will be using the textboxes ... them combobox names will change to the textbox names.

Note: when comparing dates, it has to be done in a [blue]US accepted date format![/blue] ... foreign or otherwise. Hence the reason for the format.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[zapzip]

Thanks Aceman1

Your suggestions sparked a relook at the query where I found a Or And conflict that was the problem. I did as did as you suggested change the event to afterupdate and created text boxes.

The query could not digest your sample code and the "#" constructions are not necessary. Here is the code that worked:

Private Sub cboRangeDate_AfterUpdate()
'Set date ranges in hidden start & end date controls 
Select Case Me.cboRangeDate.Value
    Case Is = "Today"
        txtHiddenStartDate = Date
        txtHiddenEndDate = Date
    Case Is = "Custom dates"
        txtFromDate.Visible = yes
        txtToDate.Visible = yes
        txtHiddenStartDate = txtFromDate
        txtHiddenEndDate = txtToDate
    Case Is = "All to date"
        txtHiddenStartDate = #1/1/100#
        txtHiddenEndDate = Date
    Case Is = "All dates"
        txtHiddenStartDate = #1/1/100#
        txtHiddenEndDate = #1/1/3000#
    Case Is = "Current month"
        txtHiddenStartDate = Format(Now(), "M") & "/" & "1" & "/" & Format(Now(), "yyyy")
        txtHiddenEndDate = DateAdd("M", 1, Now)  'next month
        txtHiddenEndDate = Format(txtHiddenEndDate, "M") & "/" & "1" & "/" & Format(Now(), "yyyy") '1st of next month
        txtHiddenEndDate = DateAdd("d", -1, txtHiddenEndDate) 'last day of  current month
    Case Is = "Current year"
        txtHiddenStartDate = "1" & "/" & "1" & "/" & Format(Now(), "yyyy")
        txtHiddenEndDate = DateAdd("yyyy", 1, Now) 'next year
        txtHiddenEndDate = "1" & "/" & "1" & "/" & Format(txtHiddenEndDate, "yyyy") '1st day of next year
        txtHiddenEndDate = DateAdd("d", -1, txtHiddenEndDate) 'last day of  current year
    Case Is = "Month to date"
        txtHiddenStartDate = Format(Now(), "M") & "/" & "1" & "/" & Format(Now(), "yyyy")
        txtHiddenEndDate = Now()
    Case Is = "Year to date"
        txtHiddenStartDate = "1" & "/" & "1" & "/" & Format(Now(), "yyyy")
        txtHiddenEndDate = Now()
    Case Is = "Previous month"
        txtHiddenStartDate = Format(DateAdd("m", -1, Date), "m") & "/" & "1" & "/" & Format(Now(), "yyyy")
        txtHiddenEndDate = Format(Now(), "M") & "/" & "1" & "/" & Format(Now(), "yyyy") '1st of current month
        txtHiddenEndDate = DateAdd("d", -1, txtHiddenEndDate)  'previous month last day
    Case Is = "Previous year"
        txtHiddenStartDate = "1" & "/" & "1" & "/" & Format(DateAdd("yyyy", -1, Now), "yyyy")
        txtHiddenEndDate = "1" & "/" & "1" & "/" & Format(txtHiddenEndDate, "yyyy") '1st day of current year
        txtHiddenEndDate = DateAdd("d", -1, txtHiddenEndDate) 'last day of  last year
    Case Is = "Last 30 days"
        txtHiddenStartDate = DateAdd("d", -31, Date)
        txtHiddenEndDate = Now()
    Case Is = "Last 3 months"
        txtHiddenStartDate = DateAdd("d", -91, Date)
        txtHiddenEndDate = Now()
    Case Is = "Last 6 Months"
        txtHiddenStartDate = DateAdd("m", -7, Date)
        txtHiddenEndDate = Now()
    Case Is = "Last 12 months"
        txtHiddenStartDate = DateAdd("m", -13, Date)
        txtHiddenEndDate = Now()
    Case Is = "Next 30 days"
        txtHiddenStartDate = Now()
        txtHiddenEndDate = DateAdd("d", 31, Date)
    Case Is = "Next 3 months"
        txtHiddenStartDate = Now()
        txtHiddenEndDate = DateAdd("d", 91, Date)
    Case Is = "Next 6 Months"
        txtHiddenStartDate = Now()
        txtHiddenEndDate = DateAdd("m", 7, Date)
    Case Is = "Next 12 months"
        txtHiddenStartDate = Now()
        txtHiddenEndDate = DateAdd("m", 13, Date)
        
    Refresh
    
End Select

For completness the query criteria is 
Between [Forms]![F_FindReplace]![txtHiddenStartDate] And [Forms]![F_FindReplace]![txtHiddenEndDate][code/]

It's exciting when you can get things to work.  Thanks for your help.

Two minor issues remain that I could use some direction on:
1.  Null dates are not addressed.  Since the query criteria is a range how on earth can one address nulls?
2.  THe All dates and All to date ranges were artificially force using 1/1/100 as the earliest date and 1/1/3000 as the latest future date.  Seems like something less arbitrary would be better?