Error 3464 (2 Viewers)

Ravi Kumar

Registered User.
Local time
Tomorrow, 04:19
Joined
Aug 22, 2019
Messages
162
hello all ,
The below code was supposed to filter the data in my report using 2 combo boxes in the form .
But Area combo box is working , but when I entered the person name in second one ( even though it is related to same area) it is giving me error as :
ERROR 3464 : Data type mismatch in criteria expression.

Can anyone solve this for me .
Below is the code.
Code:
Private Sub OK_Click()
    On Error GoTo Err_Handler
    Dim strReport As String
    Dim strDateField As String
    Dim StrWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"
    strReport = "Person wise data1"
    strDateField = "[Week Of Entry ]"
    lngView = acViewReport
    If IsDate(Me.txtstartdate) Then
        StrWhere = "(" & strDateField & " >= " & Format(Me.txtstartdate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtenddate) Then
        If StrWhere <> vbNullString Then
            StrWhere = StrWhere & " AND "
        End If
        StrWhere = StrWhere & "(" & strDateField & " < " & Format(Me.txtenddate + 1, strcJetDate) & ")"
    End If
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    If Trim(Me.cbolocationbrief & "") <> "" Then
        StrWhere = StrWhere & " And " & _
                 Application.BuildCriteria("[Area]", vbString, Me.cbolocationbrief.Column(0))
                 End If
    If Trim(Me.cboperson & "") <> "" Then
        StrWhere = StrWhere & " And " & _
                 Application.BuildCriteria("[Person Name]", vbString, Me.cboperson.Column(0))
    End If
    Debug.Print StrWhere
    'DoCmd.OpenReport strReport, lngView, , StrWhere
    'DoCmd.OpenReport "OT Details", acViewPreview, , "[Area]='" & Me.cbolocationbrief & "'"
    DoCmd.OpenReport "Person wise data1", acViewReport, , StrWhere
Exit_Handler:
    Exit Sub
Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub
 

June7

AWF VIP
Local time
Today, 14:49
Joined
Mar 9, 2014
Messages
5,423
So is combobox a lookup with alias? Is actual value in combobox person ID?
 

Ravi Kumar

Registered User.
Local time
Tomorrow, 04:19
Joined
Aug 22, 2019
Messages
162
Yes the combo boxes are related , but sorry I didn't get the second question..?
 

Ravi Kumar

Registered User.
Local time
Tomorrow, 04:19
Joined
Aug 22, 2019
Messages
162
For further details please refer the attached image.
 

Attachments

  • Untitled.png
    Untitled.png
    7.8 KB · Views: 372

June7

AWF VIP
Local time
Today, 14:49
Joined
Mar 9, 2014
Messages
5,423
If you want to search on [Person Name] and that field is text type but combobox value is numeric ID, that could cause data type mismatch. Except you are using BuildCriteria which should format the criteria for text field with vbString parameter and at best there would just not be a match to the ID value.

So either Area or Person Name or both must actually be number type field.
 

Ravi Kumar

Registered User.
Local time
Tomorrow, 04:19
Joined
Aug 22, 2019
Messages
162
Thank you,
below is y row source of person details Combo box , & for the other it is a typed list .
Shall I add the ID column to my otherCombo box too ?

Code:
ELECT [person details].[ID], [person details].[Person details] FROM [person details] ORDER BY [Person details];
 

June7

AWF VIP
Local time
Today, 14:49
Joined
Mar 9, 2014
Messages
5,423
I assume ID is a number type. If so, the BuildCriteria should not use vbString, it should use vbLong.

If Area is a typed list then there is no ID to include.
 

Ravi Kumar

Registered User.
Local time
Tomorrow, 04:19
Joined
Aug 22, 2019
Messages
162
Hello sir ,
I edited the code to "vblong" but still it didn't work.
First combobox is filtering fine .
Only this person combo box is showing this message.
 

June7

AWF VIP
Local time
Today, 14:49
Joined
Mar 9, 2014
Messages
5,423
I have never seen BuildCriteria used but as I understand it, it should work with that correction. If you want to provide db for analysis, follow instructions at bottom of my post.

