Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Data Type Mismatch in Criteria Expression

Posted on 2007-10-07
18
Medium Priority
?
852 Views
Last Modified: 2013-11-28
I am running a Microsoft Access Program and when it is getting an error.  I take one form, I select a record, hit a button and it opens another form with the information so that I can edit that record.  I get the error that states the following before the new form is opened:

run time error '3464'  Data type mismatch in criteria expression

Here is the code in that block where the error is and it puts an arrow focus on Me.FilterOn = True:

Private Sub Form_Load()
    Me.Filter = "ProjectID = '" & Me.OpenArgs & "'"
    Me.FilterOn = True
   
    If Me.OpenArgs = "" Then
        'new project
        DoCmd.GoToRecord acActiveDataObject, , acNewRec
    End If
End Sub

What could be the problem?
0
Comment
Question by:VBBRett
  • 7
  • 6
  • 2
  • +2
18 Comments
 
LVL 75
ID: 20030988


Private Sub Form_Load()

    If Me.OpenArgs = "" Then   ' make this test first
        ''new project
        DoCmd.GoToRecord acActiveDataObject, , acNewRec
        Exit Sub ' ** no value in Open Args
    End If

    Me.Filter = "ProjectID = " & Chr(34) & Me.OpenArgs & Chr(34)    ' ** Chr(34) = double quote
    Me.FilterOn = True
   

End Sub
0
 

Author Comment

by:VBBRett
ID: 20030994
This is what the code is behind the edit button.  After I click this button, the error also shows up...

Private Sub cmdEdit_Click()
On Error GoTo Err_cmdEdit_Click

    If adhIsFormOpen("frmProject") Then
        Forms!frmproject.Activate
    Else
        Dim stDocName As String
        Dim stLinkCriteria As String
   
        stDocName = "frmProject"
        DoCmd.OpenForm stDocName, , , stLinkCriteria, , acWindowNormal, sfMainGrid.Form!ProjectID
    End If
Exit_cmdEdit_Click:
    Exit Sub

Err_cmdEdit_Click:
    MsgBox Err.Description
    Resume Exit_cmdEdit_Click
End Sub
0
 

Author Comment

by:VBBRett
ID: 20031005
I still get the error where it is pointing at the following code:

Me.FilterOn = True

What is wrong with this line of code?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 75
ID: 20031019
ProjectID is numeric ?
OpenArgs is a String value by definition.

If so, try this:

    Me.Filter = "ProjectID = " & Chr(34) & CStr(Me.OpenArgs) & Chr(34)    ' ** Chr(34) = double quote
    Me.FilterOn = True

mx
0
 

Author Comment

by:VBBRett
ID: 20031043
I am still getting that same problem.  GRR...this is annoying.  I have to keep projectID as an integer in the database table.  What can make this work?
0
 
LVL 75
ID: 20031053
What happens if you just do

vba editor >> Debug >> Compile ?  Do you get errors.

btw ... no need to change the data type of ProjectID

There is nothing wrong with

Me.FilterOn = True

per se .. something else is going gon ...

mx
0
 
LVL 14

Expert Comment

by:ldunscombe
ID: 20031305
try
Val(Me.OpenArgs)
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 2000 total points
ID: 20031365
if ProjectID is an integer, then this line :

Me.Filter = "ProjectID = '" & Me.OpenArgs & "'"

will cause the error you are getting.  Change it to:

Me.Filter = "ProjectID = " & Val(Me.OpenArgs)


the original line is treating the paramet as if it were a STRING, while the ProjectID field is actually an Integer, hence the Type Mismatch.

AW
0
 
LVL 58

Expert Comment

by:harfang
ID: 20031415
Are you absolutely certain that ProjectID is in fact a text field? If not, you should not add the quotes. This would explain the error message perfectly, too!

Private Sub Form_Load()
    If Me.OpenArgs = "" Then
            Me.DataEntry = True
    Else
        Me.Filter = "ProjectID = " & Me.OpenArgs
        Me.FilterOn = True
    End If
End Sub

Cheers!
(°v°)
0
 
LVL 58

Expert Comment

by:harfang
ID: 20031416
Sorry AW. Didn't realize I had this open for so long! I should have refreshed.
(°v°)
0
 

Author Comment

by:VBBRett
ID: 20031471
Thanks Arthur!  You are amazing!
0
 
LVL 75
ID: 20031626
I believe unscombe had the answer first ... if Val() was the solution ?

mx
0
 

Author Comment

by:VBBRett
ID: 20031640
Is there a way I can split the points?
0
 
LVL 75
ID: 20031685
0
 
LVL 75
ID: 20031753
Also, I a little confused here.  The Filter property is a String:

"The Filter property is a string expression consisting of a WHERE clause without the WHERE keyword. For example, the following Visual Basic code defines and applies a filter to show only customers from the USA:"

So ... I'm confused as to how Val worked ... again, if that was what actually may this work?

VBBRett ...are you sure this fixed the issue?

mx
0
 
LVL 75
ID: 20031758
Never mind my last post ... I'm thinking of something else.

Sorry ...

mx
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 20031811
Actually, it was the combination of Val(me.openArgs) and the removal of the un-needed '...'

Me.OpenArgs is passed as a String value, but if the string is a digit (or sequence of digits, composing a Number) then the Val function is not really needed.  The true source of the Type Mismatch error was the use of a String paramater (enclosing the Number passed as OpenArgs in '...') being mis-matched with the Integer field in the bound table.  The Val is simply to convert the string '123' to the Integer 123.

But even is OpenArgs = '123', the line

Me.Filter = "ProjectID = " & Val(Me.OpenArgs)

will result in

Me.Filter = "ProjectID = 123", which will correctly treat the parameter as na Integer - hence no type-mismatch

the code, as originally used by VBRett

Me.Filter = "ProjectID = '" & Me.OpenArgs & "'"

would be interpreted as

Me.Filter = "ProjectID = '123'  "

which results in the Type Mismatch error.

Even

Me.Filter = "ProjectID = '" & val(Me.OpenArgs) & "'"

ends up exactly the same way, and still leads to the type mis-match error.

AW
0
 

Author Comment

by:VBBRett
ID: 20031933
I see.  Thanks for clarifying Arthur.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question