Hi,
I'm pretty sure it's a no brainer for some people, but I'm lost...
I have a form that I use to filter on a report on Access 2000. I use various criterias in list boxes to filter my report and it works for all of them except for the year, where I recieve the message; " Data Type mismatch in criteria expression" I guess it's because my year is extracted from a date (06/06/2007) and the data type is wrong, but I don't know how to solve my issue.
Here's the code:
Private Sub cmdFilter_Click()
Dim varItem As Variant
Dim strSite_Name As String
Dim strYear As String
Dim strFilter As String
'For Site_Name'
For Each varItem In Me.lstSite_Name.ItemsSelected
strSite_Name = strSite_Name & ",'" & Me.lstSite_Name.ItemData(varItem) & "'"
Next varItem
If Len(strSite_Name) = 0 Then
strSite_Name = "Like '*'"
Else
strSite_Name = Right(strSite_Name, Len(strSite_Name) - 1)
strSite_Name = "IN (" & strSite_Name & ")"
End If
'For Year'
For Each varItem In Me.lstYear.ItemsSelected
strYear = strYear & ",'" & Me.lstYear.ItemData(varItem) & "'"
Next varItem
If Len(strYear) = 0 Then
strYear = "Like '*'"
Else:
strYear = Right(strYear, Len(strYear) - 1)
strYear = "IN(" & strYear & ")"
End If
strFilter = "[Site_Name] " & strSite_Name & _
"AND [Year] " & strYear
With Reports![rpt_detail]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Anyone can help?
Thank you
Marie
2 2213
Marie:
I'm fairly new at all of this, but I had similar needs on a database of my own. I concocted an elaborate workaround using the DatePart() function, which I know lets you pull out a part of a date (like, d or m or yyyy) to use with/against date values. I don't know enough to find a way to work it into your code, but maybe it's a step in the right direction?
Sorry if I'm way off base here.
Hi,
I'm pretty sure it's a no brainer for some people, but I'm lost...
I have a form that I use to filter on a report on Access 2000. I use various criterias in list boxes to filter my report and it works for all of them except for the year, where I recieve the message; " Data Type mismatch in criteria expression" I guess it's because my year is extracted from a date (06/06/2007) and the data type is wrong, but I don't know how to solve my issue.
Here's the code:
Private Sub cmdFilter_Click()
Dim varItem As Variant
Dim strSite_Name As String
Dim strYear As String
Dim strFilter As String
'For Site_Name'
For Each varItem In Me.lstSite_Name.ItemsSelected
strSite_Name = strSite_Name & ",'" & Me.lstSite_Name.ItemData(varItem) & "'"
Next varItem
If Len(strSite_Name) = 0 Then
strSite_Name = "Like '*'"
Else
strSite_Name = Right(strSite_Name, Len(strSite_Name) - 1)
strSite_Name = "IN (" & strSite_Name & ")"
End If
'For Year'
For Each varItem In Me.lstYear.ItemsSelected
strYear = strYear & ",'" & Me.lstYear.ItemData(varItem) & "'"
Next varItem
If Len(strYear) = 0 Then
strYear = "Like '*'"
Else:
strYear = Right(strYear, Len(strYear) - 1)
strYear = "IN(" & strYear & ")"
End If
strFilter = "[Site_Name] " & strSite_Name & _
"AND [Year] " & strYear
With Reports![rpt_detail]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Anyone can help?
Thank you
Marie
Hi,
I'm pretty sure it's a no brainer for some people, but I'm lost...
I have a form that I use to filter on a report on Access 2000. I use various criterias in list boxes to filter my report and it works for all of them except for the year, where I recieve the message; " Data Type mismatch in criteria expression" I guess it's because my year is extracted from a date (06/06/2007) and the data type is wrong, but I don't know how to solve my issue.
Here's the code:
Private Sub cmdFilter_Click()
Dim varItem As Variant
Dim strSite_Name As String
Dim strYear As String
Dim strFilter As String
'For Site_Name'
For Each varItem In Me.lstSite_Name.ItemsSelected
strSite_Name = strSite_Name & ",'" & Me.lstSite_Name.ItemData(varItem) & "'"
Next varItem
If Len(strSite_Name) = 0 Then
strSite_Name = "Like '*'"
Else
strSite_Name = Right(strSite_Name, Len(strSite_Name) - 1)
strSite_Name = "IN (" & strSite_Name & ")"
End If
'For Year'
For Each varItem In Me.lstYear.ItemsSelected
strYear = strYear & ",'" & Me.lstYear.ItemData(varItem) & "'"
Next varItem
If Len(strYear) = 0 Then
strYear = "Like '*'"
Else:
strYear = Right(strYear, Len(strYear) - 1)
strYear = "IN(" & strYear & ")"
End If
strFilter = "[Site_Name] " & strSite_Name & _
"AND [Year] " & strYear
With Reports![rpt_detail]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Anyone can help?
Thank you
Marie
Syntax for an IN statement using dates looks like this
In (#5/21/2007#,#6/1/2007#)
so you just need to alter your string create code to include the pound signs.
J
Sign in to post your reply or Sign up for a free account.
Similar topics
by: LJgrnl |
last post by:
I've got a type mismatch error that's driving me nutty. Variable blnNoData
has the initial value False. If a recordset comes back empty (both .EOF and
..BOF are true) then blnNoData is set to...
|
by: deko |
last post by:
Okay, so I figured out how to pull Outlook Appointments into an Access Table
(see below).
But the data comes in the wrong Data Type - how do I convert it from Text to
Long Integer?
For...
|
by: Chicken Kebab Abdullah |
last post by:
Does anyone know why I get the error 3464 Data type mismatch from the
following code.
I have a form with a combo(to choose a consumable) and 2 list boxes on
it.
list on left is all printers...
|
by: news.paradise.net.nz |
last post by:
I have been developing access databases for over 5 years.
I have a large database and I have struck this problem with it before
but can find nothing in help or online. Access 2000
I have a query...
|
by: ArcadeJr |
last post by:
Good morning all!
I have been getting a Run-time Error message #3464 - Data Type mismatch
in criteria expression. While trying to run a query.
I have a database where the field Asset_Number...
|
by: Jake |
last post by:
I am currently trying to create my own Point Of Sale software for my
retail store. I wrote the program with the UPC field as Long integer.
When I started to add the products by UPC code, I got a...
|
by: amitbadgi |
last post by:
I am getting the following error while converting an asp application
to asp.net
Exception Details: System.Runtime.InteropServices.COMException: Data
type mismatch in criteria expression.
...
|
by: psychomad |
last post by:
Please, can someone help me out to solve this error, i've been
searching throughout my codes and yet i didnt succeed in finding the
error!!!!
The Error is:
Server Error in '/' Application....
|
by: sara |
last post by:
I have a Memo field in a table to hold notes from a conversation a
social worker has had with a client (this is for a non-profit).
If the user needs to update the memo field, I need to find the...
|
by: Lysander |
last post by:
I have written a query that takes three integers representing day,month and year, forms a date from them and compares this date to the date the record was entered and returns any records where the...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |