Searching between two dates

I have used Allen Brownes search form to create one for myself and it works
swimmingly except the date search. I want to include two unbound text boxes
for searching dates (from and to). Is it just me or does the example database
not include the code for doing such a task. Just help with a pointer in the
right direction of how to write the code for this search would be helpful.

I have managed to figure out how to get the form to search for one particular
date but how would i get a search form to search between two dates. The
following code was supplied on the example...

'Date field example. Use the format string to add the # delimiters and get
the right international format.

If Not IsNull(Me.txtEntryFrom) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryFrom, conJetDate)
& ") AND "
End If

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo, conJetDate) &
") AND "
End If


'************************************************* **********************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'************************************************* **********************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "You did not enter any search
criteria."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub


Thanks

Don't know whether it's your typo or Allen's, but I would expect it to be

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo, conJetDate) &
") AND "
End If

(a change from = to =)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
I have used Allen Brownes search form to create one for myself and it works
swimmingly except the date search. I want to include two unbound text
boxes
for searching dates (from and to). Is it just me or does the example
database
not include the code for doing such a task. Just help with a pointer in
the
right direction of how to write the code for this search would be helpful.

I have managed to figure out how to get the form to search for one
particular
date but how would i get a search form to search between two dates. The
following code was supplied on the example...

'Date field example. Use the format string to add the # delimiters and
get
the right international format.

If Not IsNull(Me.txtEntryFrom) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryFrom,
conJetDate)
& ") AND "
End If

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo, conJetDate)
&
") AND "
End If



'************************************************* **********************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'************************************************* **********************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "You did not enter any search
criteria."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub


Thanks

This is the code supplied in Allens database concerning the date search:

'Date field example. Use the format string to add the # delimiters and get
the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] = " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([EnteredOn] " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

and this is the code I have used (similar except the fields have changed):

If Not IsNull(Me.txtEntryFrom) Then
strWhere = strWhere & "([Entry Date] = " & Format(Me.txtEntryFrom,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEntryTo) Then 'Less than the next day.
strWhere = strWhere & "([Entry Date] " & Format(Me.txtEntryTo,
conJetDate) & ") AND "
End If


When I search between two dates where records should appear in the results,
nothing appears.


"Douglas J. Steele" wrote:

Don't know whether it's your typo or Allen's, but I would expect it to be

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo, conJetDate) &
") AND "
End If

(a change from = to =)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
I have used Allen Brownes search form to create one for myself and it works
swimmingly except the date search. I want to include two unbound text
boxes
for searching dates (from and to). Is it just me or does the example
database
not include the code for doing such a task. Just help with a pointer in
the
right direction of how to write the code for this search would be helpful.

I have managed to figure out how to get the form to search for one
particular
date but how would i get a search form to search between two dates. The
following code was supplied on the example...

'Date field example. Use the format string to add the # delimiters and
get
the right international format.

If Not IsNull(Me.txtEntryFrom) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryFrom,
conJetDate)
& ") AND "
End If

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo, conJetDate)
&
") AND "
End If



'************************************************* **********************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'************************************************* **********************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "You did not enter any search
criteria."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub


Thanks

What's the actual value of strWhere when you're done with it?



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
This is the code supplied in Allens database concerning the date search:

'Date field example. Use the format string to add the # delimiters and get
the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] = " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([EnteredOn] " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

and this is the code I have used (similar except the fields have changed):

If Not IsNull(Me.txtEntryFrom) Then
strWhere = strWhere & "([Entry Date] = " & Format(Me.txtEntryFrom,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEntryTo) Then 'Less than the next day.
strWhere = strWhere & "([Entry Date] " & Format(Me.txtEntryTo,
conJetDate) & ") AND "
End If


When I search between two dates where records should appear in the
results,
nothing appears.


"Douglas J. Steele" wrote:

Don't know whether it's your typo or Allen's, but I would expect it to be

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo,
conJetDate) &
") AND "
End If

(a change from = to =)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
I have used Allen Brownes search form to create one for myself and it
works
swimmingly except the date search. I want to include two unbound text
boxes
for searching dates (from and to). Is it just me or does the example
database
not include the code for doing such a task. Just help with a pointer in
the
right direction of how to write the code for this search would be
helpful.

I have managed to figure out how to get the form to search for one
particular
date but how would i get a search form to search between two dates. The
following code was supplied on the example...

'Date field example. Use the format string to add the # delimiters and
get
the right international format.

If Not IsNull(Me.txtEntryFrom) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryFrom,
conJetDate)
& ") AND "
End If

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo,
conJetDate)
&
") AND "
End If



'************************************************* **********************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'************************************************* **********************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "You did not enter any
search
criteria."
Else 'Yep: there is something there, so remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub


Thanks

the value that is typed in to the unbound box named 'txt.EntryFrom' or
'txt.EntryTo' on the search form.

"Douglas J. Steele" wrote:

What's the actual value of strWhere when you're done with it?



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
This is the code supplied in Allens database concerning the date search:

'Date field example. Use the format string to add the # delimiters and get
the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] = " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([EnteredOn] " & Format(Me.txtEndDate + 1,
conJetDate) & ") AND "
End If

and this is the code I have used (similar except the fields have changed):

If Not IsNull(Me.txtEntryFrom) Then
strWhere = strWhere & "([Entry Date] = " & Format(Me.txtEntryFrom,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEntryTo) Then 'Less than the next day.
strWhere = strWhere & "([Entry Date] " & Format(Me.txtEntryTo,
conJetDate) & ") AND "
End If


When I search between two dates where records should appear in the
results,
nothing appears.


"Douglas J. Steele" wrote:

Don't know whether it's your typo or Allen's, but I would expect it to be

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo,
conJetDate) &
") AND "
End If

(a change from = to =)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
I have used Allen Brownes search form to create one for myself and it
works
swimmingly except the date search. I want to include two unbound text
boxes
for searching dates (from and to). Is it just me or does the example
database
not include the code for doing such a task. Just help with a pointer in
the
right direction of how to write the code for this search would be
helpful.

I have managed to figure out how to get the form to search for one
particular
date but how would i get a search form to search between two dates. The
following code was supplied on the example...

'Date field example. Use the format string to add the # delimiters and
get
the right international format.

If Not IsNull(Me.txtEntryFrom) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryFrom,
conJetDate)
& ") AND "
End If

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo,
conJetDate)
&
") AND "
End If



'************************************************* **********************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'************************************************* **********************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "You did not enter any
search
criteria."
Else 'Yep: there is something there, so remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub


Thanks

No, you misunderstood.

Put the statement

Debug.Print strWhere

into your code after all the logic to assign values to strWhere.

Once the code has run, go to the Immediate window (Ctrl-G), copy what's
printed there, and paste it into your reply.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
the value that is typed in to the unbound box named 'txt.EntryFrom' or
'txt.EntryTo' on the search form.

"Douglas J. Steele" wrote:

What's the actual value of strWhere when you're done with it?



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
This is the code supplied in Allens database concerning the date
search:

'Date field example. Use the format string to add the # delimiters and
get
the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] = " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([EnteredOn] " & Format(Me.txtEndDate +
1,
conJetDate) & ") AND "
End If

and this is the code I have used (similar except the fields have
changed):

If Not IsNull(Me.txtEntryFrom) Then
strWhere = strWhere & "([Entry Date] = " &
Format(Me.txtEntryFrom,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEntryTo) Then 'Less than the next day.
strWhere = strWhere & "([Entry Date] " & Format(Me.txtEntryTo,
conJetDate) & ") AND "
End If


When I search between two dates where records should appear in the
results,
nothing appears.


"Douglas J. Steele" wrote:

Don't know whether it's your typo or Allen's, but I would expect it to
be

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo,
conJetDate) &
") AND "
End If

(a change from = to =)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
I have used Allen Brownes search form to create one for myself and it
works
swimmingly except the date search. I want to include two unbound
text
boxes
for searching dates (from and to). Is it just me or does the example
database
not include the code for doing such a task. Just help with a pointer
in
the
right direction of how to write the code for this search would be
helpful.

I have managed to figure out how to get the form to search for one
particular
date but how would i get a search form to search between two dates.
The
following code was supplied on the example...

'Date field example. Use the format string to add the # delimiters
and
get
the right international format.

If Not IsNull(Me.txtEntryFrom) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryFrom,
conJetDate)
& ") AND "
End If

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo,
conJetDate)
&
") AND "
End If



