Searching between two dates

I changed the date format back to the original and it still filtered out some
data and when dates were entered but not the correct ones, I expect this is
due to the international dates again. How do I format the original code so
that access accepts dd/mm/yy?

Thank you

"Douglas J. Steele" wrote:

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

Have you bothered to read the articles I cited?

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


"rjw24" wrote in message
...
I changed the date format back to the original and it still filtered out
some
data and when dates were entered but not the correct ones, I expect this
is
due to the international dates again. How do I format the original code so
that access accepts dd/mm/yy?

Thank you

"Douglas J. Steele" wrote:

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

Yes, but as I am not fluent on VBA i had trouble getting my head round them
but think I did in the end, thanks for the help

"Douglas J. Steele" wrote:

Have you bothered to read the articles I cited?

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


"rjw24" wrote in message
...
I changed the date format back to the original and it still filtered out
some
data and when dates were entered but not the correct ones, I expect this
is
due to the international dates again. How do I format the original code so
that access accepts dd/mm/yy?

Thank you

"Douglas J. Steele" wrote:

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