Can't use dd/mm/yyyy format

[Paul B.]

I have since found that I cannot use the dd/mm/yyyy date format, so I am now
wondering if I can use an SQL statement to apply criteria to open a form to
specific records.

I need to replace the following code with an SQL statement if possible

----------------------------------------------------
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmDispositionHistory"

If IsNull([txtStartDate]) And IsNull([txtEndDate]) Then
stLinkCriteria = "[VehicleNumber]=" & Me![VehicleNumber]

Else
stLinkCriteria = "VehicleNumber='" & Me!VehicleNumber & "' And " &
"DispositionDate BETWEEN #" & Format$(Me!txtStartDate, "dd/mm/yyyy") & "#
AND # " & Format$(Me!txtEndDate, "dd/mm/yyyy") & "#"

End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

-----------------------------------------------------------------------

Here is the value of stLinkCriteria:

stLinkCriteria = "VehicleNumber='4091' AND DispositionDate BETWEEN
#01/12/2004# AND #04/01/2005#"


When the DoCmd.OpenForm part runs, this causes an error that states
OpenForm action was cancelled. I am assuming that it has something to do with
the date format, because without selecting dates, the form opens just fine.

If there is some other way of doing this, I would appreciate a nudge in that
direction.

Cheers

 

[Douglas J. Steele]

Try changing it to Format$(Me!txtEndDate, "mm/dd/yyyy")

Regardless of what your regional settings might be, Access expects
mm/dd/yyyy format in SQL statements. Access is always going to interpret
your dates as BETWEEN 12 Jan, 2004 AND 01 Apr, 2004. The only time this
won't be the case is if the day is greater than 13.

For more information about International dates, check Allen Browne's
"International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html or what I have in my
September 2003 Access Answers column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be downloaded
at http://members.rogers.com/douglas.j.steele/SmartAccess.html)


There's another difference in your code, though. You're including quotes
around the vehicle number in the second case, whereas you aren't in the
first case.

 

[Paul B.]

Thanks for the rely Douglas,

I changed the date format as you suggested, and fixed the vehiclenumber bit.
However, I am still getting the error OpenForm action was cancelled.

I am not that sure of how to create and use a recordset, did some reading on
it last night, but would that be a better way of getting the data filter??

Cheers

Try changing it to Format$(Me!txtEndDate, "mm/dd/yyyy")

Regardless of what your regional settings might be, Access expects
mm/dd/yyyy format in SQL statements. Access is always going to interpret
your dates as BETWEEN 12 Jan, 2004 AND 01 Apr, 2004. The only time this
won't be the case is if the day is greater than 13.

For more information about International dates, check Allen Browne's
"International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html or what I have in my
September 2003 Access Answers column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be downloaded
at http://members.rogers.com/douglas.j.steele/SmartAccess.html)


There's another difference in your code, though. You're including quotes
around the vehicle number in the second case, whereas you aren't in the
first case.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have since found that I cannot use the dd/mm/yyyy date format, so I am now
wondering if I can use an SQL statement to apply criteria to open a form to
specific records.

I need to replace the following code with an SQL statement if possible

----------------------------------------------------
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmDispositionHistory"

If IsNull([txtStartDate]) And IsNull([txtEndDate]) Then
stLinkCriteria = "[VehicleNumber]=" & Me![VehicleNumber]

Else
stLinkCriteria = "VehicleNumber='" & Me!VehicleNumber & "' And " &
"DispositionDate BETWEEN #" & Format$(Me!txtStartDate, "dd/mm/yyyy") & "#
AND # " & Format$(Me!txtEndDate, "dd/mm/yyyy") & "#"

End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

-----------------------------------------------------------------------

Here is the value of stLinkCriteria:

stLinkCriteria = "VehicleNumber='4091' AND DispositionDate BETWEEN
#01/12/2004# AND #04/01/2005#"


When the DoCmd.OpenForm part runs, this causes an error that states
OpenForm action was cancelled. I am assuming that it has something to do with
the date format, because without selecting dates, the form opens just fine.

If there is some other way of doing this, I would appreciate a nudge in that
direction.

Cheers

 

[Douglas J. Steele]

It would probably help if you pasted the actual code here. Based on what
you've said you did, I can't think of any reason why that error would be
occurring.

I can't see how creating a recordset would work in this situation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the rely Douglas,