'************************************************* **********************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'************************************************* **********************
'See if the string has more than 5 characters (a trailng " AND ")
to
remove.
lngLen = Len(strWhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "You did not enter any
search
criteria."
Else 'Yep: there is something there, so remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub


Thanks

Sorry, thats when i type 11/06/07 in txt.EntryFrom and 14/06/07 in txt.EntryTo

([Entry Date] = #11/06/2007#) AND ([Entry Date] #14/06/2007#)


"Douglas J. Steele" wrote:

No, you misunderstood.

Put the statement

Debug.Print strWhere

into your code after all the logic to assign values to strWhere.

Once the code has run, go to the Immediate window (Ctrl-G), copy what's
printed there, and paste it into your reply.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
the value that is typed in to the unbound box named 'txt.EntryFrom' or
'txt.EntryTo' on the search form.

"Douglas J. Steele" wrote:

What's the actual value of strWhere when you're done with it?



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
This is the code supplied in Allens database concerning the date
search:

'Date field example. Use the format string to add the # delimiters and
get
the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] = " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([EnteredOn] " & Format(Me.txtEndDate +
1,
conJetDate) & ") AND "
End If

and this is the code I have used (similar except the fields have
changed):

If Not IsNull(Me.txtEntryFrom) Then
strWhere = strWhere & "([Entry Date] = " &
Format(Me.txtEntryFrom,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEntryTo) Then 'Less than the next day.
strWhere = strWhere & "([Entry Date] " & Format(Me.txtEntryTo,
conJetDate) & ") AND "
End If


When I search between two dates where records should appear in the
results,
nothing appears.


"Douglas J. Steele" wrote:

Don't know whether it's your typo or Allen's, but I would expect it to
be

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo,
conJetDate) &
") AND "
End If

(a change from = to =)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
I have used Allen Brownes search form to create one for myself and it
works
swimmingly except the date search. I want to include two unbound
text
boxes
for searching dates (from and to). Is it just me or does the example
database
not include the code for doing such a task. Just help with a pointer
in
the
right direction of how to write the code for this search would be
helpful.

I have managed to figure out how to get the form to search for one
particular
date but how would i get a search form to search between two dates.
The
following code was supplied on the example...

'Date field example. Use the format string to add the # delimiters
and
get
the right international format.

If Not IsNull(Me.txtEntryFrom) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryFrom,
conJetDate)
& ") AND "
End If

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo,
conJetDate)
&
") AND "
End If



'************************************************* **********************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'************************************************* **********************
'See if the string has more than 5 characters (a trailng " AND ")
to
remove.
lngLen = Len(strWhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "You did not enter any
search
criteria."
Else 'Yep: there is something there, so remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub


Thanks

Did you change the value of conJetDate from what Allen had? Regardless of
what your regional settings may be, you cannot use dd/mm/yyyy with dates in
Access queries. 14/06/2007 will be correctly interpretted as 14 June, 2007,
but 11/06/2007 is going to be treated as 06 Nov, 2007.

You should probably read Allen Browne's "International Dates in Access"
(referred to in the page you're using, or at
http://www.allenbrowne.com/ser-36.html) , or what I had in my September,
2003 "Access Answers" column in Pinnacle Publication's "Smart Access". (You
can download the column, and sample database, for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html )


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
Sorry, thats when i type 11/06/07 in txt.EntryFrom and 14/06/07 in
txt.EntryTo

([Entry Date] = #11/06/2007#) AND ([Entry Date] #14/06/2007#)


"Douglas J. Steele" wrote:

No, you misunderstood.

Put the statement

Debug.Print strWhere

into your code after all the logic to assign values to strWhere.

Once the code has run, go to the Immediate window (Ctrl-G), copy what's
printed there, and paste it into your reply.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
the value that is typed in to the unbound box named 'txt.EntryFrom' or
'txt.EntryTo' on the search form.

"Douglas J. Steele" wrote:

What's the actual value of strWhere when you're done with it?



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
This is the code supplied in Allens database concerning the date
search:

'Date field example. Use the format string to add the # delimiters
and
get
the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] = " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([EnteredOn] " &
Format(Me.txtEndDate +
1,
conJetDate) & ") AND "
End If

and this is the code I have used (similar except the fields have
changed):

If Not IsNull(Me.txtEntryFrom) Then
strWhere = strWhere & "([Entry Date] = " &
Format(Me.txtEntryFrom,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this
field has times as well as dates.
If Not IsNull(Me.txtEntryTo) Then 'Less than the next day.
strWhere = strWhere & "([Entry Date] " &
Format(Me.txtEntryTo,
conJetDate) & ") AND "
End If


When I search between two dates where records should appear in the
results,
nothing appears.


"Douglas J. Steele" wrote:

Don't know whether it's your typo or Allen's, but I would expect it
to
be

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo,
conJetDate) &
") AND "
End If

(a change from = to =)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
I have used Allen Brownes search form to create one for myself and
it
works
swimmingly except the date search. I want to include two unbound
text
boxes
for searching dates (from and to). Is it just me or does the
example
database
not include the code for doing such a task. Just help with a
pointer
in
the
right direction of how to write the code for this search would be
helpful.

I have managed to figure out how to get the form to search for
one
particular
date but how would i get a search form to search between two
dates.
The
following code was supplied on the example...

'Date field example. Use the format string to add the #
delimiters
and
get
the right international format.

If Not IsNull(Me.txtEntryFrom) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryFrom,
conJetDate)
& ") AND "
End If

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo,
conJetDate)
&
") AND "
End If



