Date type mismatch

[Ann]

Hi, I have used the code below (there is more but the rest works) to create a
form with combo boxes so the value in each combo box chosen will filter and
give me a report with only the values I want to see. I didn't write this but
I can understand enough to make it work and have used it many times as long
as the values are all strings. But now I have a date value. Everything is
formatted to be a Short Date. I type the date (11/09/2006) and I apply the
filter to pull only employees terminated on that date. I get a type mismatch
error. My value that is being passed is the correct date but when I hover
over dtmEmployeeTermination I see 12:00:00 AM. It thinks this is the time
yet no where did I format it to be anyting but a Short Date. Can anyone help
me make this work for dates? Thanks.


Dim dtmEmployeeTermination As Date

'Employee Termination
If IsNull(Me.cboEmployeeTermination.Value) Then
dtmEmployeeTermination = "Like '*'"
Else
dtmEmployeeTermination = "='" & Me.cboEmployeeTermination.Value & "'"
End If

'Filter the string for each declared variable
strFilter = "[Age] " & strAge & _
" AND [Gender] " & strGender & _
" AND [dtmEmployeeTermination] " & dtmEmployeeTermination & _
" AND [YearIn] " & strYearAreIn

 

[Ken Snell \(MVP\)]

Format the date string and delimit it with # characters. I don't see in your
code what string text the strYearAreIn variable contains, so I cannot
suggest more specifics without more info from you.

 

[Ann]

Thanks but I'm not a programmer so I'm not sure how it needs to be typed. I
took someone else's code and changed the names they used. That's why it's
worked in the past but they were all strings. Where does the # go?

The strYearAreIn works fine. I guess I accidentally copied more than I
should have.

Format the date string and delimit it with # characters. I don't see in your
code what string text the strYearAreIn variable contains, so I cannot
suggest more specifics without more info from you.
--

Ken Snell
<MS ACCESS MVP>

Hi, I have used the code below (there is more but the rest works) to
create a
form with combo boxes so the value in each combo box chosen will filter
and
give me a report with only the values I want to see. I didn't write this
but
I can understand enough to make it work and have used it many times as
long
as the values are all strings. But now I have a date value. Everything
is
formatted to be a Short Date. I type the date (11/09/2006) and I apply
the
filter to pull only employees terminated on that date. I get a type
mismatch
error. My value that is being passed is the correct date but when I hover
over dtmEmployeeTermination I see 12:00:00 AM. It thinks this is the time
yet no where did I format it to be anyting but a Short Date. Can anyone
help
me make this work for dates? Thanks.


Dim dtmEmployeeTermination As Date

'Employee Termination
If IsNull(Me.cboEmployeeTermination.Value) Then
dtmEmployeeTermination = "Like '*'"
Else
dtmEmployeeTermination = "='" & Me.cboEmployeeTermination.Value &
"'"
End If

'Filter the string for each declared variable
strFilter = "[Age] " & strAge & _
" AND [Gender] " & strGender & _
" AND [dtmEmployeeTermination] " & dtmEmployeeTermination & _
" AND [YearIn] " & strYearAreIn

 

[Ken Snell \(MVP\)]

What part of the code contains the place where you want to filter by a date
value? I assumed that the strYearAreIn variable contained some type of
filtering clause?

--

Ken Snell
<MS ACCESS MVP>

Thanks but I'm not a programmer so I'm not sure how it needs to be typed.
I
took someone else's code and changed the names they used. That's why it's
worked in the past but they were all strings. Where does the # go?

The strYearAreIn works fine. I guess I accidentally copied more than I
should have.

Format the date string and delimit it with # characters. I don't see in
your
code what string text the strYearAreIn variable contains, so I cannot
suggest more specifics without more info from you.
--

Ken Snell
<MS ACCESS MVP>