I changed the date format as you suggested, and fixed the vehiclenumber
bit.
However, I am still getting the error OpenForm action was cancelled.

I am not that sure of how to create and use a recordset, did some reading
on
it last night, but would that be a better way of getting the data filter??

Cheers

Try changing it to Format$(Me!txtEndDate, "mm/dd/yyyy")

Regardless of what your regional settings might be, Access expects
mm/dd/yyyy format in SQL statements. Access is always going to interpret
your dates as BETWEEN 12 Jan, 2004 AND 01 Apr, 2004. The only time this
won't be the case is if the day is greater than 13.

For more information about International dates, check Allen Browne's
"International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html or what I have in my
September 2003 Access Answers column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be
downloaded
at http://members.rogers.com/douglas.j.steele/SmartAccess.html)


There's another difference in your code, though. You're including quotes
around the vehicle number in the second case, whereas you aren't in the
first case.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have since found that I cannot use the dd/mm/yyyy date format, so I
am now
wondering if I can use an SQL statement to apply criteria to open a
form to
specific records.

I need to replace the following code with an SQL statement if possible

----------------------------------------------------
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmDispositionHistory"

If IsNull([txtStartDate]) And IsNull([txtEndDate]) Then
stLinkCriteria = "[VehicleNumber]=" & Me![VehicleNumber]

Else
stLinkCriteria = "VehicleNumber='" & Me!VehicleNumber & "' And " &
"DispositionDate BETWEEN #" & Format$(Me!txtStartDate, "dd/mm/yyyy") &
"#
AND # " & Format$(Me!txtEndDate, "dd/mm/yyyy") & "#"

End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

-----------------------------------------------------------------------

Here is the value of stLinkCriteria:

stLinkCriteria = "VehicleNumber='4091' AND DispositionDate BETWEEN
#01/12/2004# AND #04/01/2005#"


When the DoCmd.OpenForm part runs, this causes an error that states
OpenForm action was cancelled. I am assuming that it has something to
do with
the date format, because without selecting dates, the form opens just fine.

If there is some other way of doing this, I would appreciate a nudge in that
direction.

Cheers

 

[Paul B.]

Thanks for your help Douglas,

I gave up trying to write the code. What I did was create a query using the
criteria I was trying to code, then opened the form bound to the query. It
was easier than I thought....just the learning curve to find these solutions
is quite steep.

I even searched the knowledge base looking for samples of how to apply two
criteria to the ApplyFilter option, but drew a blank there too.

The code I was trying was:

------------------------------------
(omitted stuff)

If IsNull([txtStartDate]) And IsNull([txtEndDate]) Then

stLinkCriteria = "[VehicleNumber]=" & Me![VehicleNumber]

Else

stLinkCriteria = "VehicleNumber=" & Me![VehicleNumber] & " And " &
"DispositionDate BETWEEN #" & Format$(Me!txtStartDate, "mm/dd/yyyy") & "#
AND # " & Format$(Me!txtEndDate, "mm/dd/yyyy") & "#"

End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

--------------------------------

This stopped the OpenForm Action was cancelled message, but resulted it a
Type Mismatch error message.

Anyway, problem solved, or should I say resolved since I didn't really solve
the issue in the first place, just a different solution.

Cheers

It would probably help if you pasted the actual code here. Based on what
you've said you did, I can't think of any reason why that error would be
occurring.

I can't see how creating a recordset would work in this situation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the rely Douglas,

I changed the date format as you suggested, and fixed the vehiclenumber
bit.
However, I am still getting the error OpenForm action was cancelled.

I am not that sure of how to create and use a recordset, did some reading
on
it last night, but would that be a better way of getting the data filter??

Cheers

Try changing it to Format$(Me!txtEndDate, "mm/dd/yyyy")

Regardless of what your regional settings might be, Access expects
mm/dd/yyyy format in SQL statements. Access is always going to interpret
your dates as BETWEEN 12 Jan, 2004 AND 01 Apr, 2004. The only time this
won't be the case is if the day is greater than 13.

For more information about International dates, check Allen Browne's
"International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html or what I have in my
September 2003 Access Answers column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be
downloaded
at http://members.rogers.com/douglas.j.steele/SmartAccess.html)


There's another difference in your code, though. You're including quotes
around the vehicle number in the second case, whereas you aren't in the
first case.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have since found that I cannot use the dd/mm/yyyy date format, so I
am
now
wondering if I can use an SQL statement to apply criteria to open a
form
to
specific records.

