Filter form by date - Problems!

[jbutcher]

Hi there,

I am having a heck of a time attempting to open a form and filter it automatically based on a date derived from a control on another form.

I am using the DoCmd.OpenForm method and applying and applying criteria to the "WhereCondition".

Initially the code looked like this and worked just fine:

Private Sub GoToPlantSpeciesForm_Click()

Dim stDocName As String
Dim stSiteID As Date
Dim stLinkCriteria As String

stDocName = "PlantSpecies"
stSiteID = Me.SiteID_Header.Value
stLinkCriteria = "[Site_ID] = " & " '" & stSiteID & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit, acDialog

Exit Sub

As you can see, it is taking the ID value from a control on a main form and using it to filter out records on the "PlantSpecies" form.

However, it was determined that we should also filter by a date field common to both tables:

Private Sub GoToPlantSpeciesForm_Click()

Dim stDocName As String
Dim stSiteID As Date
Dim dtSurveyDate As Date
Dim stLinkCriteria As String

stDocName = "PlantSpecies"
stSiteID = Me.SiteID_Header.Value
dtSurveyDate = Me.Survey_Date.Value
stLinkCriteria = "[Site_ID] = " & " '" & stSiteID & "' And [Date] = #" _
& dtSurveyDate & "#"

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit, acDialog

Exit Sub

Now the filter returns nothing.

If I set the date portion of the Where condition to a specific date it works fine, ie:

stLinkCriteria = "[Site_ID] = " & " '" & stSiteID & "' And [Date] = #12/6/2010#"

I'm sure it is some minor syntax thing. I'm not receiving any error message, mind you. The form opens with a filter applied, but showing no records as a result of the filter. Again, this only occurs when I assign a date to a variable and then use that date variable in the where condition.

Help! Please! If I had any hair to tear out, I would be doing so!

thanks!

 

[boblarson]

Depending on how your dates are stored/entered (and Regional settings) it may need be explicitly formatted to US Date format. Also, you don't need to use the extra concatenation for the space and quote:

stLinkCriteria = "[Site_ID] = '" & stSiteID & "' And [Date] = #" _
& Format(dtSurveyDate,"mm\/dd\/yyyy") & "#"

or you can incorporate the # signs as well:

[code]
stLinkCriteria = "[Site_ID] = '" & stSiteID & "' And [Date] = " _
& Format(dtSurveyDate,"#mm\/dd\/yyyy#")

[/code]

 

[jbutcher]

Yes, thank you! The date variable had to be formatted properly. It works now!

Cheers!

 

[ravencrt]

Hello
I have a problem when trying to apply a filter to a form on a oc that is running Windows 7 and Office 2007. (On other pc that run Windows Vista or XP I don't have this problem regardless of the office version (2007,2003))

code:
DateStart = InputBox("Enter Start Date " & vbCrLf & vbCrLf & "<dd/mm/yy>")
DateEnd = InputBox("Enter End Date " & vbCrLf & vbCrLf & "<dd/mm/yy>")
Me.Filter = "(InvoiceDate >= #" & datestart & "#) AND (InvoiceDate <= #" & dateend & "#)"
me.filteron=true

When I try to apply this filter on a PC with Windows 7 and Office 2007 the first filter applies ok but the second on try to apply the filter with the date 01/11/1931, and like I said on other pc's that run Vista or XP the second date is interpreted correctly
I really don't know how to overcome this problem

 

[boblarson]

Hello
I have a problem when trying to apply a filter to a form on a oc that is running Windows 7 and Office 2007. (On other pc that run Windows Vista or XP I don't have this problem regardless of the office version (2007,2003))

code:
DateStart = InputBox("Enter Start Date " & vbCrLf & vbCrLf & "<dd/mm/yy>")
DateEnd = InputBox("Enter End Date " & vbCrLf & vbCrLf & "<dd/mm/yy>")
Me.Filter = "(InvoiceDate >= #" & datestart & "#) AND (InvoiceDate <= #" & dateend & "#)"
me.filteron=true

When I try to apply this filter on a PC with Windows 7 and Office 2007 the first filter applies ok but the second on try to apply the filter with the date 01/11/1931, and like I said on other pc's that run Vista or XP the second date is interpreted correctly
I really don't know how to overcome this problem

You didn't read the solution to the problem that was the original post here did you? You currently have your dates formatted incorrectly. You need to use US date format (thank Microsoft for that) or use a non-ambiguous format. So your code should be:

[COLOR=black][FONT=Verdana]DateStart = [COLOR=red]Format([/COLOR]InputBox("Enter Start Date " & vbCrLf & vbCrLf & "<dd/mm/yy>")[COLOR=red], "#mm\/dd\/yyyy#")[/COLOR][/FONT][/COLOR]
[FONT=Verdana][COLOR=black]DateEnd = [COLOR=red]Format([/COLOR]InputBox("Enter End Date " & vbCrLf & vbCrLf & "<dd/mm/yy>")[/COLOR][COLOR=red], "#mm\/dd\/yyyy#")[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Me.Filter = "InvoiceDate >= " & DateStart & " AND InvoiceDate <= " & DateEnd[/COLOR][/FONT]
[COLOR=black][FONT=Verdana]me.filteron=true[/FONT][/COLOR]

 

[ravencrt]

It works, thank you very much, I'm sorry about not reading the previous post, I actually just glanced over them and thought that it is not what I’m looking for, sorry again.
Now I have another question (I hope this one isn’t as silly as the last one  )
With the filter that I have created above I want to run a sql query and return data. But again I face the same problem with the regional settings.
I saved the filter in a global variable and use it in another procedure.
[code:]
strLinkCriteria1 = Replace(filtru, Chr(34), Chr(39))
strLinkCriteria1 = Replace(strLinkCriteria1, "#", "'")
strLinkCriteria1 = Replace(strLinkCriteria1, False, 0)
strLinkCriteria1 = Replace(strLinkCriteria1, True, 1)
strLinkCriteria1 = Replace(strLinkCriteria1, "Alike", "Like")
strLinkCriteria1 = Replace(strLinkCriteria1, "Date()", "GetDate()")

strSQL = "SELECT SUM(isnull(TotalHTVA, 0)+ isnull(ExtraDiscountValue, 0)) as TotHTVA FROM qryInvoices_List " & _
"WHERE " & strLinkCriteria1 & " AND NOT Relations_ID = '1948'"

Dim cn As ADODB.Connection, rst As ADODB.Recordset
Set cn = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open strSQL, cn, adOpenStatic, adLockReadOnly

Me.TotalCalc = rst!totHTVA

rst.Close
cn.Close
Set rst = Nothing
Set cn = Nothing

[\code]

Filtru is the global variable in which I saved the filter.
This is a example of the filter that is stored in the strlinkcriteria1

"(InvoiceDate >= '01/01/2011') AND (InvoiceDate <= '01/31/2011 23:59:59 ')"

How can I make this work on any pc regardless of the regional settings?

 

[ravencrt]

I just found out how to solve this problem. I can pass a sql string with dates working on any computer regarding of the regional setting. I have to format the date "yyyymmdd". If you know any other solution please let me know.