Hi, I have used the code below (there is more but the rest works) to
create a
form with combo boxes so the value in each combo box chosen will filter
and
give me a report with only the values I want to see. I didn't write
this
but
I can understand enough to make it work and have used it many times as
long
as the values are all strings. But now I have a date value.
Everything
is
formatted to be a Short Date. I type the date (11/09/2006) and I apply
the
filter to pull only employees terminated on that date. I get a type
mismatch
error. My value that is being passed is the correct date but when I
hover
over dtmEmployeeTermination I see 12:00:00 AM. It thinks this is the
time
yet no where did I format it to be anyting but a Short Date. Can
anyone
help
me make this work for dates? Thanks.


Dim dtmEmployeeTermination As Date

'Employee Termination
If IsNull(Me.cboEmployeeTermination.Value) Then
dtmEmployeeTermination = "Like '*'"
Else
dtmEmployeeTermination = "='" & Me.cboEmployeeTermination.Value
&
"'"
End If

'Filter the string for each declared variable
strFilter = "[Age] " & strAge & _
" AND [Gender] " & strGender & _
" AND [dtmEmployeeTermination] " & dtmEmployeeTermination & _
" AND [YearIn] " & strYearAreIn

 

[Ann]

The dtmEmployeeTermination. The other field is for Freshman, Sophmore, etc.

There are a lot of fields to filter by but I only pasted the one pertaining
to the Employee Termination date. If it would be better to show you
everything I can. It was long so I didn't know if it was too much
information. I had tried to use the # since I know to do that in a query for
dates but I must have put it in the wrong place because it didn't work. Here
are the pieces of code where the Employee Termination field is reverenced.
Thank you for all of your help.

Dim dtmEmployeeTermination As Date

'Employee Termination
If IsNull(Me.cboEmployeeTermination.Value) Then
dtmEmployeeTermination = "Like '*'"
Else
dtmEmployeeTermination = "='" & Me.cboEmployeeTermination.Value & "'"
End If

'Filter the string for each declared variable
strFilter = "[Age] " & strAge & _
" AND [Gender] " & strGender & _
" AND [dtmEmployeeTermination] " & dtmEmployeeTermination

What part of the code contains the place where you want to filter by a date
value? I assumed that the strYearAreIn variable contained some type of
filtering clause?

--

Ken Snell
<MS ACCESS MVP>

Thanks but I'm not a programmer so I'm not sure how it needs to be typed.
I
took someone else's code and changed the names they used. That's why it's
worked in the past but they were all strings. Where does the # go?

The strYearAreIn works fine. I guess I accidentally copied more than I
should have.

Format the date string and delimit it with # characters. I don't see in
your
code what string text the strYearAreIn variable contains, so I cannot
suggest more specifics without more info from you.
--

Ken Snell
<MS ACCESS MVP>


Hi, I have used the code below (there is more but the rest works) to
create a
form with combo boxes so the value in each combo box chosen will filter
and
give me a report with only the values I want to see. I didn't write
this
but
I can understand enough to make it work and have used it many times as
long
as the values are all strings. But now I have a date value.
Everything
is
formatted to be a Short Date. I type the date (11/09/2006) and I apply
the
filter to pull only employees terminated on that date. I get a type
mismatch
error. My value that is being passed is the correct date but when I
hover
over dtmEmployeeTermination I see 12:00:00 AM. It thinks this is the
time
yet no where did I format it to be anyting but a Short Date. Can
anyone
help
me make this work for dates? Thanks.


Dim dtmEmployeeTermination As Date

'Employee Termination
If IsNull(Me.cboEmployeeTermination.Value) Then
dtmEmployeeTermination = "Like '*'"
Else
dtmEmployeeTermination = "='" & Me.cboEmployeeTermination.Value
&
"'"
End If

'Filter the string for each declared variable
strFilter = "[Age] " & strAge & _
" AND [Gender] " & strGender & _
" AND [dtmEmployeeTermination] " & dtmEmployeeTermination & _
" AND [YearIn] " & strYearAreIn