min and max dates

[compufreaks]

hi everyone

i have researched about this for days but nothing has come up. i would just like to know if this is possible....


i have an option that a user can select a date range by a start date and end date and it shall be displayed on the report as follows...

="Between"+Format([Forms]![ViewReportsForm]![StartDate],"\ d\ mmmm\ yyyy")+"  to  "+Format([Forms]![ViewReportsForm]![EndDate],"d\ mmmm\ yyyy")

now i would like to know that if a user does not select a date range from the form , can i pull the most recent and earliest record?

i have been trying for week to solve this but nothing has come up.

 

[dhookom]

You can try something like:

="Between " & IIf(IsNull([reference to control]),Min([YourDateField),Format(...)) & " to " & IIf(IsNull([reference to control]),Max([YourDateField),Format(...))

Duane
Hook'D on Access
MS Access MVP

 

[compufreaks]

hi there thanks for your reply

i have made some progress with this issue

just having this error , its a runtime 3075 error

strSQL = strSQL & " " & "WHERE [Clusters].Cluster_Desc =" & [Combo1] & " and " & "Source.Day_Month_Year  Between #" & DMax("Day_Month_Year", "Source") & "# AND #" & DMin("Day_Month_Year", "Source") & "#" & ";"

 

[dhookom]

This looks like a different problem/question. Can you share your code and the results after putting line continuations and debug.print in your code:

strSQL = strSQL & " WHERE [Clusters].Cluster_Desc =" & [Combo1] & _
  " and Source.Day_Month_Year  Between #" & _
  DMax("Day_Month_Year", "Source") & "# AND #" & _
  DMin("Day_Month_Year", "Source") & "#;" 
Debug.Print strSQL

Also tell us if Cluster_Desc is text.

Duane
Hook'D on Access
MS Access MVP

 

[compufreaks]

hi i have managed to sort this issue out ...

here is the solution

 strSQL = strSQL & " WHERE [Clusters].Cluster_Desc='" & [Combo1] & "' AND Source.Day_Month_Year Between #" & DMax("Day_Month_Year", "Source") & "# AND #" & DMin("Day_Month_Year", "Source") & "#;"

thanks again for all your help

 

[dhookom]

If "Source" is a table/query in your report's record source then you shouldn't need to include "Source.Day_Month_Year" in the WHERE clause. IMO, you should also change the name of Combo1 to something that makes sense. It only takes a couple seconds to make your apps look more professional. I would also prefix the combo box names with "Me."

[CODE vba]
strSQL = strSQL & " WHERE [Clusters].Cluster_Desc='" & Me.[cboClustDesc] & "'"
[/code]

Duane
Hook'D on Access
MS Access MVP