'************************************************* **********************
'Chop off the trailing " AND ", and use the string as the
form's
Filter.

'************************************************* **********************
'See if the string has more than 5 characters (a trailng " AND
")
to
remove.
lngLen = Len(strWhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "You did not enter
any
search
criteria."
Else 'Yep: there is something there, so
remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub


Thanks

Const conJetDate = "\#dd\/mm\/yyyy\#" That is what I have in my code. I read
Allen's piece on international dates but didnt find it clear about how to
alter it so it works in the UK format. Any advice? But to be honest, I still
dont think the search function is working after I have altered the dates
althoguh I am probably wrong.

"Douglas J. Steele" wrote:

Did you change the value of conJetDate from what Allen had? Regardless of
what your regional settings may be, you cannot use dd/mm/yyyy with dates in
Access queries. 14/06/2007 will be correctly interpretted as 14 June, 2007,
but 11/06/2007 is going to be treated as 06 Nov, 2007.

You should probably read Allen Browne's "International Dates in Access"
(referred to in the page you're using, or at
http://www.allenbrowne.com/ser-36.html) , or what I had in my September,
2003 "Access Answers" column in Pinnacle Publication's "Smart Access". (You
can download the column, and sample database, for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html )


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
Sorry, thats when i type 11/06/07 in txt.EntryFrom and 14/06/07 in
txt.EntryTo

([Entry Date] = #11/06/2007#) AND ([Entry Date] #14/06/2007#)


"Douglas J. Steele" wrote:

No, you misunderstood.

Put the statement

Debug.Print strWhere

into your code after all the logic to assign values to strWhere.

Once the code has run, go to the Immediate window (Ctrl-G), copy what's
printed there, and paste it into your reply.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
the value that is typed in to the unbound box named 'txt.EntryFrom' or
'txt.EntryTo' on the search form.

"Douglas J. Steele" wrote:

What's the actual value of strWhere when you're done with it?



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
This is the code supplied in Allens database concerning the date
search:

'Date field example. Use the format string to add the # delimiters
and
get
the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] = " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([EnteredOn] " &
Format(Me.txtEndDate +
1,
conJetDate) & ") AND "
End If

and this is the code I have used (similar except the fields have
changed):

If Not IsNull(Me.txtEntryFrom) Then
strWhere = strWhere & "([Entry Date] = " &
Format(Me.txtEntryFrom,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since
this
field has times as well as dates.
If Not IsNull(Me.txtEntryTo) Then 'Less than the next day.
strWhere = strWhere & "([Entry Date] " &
Format(Me.txtEntryTo,
conJetDate) & ") AND "
End If


When I search between two dates where records should appear in the
results,
nothing appears.


"Douglas J. Steele" wrote:

Don't know whether it's your typo or Allen's, but I would expect it
to
be

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo,
conJetDate) &
") AND "
End If

(a change from = to =)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
I have used Allen Brownes search form to create one for myself and
it
works
swimmingly except the date search. I want to include two unbound
text
boxes
for searching dates (from and to). Is it just me or does the
example
database
not include the code for doing such a task. Just help with a
pointer
in
the
right direction of how to write the code for this search would be
helpful.

I have managed to figure out how to get the form to search for
one
particular
date but how would i get a search form to search between two
dates.
The
following code was supplied on the example...

'Date field example. Use the format string to add the #
delimiters
and
get
the right international format.

If Not IsNull(Me.txtEntryFrom) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryFrom,
conJetDate)
& ") AND "
End If

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo,
conJetDate)
&
") AND "
End If



'************************************************* **********************
'Chop off the trailing " AND ", and use the string as the
form's
Filter.

