473,836 Members | 1,405 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query - data type mismatch in criteria expression

759 Contributor
Hi all !
I have trying to provide some help to this thread

http://bytes.com/topic/access/answer...ue#post3706190

but I have fall myself in trouble :) .

I design a query where I have trying to use a Public Function in the criteria row.
This function seems to return exactly the same expression as I see in the criteria row if I use certain numbers as criteria. Unfortunately this not work.
Can someone check that and teach me what data type must be used for my function ?

I have attache this very small database, because I can't explain very well what happen. And not because my poor English. Because I have no idea.
Attached Files
File Type: zip Staff.zip (37.3 KB, 125 views)
Feb 29 '12 #1
5 5379
MikeTheBike
639 Recognized Expert Contributor
Hi

What you are doing will not work (comaring a string to a numic field).
I would do it this way.
This code goes in the frmStaffAbiliti eas form module
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Dim SQL As String
  5. Dim strCriteria As String
  6.  
  7. Sub BuildSQL(ByVal Criteria As String)
  8.     SQL = "SELECT DISTINCT tblStaff.Name_Surname, tblAssignedAbilities.Abilitie_ID "
  9.     SQL = SQL & "FROM tblStaff INNER JOIN "
  10.     SQL = SQL & "tblAssignedAbilities ON "
  11.     SQL = SQL & "tblStaff.Staff_ID = tblAssignedAbilities.Staff_ID "
  12.     SQL = SQL & Criteria
  13. End Sub
  14.  
  15. Private Sub cmdRequery_Click()
  16.     Dim i As Integer
  17.  
  18.     strCriteria = ""
  19.  
  20.     With Me.lstAbilities
  21.         For i = 0 To .ListCount - 1
  22.             If .Selected(i) Then
  23.                 strCriteria = strCriteria & " OR Abilitie_ID = " & .ItemData(i)
  24.             End If
  25.         Next i
  26.     End With
  27.  
  28.     If strCriteria <> "" Then
  29.         strCriteria = "WHERE " & Mid(strCriteria, 5)
  30.     End If
  31.     BuildSQL (strCriteria)
  32.     Me.RecordSource = SQL
  33.     Me.Requery
  34.  
  35. End Sub
  36.  
  37. Private Sub Form_Open(Cancel As Integer)
  38.     BuildSQL ("")
  39.     Me.RecordSource = SQL
  40.     Me.Requery
  41.  
  42. End Sub
HTH

MTB
Feb 29 '12 #2
Mihail
759 Contributor
Thank you for reply, Mike.
I already solve that in this way (using SQL in VBA).

Look here:
http://bytes.com/topic/access/answer...ue#post3706190

But SQL is not my best friend.
So I like to solve that by passing parameter to query (as I try in the attached database).
Feb 29 '12 #3
NeoPa
32,584 Recognized Expert Moderator MVP
You need to post at least the code of the function Mihail. Downloading databases is a PitA, and should only be asked when necessary. This doesn't seem to be such a case.
Feb 29 '12 #4
Mihail
759 Contributor
I post the database in order to preserve time for every one which wish to help me in finding a solution. Not to preserve my time.
In attached pics are the definitions for tables and the "in trouble" query.

The SQL for this query is:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblStaff.Name_Surname, tblAssignedAbilities.Abilitie_ID
  2. FROM tblStaff INNER JOIN tblAssignedAbilities ON tblStaff.Staff_ID = tblAssignedAbilities.Staff_ID
  3. WHERE (((tblStaff.Staff_ID)=WhereClause()));