Alternative is to not use BuildCriteria. Review http://allenbrowne.com/ser-62code.html
 

Ravi Kumar

Registered User.
Local time
Tomorrow, 04:19
Joined
Aug 22, 2019
Messages
162
Hello , I have attached the data base.
 

Attachments

  • Leave & OT monitoring.accdb
    576 KB · Views: 182

June7

AWF VIP
Local time
Today, 14:49
Joined
Mar 9, 2014
Messages
5,423
Did not provide [person details] table so combobox references wrong table name. Combobox references a field [person details] - should be [Person Name]. An ID of 0 is odd.

Regardless, your code needs to be:
Code:
    If Trim(Me.cbolocationbrief & "") <> "" Then
        StrWhere = StrWhere & " And " & _
                 Application.BuildCriteria("[Area]", vbString, Me.cbolocationbrief.Column(0))
    End If
    If Trim(Me.cboperson & "") <> "" Then
        StrWhere = StrWhere & " And " & _
                 Application.BuildCriteria("[ID]", vbLong, Me.cboperson.Column(0))
    End If
But you will have an issue with strWhere beginning with " AND " if there are no date criteria. One way to deal with:
Code:
        StrWhere = " AND (" & strDateField & " >= " & Format(Me.txtstartdate, strcJetDate) & ")"

Then
Code:
    If StrWhere <> "" Then StrWhere = Mid(StrWhere, 5)
    Debug.Print StrWhere
Advise not to use spaces nor punctuation/special characters in naming convention.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 22:49
Joined
Jan 14, 2017
Messages
18,186
Adding to June's comments, I would scrap the Application.BuildCriteria notation and instead use code like this

Code:
    If Trim(Me.cbolocationbrief & "") <> "" Then
        StrWhere = StrWhere & " And Area = '" & Me.cbolocationbrief & "'"
    End If

    If Trim(Me.cboperson & "") <> "" Then
        StrWhere = StrWhere & " And [ID] = " & Me.cboperson
        [COLOR="SeaGreen"]'or as possible alternative
      ' StrWhere = StrWhere & " And [Person Details] = '" & Me.cboperson.Column(1) & "'"[/COLOR]
    End If

It would have helped if you had included the 'missing' Person Details table
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:49
Joined
Feb 28, 2001
Messages
27,001
Let's take this in a different direction. You are building an SQL WHERE-clause string here. Your code shows that before you left that routine, you had a Debug.Print so you could examine the string you were building. So... what did it show you? I suspect it didn't like what you wrote because you didn't have an explicit operator in the BuildCriteria calls. Can you show us an example of what it built in the case that troubles you?

In the BuildCriteria documentation I looked up, there is no mention of a default relationship to be tested, i.e. you need to explicitly provide "=" or "<" or ">" or whatever for the selected value, and I don't see that you did that. Unless it is somehow part of the combo box, there IS no obvious relationship. So I have no clue as to how the SQL parser will report that. The "AND" preceding the field to be tested probably doesn't work well with string variables or fields IF it is taken as an isolated binary logic operator because AND is a bit-wise function. For that matter, I didn't see a relationship indicator as part of the FIRST call (involving location) either, so I'm a little bit confused there, too.
 

June7

AWF VIP
Local time
Today, 14:49
Joined
Mar 9, 2014
Messages
5,423
Apparently = sign is default to BuildCriteria function because code with my edits works. Equal sign is in constructed clause.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:49
Joined
Feb 28, 2001
Messages
27,001
Interesting, June7. Not at all disputing you, but I had never used this and there is no mention of the default being an "=" for a relational expression. The examples given in the docs online didn't say doodlum squat about a default of any kind. I would still like to see the output of the Debug.Print, though, to see what Ravi's system built.
 

June7

AWF VIP
Local time
Today, 14:49
Joined
Mar 9, 2014
Messages
5,423
It builds: [Area]="Stores" And [ID]=0
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:49
Joined
Sep 21, 2011
Messages
14,048
Doc,
I too was curious

Code:
tt="Paul"
? application.buildcriteria("ID",vbstring,"tt1")
ID="tt1"
 

Users who are viewing this thread

Top Bottom