'************************************************* **********************
'See if the string has more than 5 characters (a trailng " AND
")
to
remove.
lngLen = Len(strWhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "You did not enter
any
search
criteria."
Else 'Yep: there is something there, so
remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub


Thanks

As both Allen & I tried to explain in our articles, you MUST use a date
format that Access will recognize. Access prefers mm/dd/yyyy, but it's happy
with unambiguous formats such as yyyy-mm-dd or dd mmm yyyy. Regardless of
what your Regional Settings may have the Short Date format set to, Access
will ALWAYS treat nn/nn/nnnn as mm/dd/yyyy in queries, unless the day is
greater than 12 (since there's no 13th month).

Did you change the constant back to its proper value and try the code?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
Const conJetDate = "\#dd\/mm\/yyyy\#" That is what I have in my code. I
read
Allen's piece on international dates but didnt find it clear about how to
alter it so it works in the UK format. Any advice? But to be honest, I
still
dont think the search function is working after I have altered the dates
althoguh I am probably wrong.

"Douglas J. Steele" wrote:

Did you change the value of conJetDate from what Allen had? Regardless of
what your regional settings may be, you cannot use dd/mm/yyyy with dates
in
Access queries. 14/06/2007 will be correctly interpretted as 14 June,
2007,
but 11/06/2007 is going to be treated as 06 Nov, 2007.

You should probably read Allen Browne's "International Dates in Access"
(referred to in the page you're using, or at
http://www.allenbrowne.com/ser-36.html) , or what I had in my September,
2003 "Access Answers" column in Pinnacle Publication's "Smart Access".
(You
can download the column, and sample database, for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html )


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
Sorry, thats when i type 11/06/07 in txt.EntryFrom and 14/06/07 in
txt.EntryTo

([Entry Date] = #11/06/2007#) AND ([Entry Date] #14/06/2007#)


"Douglas J. Steele" wrote:

No, you misunderstood.

Put the statement

Debug.Print strWhere

into your code after all the logic to assign values to strWhere.

Once the code has run, go to the Immediate window (Ctrl-G), copy
what's
printed there, and paste it into your reply.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
the value that is typed in to the unbound box named 'txt.EntryFrom'
or
'txt.EntryTo' on the search form.

"Douglas J. Steele" wrote:

What's the actual value of strWhere when you're done with it?



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
This is the code supplied in Allens database concerning the date
search:

'Date field example. Use the format string to add the #
delimiters
and
get
the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] = " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day"
since
this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([EnteredOn] " &
Format(Me.txtEndDate +
1,
conJetDate) & ") AND "
End If

and this is the code I have used (similar except the fields have
changed):

If Not IsNull(Me.txtEntryFrom) Then
strWhere = strWhere & "([Entry Date] = " &
Format(Me.txtEntryFrom,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day"
since
this
field has times as well as dates.
If Not IsNull(Me.txtEntryTo) Then 'Less than the next day.
strWhere = strWhere & "([Entry Date] " &
Format(Me.txtEntryTo,
conJetDate) & ") AND "
End If


When I search between two dates where records should appear in
the
results,
nothing appears.


"Douglas J. Steele" wrote:

Don't know whether it's your typo or Allen's, but I would expect
it
to
be

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo,
conJetDate) &
") AND "
End If

(a change from = to =)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"rjw24" wrote in message
...
I have used Allen Brownes search form to create one for myself
and
it
works
swimmingly except the date search. I want to include two
unbound
text
boxes
for searching dates (from and to). Is it just me or does the
example
database
not include the code for doing such a task. Just help with a
pointer
in
the
right direction of how to write the code for this search would
be
helpful.

I have managed to figure out how to get the form to search for
one
particular
date but how would i get a search form to search between two
dates.
The
following code was supplied on the example...

'Date field example. Use the format string to add the #
delimiters
and
get
the right international format.

If Not IsNull(Me.txtEntryFrom) Then
strWhere = "([Entry Date] = " &
Format(Me.txtEntryFrom,
conJetDate)
& ") AND "
End If

If Not IsNull(Me.txtEntryTo) Then
strWhere = "([Entry Date] = " & Format(Me.txtEntryTo,
conJetDate)
&
") AND "
End If



'************************************************* **********************
'Chop off the trailing " AND ", and use the string as the
form's
Filter.

'************************************************* **********************
'See if the string has more than 5 characters (a trailng "
AND
")
to
remove.
lngLen = Len(strWhere) - 5
If lngLen = 0 Then 'Nah: there was nothing in the
string.
MsgBox "No criteria", vbInformation, "You did not enter
any
search
criteria."
Else 'Yep: there is something there, so
remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next
line.
Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub


Thanks