I need to replace the following code with an SQL statement if possible

----------------------------------------------------
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmDispositionHistory"

If IsNull([txtStartDate]) And IsNull([txtEndDate]) Then
stLinkCriteria = "[VehicleNumber]=" & Me![VehicleNumber]

Else
stLinkCriteria = "VehicleNumber='" & Me!VehicleNumber & "' And " &
"DispositionDate BETWEEN #" & Format$(Me!txtStartDate, "dd/mm/yyyy") &
"#
AND # " & Format$(Me!txtEndDate, "dd/mm/yyyy") & "#"

End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

-----------------------------------------------------------------------

Here is the value of stLinkCriteria:

stLinkCriteria = "VehicleNumber='4091' AND DispositionDate BETWEEN
#01/12/2004# AND #04/01/2005#"


When the DoCmd.OpenForm part runs, this causes an error that states
OpenForm action was cancelled. I am assuming that it has something to
do
with
the date format, because without selecting dates, the form opens just
fine.

If there is some other way of doing this, I would appreciate a nudge in
that
direction.

Cheers

 

[Douglas J. Steele]

What data types are VehicleNumber and DispositionDate? What you had looks
correct if the former is numeric data type and the latter a Date/Time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for your help Douglas,

I gave up trying to write the code. What I did was create a query using
the
criteria I was trying to code, then opened the form bound to the query. It
was easier than I thought....just the learning curve to find these
solutions
is quite steep.

I even searched the knowledge base looking for samples of how to apply two
criteria to the ApplyFilter option, but drew a blank there too.

The code I was trying was:

------------------------------------
(omitted stuff)

If IsNull([txtStartDate]) And IsNull([txtEndDate]) Then

stLinkCriteria = "[VehicleNumber]=" & Me![VehicleNumber]

Else

stLinkCriteria = "VehicleNumber=" & Me![VehicleNumber] & " And " &
"DispositionDate BETWEEN #" & Format$(Me!txtStartDate, "mm/dd/yyyy") & "#
AND # " & Format$(Me!txtEndDate, "mm/dd/yyyy") & "#"

End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

--------------------------------

This stopped the OpenForm Action was cancelled message, but resulted it a
Type Mismatch error message.

Anyway, problem solved, or should I say resolved since I didn't really
solve
the issue in the first place, just a different solution.

Cheers

It would probably help if you pasted the actual code here. Based on what
you've said you did, I can't think of any reason why that error would be
occurring.

I can't see how creating a recordset would work in this situation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the rely Douglas,

I changed the date format as you suggested, and fixed the vehiclenumber
bit.
However, I am still getting the error OpenForm action was cancelled.

I am not that sure of how to create and use a recordset, did some
reading
on
it last night, but would that be a better way of getting the data
filter??

Cheers


:

Try changing it to Format$(Me!txtEndDate, "mm/dd/yyyy")

Regardless of what your regional settings might be, Access expects
mm/dd/yyyy format in SQL statements. Access is always going to
interpret
your dates as BETWEEN 12 Jan, 2004 AND 01 Apr, 2004. The only time
this
won't be the case is if the day is greater than 13.

For more information about International dates, check Allen Browne's
"International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html or what I have in
my
September 2003 Access Answers column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be
downloaded
at http://members.rogers.com/douglas.j.steele/SmartAccess.html)


There's another difference in your code, though. You're including
quotes
around the vehicle number in the second case, whereas you aren't in
the
first case.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have since found that I cannot use the dd/mm/yyyy date format, so
I
am
now
wondering if I can use an SQL statement to apply criteria to open a
form
to
specific records.

I need to replace the following code with an SQL statement if
possible

----------------------------------------------------
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmDispositionHistory"

If IsNull([txtStartDate]) And IsNull([txtEndDate]) Then
stLinkCriteria = "[VehicleNumber]=" & Me![VehicleNumber]

Else
stLinkCriteria = "VehicleNumber='" & Me!VehicleNumber & "' And "
&
"DispositionDate BETWEEN #" & Format$(Me!txtStartDate, "dd/mm/yyyy")
&
"#
AND # " & Format$(Me!txtEndDate, "dd/mm/yyyy") & "#"

End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

-----------------------------------------------------------------------

