Great. Good progress.
In the context of a WHERE clause, SQL statement, or literal VBA value,
you
must use the mm/dd/yyyy format, regardless of your regional settings.
See:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
For an example of how to format the dates into the WHERE clause, see
example
2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
To explain the cryptic date format string inside the Format() function:
- Backslash indicates the followgin character is a literal, so \# is
interpreted by Format as #.
- The backslash before the forward slash again specifies a literal
forward
slash. If you don't do that, the Format() function interprets the forward
slash according to the date separator specified in your regional
settings,
which can give unreliable results.
If you format a date as "\#MM\/YY\#", you get: hash, month, slash, year,
hash. That is NOT a valid date in Access, so is useless in a WHERE
string.
You can demonstrate that in the Immediate Window (Ctrl+G) with:
? IsDate(Format(date, "\#MM\/YY\#"))
which returns False.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Hi Allen,
OK - I got every combo to work now except for limiting between the 2
dates
would you possibly be able to advise?
I'm attempting the short date format first "\#dd\/mm\/yyyy\#" but I
get a
syntax error '(DateofEntry>#29/06/)'
The second date format that I want to use is just "\#MM\/YY\#" - I
took
the
syntax from the website but I don't really understand it - can you
advise? -
I put the code I used for the short date below:
If IsNull(Me.EntryDateFirst) Then
If Not IsNull(Me.EntryDateLast) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.EntryDateLast,
conDateFormat)
End If
Else
If IsNull(Me.EntryDateLast) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.EntryDateFirst,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " &
Format(Me.EntryDateFirst,
conDateFormat) _
& " And " & Format(Me.EntryDateLast, conDateFormat)
End If
End If
--
Kind Regards
Hazel
:
First, it won't work if the report is already open.
Next, you have lots of combos there.
Break it down to just 2 or 3 and get that working.
You can then build it up again from there.
Particularly, omit the date fields (if you have any.) They need # as
the
delimiter.
Immediately before the OpenReport line, add:
Debug.Print strWhere
This will give you a print in the Immediate Window (Ctrl+G) of the
filter
being applied.
Hopefully those ideas will get you debugging your issue.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
message
Hi Allen,
Sorry - got a bit lost again,
My code seems to run OK but the records arent restricting? - not
sure
if
I've done everything that is required in my report - do I need to
add
dependency to the controls on the report?? -please find the code
below
does
this look correct?
rivate Sub OpenReportAll_Click()
Dim stDocName As String
Dim lngLen As Long
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conMissFormat = "mm/yy"
Const conDateFormat = "dd/mm/yyyy"
stDocName = "RptAll"
strField = "DateofEntry"
If Not IsNull(CustomerNameCombo) Then
strWhere = strWhere & "([CustomerName] = """ &
Me.CustomerNameCombo
& """) AND "
End If
If Not IsNull(Me.SiteIDCombo) Then
strWhere = strWhere & "([SiteID] = " & Me.SiteIDCombo & ")
AND "
End If
If Not IsNull(Me.SiteNameCombo) Then
strWhere = strWhere & "([SiteName] = """ & Me.SiteNameCombo &
""")
AND "
End If
If Not IsNull(Me.ReportingSiteIDCombo) Then
strWhere = strWhere & "([ReportingSiteID] = " &
Me.ReportingSiteIDCombo & ") AND "
End If
If Not IsNull(Me.OrgAccountCombo) Then
strWhere = strWhere & "([OrgAccount] = """ &
Me.OrgAccountCombo
&
""") AND "
End If
If Not IsNull(Me.SLAMissTypeCombo) Then
strWhere = strWhere & "([SLAMissType] = """ &
Me.SLAMissTypeCombo &
""") AND "
End If
If Not IsNull(Me.ConsecutiveMonthNumberCombo) Then
strWhere = strWhere & "([ConsecutiveMonthNumber] = " &
Me.ConsecutiveMonthNumberCombo & ") AND "
End If
If IsNull(Me.EntryDateFirst) Then
If Not IsNull(Me.EntryDateLast) Then 'End date, but no
start.
strWhere = strField & " < " & Format(Me.EntryDateLast,
conDateFormat)
End If
Else
If IsNull(Me.EntryDateLast) Then 'Start date, but no
End.
strWhere = strField & " > " & Format(Me.EntryDateFirst,
conDateFormat)
Else 'Both start and end
dates.
strWhere = strField & " Between " &
Format(Me.EntryDateFirst,
conDateFormat) _
& " And " & Format(Me.EntryDateLast, conDateFormat)
End If
End If
If IsNull(Me.MissDateFirst) Then
If Not IsNull(Me.MissDateLast) Then 'End date, but no
start.
strWhere = strField & " < " & Format(Me.MissDateLast,
conMissFormat)
End If
Else
If IsNull(Me.MissDateLast) Then 'Start date, but no
End.
strWhere = strField & " > " & Format(Me.MissDateFirst,
conMissFormat)
Else 'Both start and end
dates.
strWhere = strField & " Between " &
Format(Me.MissDateFirst,
conMissFormat) _
& " And " & Format(Me.MissDateLast, conMissFormat)
End If
End If
'Chop off the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If
'Open the report
DoCmd.OpenReport stDocName, acViewPreview, , strWhere
End Sub
--
Kind Regards
Hazel
:
Leave the criteria out of the query all together.
Instead, use a command button on your form to open the report. In
its
Click
event, build up the WhereCondition string based on the combos that
are
not
blank.
This example shows how to build up the string easily, by adding the
AND
to
the end of each one, and then chopping off the trailing AND at the
end:
Private Sub cmdPreview_Click()
dim strWhere As String
dim lngLen As Long
If Not IsNull(Me.Combo1) Then
strWhere = strWhere & "([Field1] = " & Me.Combo1 & ") AND "
End If
If Not IsNull(Me.Combo2) Then
strWhere = strWhere & "([Field2] = """ & Me.Combo2 & """)
AND
"
End If
'etc for other combos
'Chop off the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If
'Open the report
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub
Note the extra quotes for Field2. That's how to handle a Text type
field.
Date fields need # as the delimiter. There's an example of that at:
http://allenbrowne.com/casu-08.html
message
Hi there - hope somebody can help?
I've created a query to base a report on and I want the query to
limit
the
lines returned based on what is selected in several unbound combo
boxes
in
a
form I created.
I can make this work if the combo box on the form has a chosen
value -
but
how can I do this if the combo box control is empty??
ie. if no criteria was chosen in the combo box I want the query
to
return
all records,
The reason for this is I want a more flexible report which can be
limited
at
many levels,
Thanks for anyones advice