Also I have a form, frmStaffAbiliti es (for VBA it's name is Form_frmStaffAb ilities) which has as record source the query qStaffAbilities.

Also, in this form, I have a list box named lstAbilities. It is a Multi Select list box.
The row source for this list box is:
Expand|Select|Wrap|Line Numbers
  1. SELECT [tblAbilities].[Abbilitie_ID], [tblAbilities].[Abilitie] FROM tblAbilities ORDER BY [Abilitie];

Finally the function WhereClause()
Expand|Select|Wrap|Line Numbers
  1. Public Function WhereClause()
  2. Dim WhereSQL As String 'Store the Where clause
  3.  
  4. Dim FirstItem As Boolean 'True until first selected item is find in lstAbilities
  5.     FirstItem = True
  6.  
  7. Dim i As Integer, IDabil As Integer 'Work variables
  8.  
  9. On Error GoTo ErrorHandler
  10.     For i = 0 To Form_frmStaffAbilities.lstAbilities.ListCount - 1 'Scan lstAbilities
  11.         If Form_frmStaffAbilities.lstAbilities.Selected(i) Then
  12.             IDabil = Form_frmStaffAbilities.lstAbilities.Column(0, i) 'Store ID_Abilitie
  13.             If FirstItem Then
  14.                 WhereSQL = IDabil
  15.                 FirstItem = False
  16.             Else
  17.                 WhereSQL = WhereSQL & " Or " & IDabil
  18.             End If
  19.         End If
  20.     Next i
  21.  
  22.     If FirstItem Then 'Nothing is selected
  23.         WhereClause = "Like ""*""" 'Not working
  24.     Else 'There are selected items
  25.         WhereClause = WhereSQL
  26.     End If
  27.  
  28. Ex:
  29.     MsgBox (WhereClause)
  30. '    Debug.Print WhereClause
  31. Exit Function
  32.  
  33. ErrorHandler:
  34.     Select Case Err.Number
  35.         Case 2427 'You entered an expression that has no value - When the form is first loading
  36.             WhereClause = "Like ""*""" 'Not working
  37.         Case Else
  38.             MsgBox ("MyMesge   " & Err.Number & " " & Err.Description)
  39.     End Select
  40.  
  41.     Resume Ex
  42. End Function

Also I try a very simplified function:
Expand|Select|Wrap|Line Numbers
  1. 'Simplified function:
  2. Public Function WhereClauseSimple()
  3.     WhereClause = 1 'Working
  4.     WhereClause = 2 'Working
  5.     WhereClause = 3 'Working
  6.  
  7.     WhereClause = "Like ""*""" 'Not working
  8.     WhereClause = "1 Or 2" 'Not working
  9.     MsgBox (WhereClause)
  10. End Function





Attached Images
File Type: jpg Table_Staff.jpg (41.0 KB, 1845 views)
File Type: jpg Table_Abilities.jpg (39.8 KB, 1780 views)
File Type: jpg Table_AssignedAbilities.jpg (42.9 KB, 1770 views)
File Type: jpg Relationships.jpg (20.3 KB, 1766 views)
File Type: jpg Query_StaffAbilities.jpg (47.0 KB, 3366 views)
Feb 29 '12 #5
Mihail
759 Contributor
Well, this approach do the job even if steel is not an answer to the main question.

The key is to create an extra field in query:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT tblStaff.Name_Surname, IsSelected([Abilitie_ID]) AS SelectedID
  2. FROM tblStaff INNER JOIN tblAssignedAbilities ON tblStaff.Staff_ID = tblAssignedAbilities.Staff_ID
  3. WHERE (((IsSelected([Abilitie_ID]))=True))
  4. ORDER BY tblStaff.Name_Surname;


The code for IsSelected() function is:
Expand|Select|Wrap|Line Numbers
  1. Public Function IsSelected(ID As Long) As Boolean
  2. On Error GoTo ErrorHandler
  3. Dim i As Long, SomethingIsSelected As Boolean
  4.  
  5. '    This are false by default but to be sure
  6.     SomethingIsSelected = False
  7.     IsSelected = False
  8.  
  9.     For i = 0 To Form_frmStaffAbilities.lstAbilities.ListCount - 1
  10.         If Form_frmStaffAbilities.lstAbilities.Selected(i) Then
  11.             SomethingIsSelected = True
  12.             If Form_frmStaffAbilities.lstAbilities.Column(0, i) = ID Then
  13.                 IsSelected = True
  14. Exit Function
  15.             End If
  16.         End If
  17.     Next i
  18. '    No items muched criteria
  19.     IsSelected = Not SomethingIsSelected 'If nothing is selected then show all items
  20. Exit Function
  21.  
  22. ErrorHandler:
  23.     Select Case Err.Number
  24.         Case Else
  25.             MsgBox (Err.Number & " " & Err.Description)
  26.     End Select
  27.     IsSelected = False
  28. End Function
Mar 2 '12 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

1
6551
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 was once a type Number, but I had to change it to a type Text due to I needed to have two zeros at the beginning of the Asset Number (EX: 001234.) The rease I am writing is that now when I run from My Query Menu a
4
3148
by: N J | last post by:
Hi, CurrentDb.Execute "Update tblDelayedOrders Set DELAYED = True Where ID = " & Me.txtOrderNumber & ";", dbfailonerror Me.StatusListBox.AddItem "ORDER # " & Me.txtOrderNumber & " MARKED AS DELAYED " & "" & "", 0 When I change Where ID to Where TRACKING (another text field) I get the error Data Type Mismatch in expression critera, or something like that
2
9811
by: igor.barbaric | last post by:
Hello! I have created a very simple query like this: SELECT Tasks.Name, DurationHrs(,) AS Duration FROM Tasks INNER JOIN Log ON Tasks.TaskID=Log.TaskID; The above query works fine. "Tasks.Name" is a "Text" field with description of task, while "DurationHrs" is a VB function that calculates number of hours between two "DateTime" values and returns
1
2114
by: Igor Barbaric | last post by:
Hello! I have created a very simple query like this: SELECT Tasks.Name, DurationHrs(,) AS Duration FROM Tasks INNER JOIN Log ON Tasks.TaskID=Log.TaskID; The above query works fine. "Tasks.Name" is a "Text" field with description of task, while "DurationHrs" is a VB function that calculates number of hours between two "DateTime" values and returns
2
2591
by: technocraze | last post by:
Hi guys, Implementations application & programming environ MS Acess + Visual Basic Table fields Serialno (pk) - Auto number StudentId - text Course - text intake - number
10
6241
by: aaronrm | last post by:
I have a real simple cross-tab query that I am trying to sum on as the action but I am getting the "data type mismatch criteria expression" error. About three queries up the food chain from this cross-tab query I am using a simple query with no grouping where I am filtering some data out in the criteria line. I have been out of access for a couple years but I remember in the past I had a solution for this but I can't remember. Any help...
19
21580
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 date is more than 10 months out. The query runs fine, but I when I put the criteria of >10 I get 'Data Type mismatch' error. The code below is the original query. I have since put all the datediff bit in code, with all variables declared as date,...
1
2718
by: DC | last post by:
Any idea why this code would cause a error? The SQL that gets executed seems to be (using record 1132 as an example) DELETE FROM user_table WHERE ID = '1132'; And the value of ViewState("ID") =1132 ID is an Access database autonumber field. Is Viewstate data passed as an odd type or something?
2
3960
by: tmoon3 | last post by:
Hello, This must be a simple mistake, but for some reason I cannot seem to get around it. I am simply trying to create a report of all employees that have a date filled in in a training column in an access DB. I give the users a drop down list to choose employee type, division, and term status and based on their choices I create the selection criteria for the report. This code works without the ForkTruckClass > '#1-01-1900#' (the below...
2
1885
by: eyoung1 | last post by:
this works sSQL = "SELECT *" & _ " FROM Expenses2008" & _ " WHERE Amount Like '%" & Request.Form("searchItem") & "%'" set rs = Connect.Execute(sSQL) however if I enter an amount of 99 it not only gives me all entries with 99.00 in the Amount collum but 199.00, 1991.72...anything with two 9s together.
0
10831
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10582
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9367
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7778
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6976
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5645
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4446
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4005
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3108
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.