Here is the value of stLinkCriteria:

stLinkCriteria = "VehicleNumber='4091' AND DispositionDate BETWEEN
#01/12/2004# AND #04/01/2005#"


When the DoCmd.OpenForm part runs, this causes an error that states
OpenForm action was cancelled. I am assuming that it has something
to
do
with
the date format, because without selecting dates, the form opens
just
fine.

If there is some other way of doing this, I would appreciate a nudge
in
that
direction.

Cheers

 

[Paul B.]

You are correct. VehicleNumber is 5 digits, and DispositionDate is in short
date format. I tried everything I could find, but kept getting the error
messages.

Cheers

What data types are VehicleNumber and DispositionDate? What you had looks
correct if the former is numeric data type and the latter a Date/Time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for your help Douglas,

I gave up trying to write the code. What I did was create a query using
the
criteria I was trying to code, then opened the form bound to the query. It
was easier than I thought....just the learning curve to find these
solutions
is quite steep.

I even searched the knowledge base looking for samples of how to apply two
criteria to the ApplyFilter option, but drew a blank there too.

The code I was trying was:

------------------------------------
(omitted stuff)

If IsNull([txtStartDate]) And IsNull([txtEndDate]) Then

stLinkCriteria = "[VehicleNumber]=" & Me![VehicleNumber]

Else

stLinkCriteria = "VehicleNumber=" & Me![VehicleNumber] & " And " &
"DispositionDate BETWEEN #" & Format$(Me!txtStartDate, "mm/dd/yyyy") & "#
AND # " & Format$(Me!txtEndDate, "mm/dd/yyyy") & "#"

End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

--------------------------------

This stopped the OpenForm Action was cancelled message, but resulted it a
Type Mismatch error message.

Anyway, problem solved, or should I say resolved since I didn't really
solve
the issue in the first place, just a different solution.

Cheers

It would probably help if you pasted the actual code here. Based on what
you've said you did, I can't think of any reason why that error would be
occurring.

I can't see how creating a recordset would work in this situation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Thanks for the rely Douglas,

I changed the date format as you suggested, and fixed the vehiclenumber
bit.
However, I am still getting the error OpenForm action was cancelled.

I am not that sure of how to create and use a recordset, did some
reading
on
it last night, but would that be a better way of getting the data
filter??

Cheers


:

Try changing it to Format$(Me!txtEndDate, "mm/dd/yyyy")

Regardless of what your regional settings might be, Access expects
mm/dd/yyyy format in SQL statements. Access is always going to
interpret
your dates as BETWEEN 12 Jan, 2004 AND 01 Apr, 2004. The only time
this
won't be the case is if the day is greater than 13.

For more information about International dates, check Allen Browne's
"International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html or what I have in
my
September 2003 Access Answers column for Pinnacle Publication's "Smart
Access" newsletter. (The column and accompanying database can be
downloaded
at http://members.rogers.com/douglas.j.steele/SmartAccess.html)


There's another difference in your code, though. You're including
quotes
around the vehicle number in the second case, whereas you aren't in
the
first case.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have since found that I cannot use the dd/mm/yyyy date format, so
I
am
now
wondering if I can use an SQL statement to apply criteria to open a
form
to
specific records.

I need to replace the following code with an SQL statement if
possible

----------------------------------------------------
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmDispositionHistory"

If IsNull([txtStartDate]) And IsNull([txtEndDate]) Then
stLinkCriteria = "[VehicleNumber]=" & Me![VehicleNumber]

Else
stLinkCriteria = "VehicleNumber='" & Me!VehicleNumber & "' And "
&
"DispositionDate BETWEEN #" & Format$(Me!txtStartDate, "dd/mm/yyyy")
&
"#
AND # " & Format$(Me!txtEndDate, "dd/mm/yyyy") & "#"

End If

DoCmd.OpenForm stDocName, , , stLinkCriteria

-----------------------------------------------------------------------

Here is the value of stLinkCriteria:

stLinkCriteria = "VehicleNumber='4091' AND DispositionDate BETWEEN
#01/12/2004# AND #04/01/2005#"


When the DoCmd.OpenForm part runs, this causes an error that states
OpenForm action was cancelled. I am assuming that it has something
to
do
with
the date format, because without selecting dates, the form opens
just
fine.

If there is some other way of doing this, I would appreciate a nudge
in
that
direction.

Cheers