Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Question Unanswered: mismatched data types

    I'm having a problem with another form i'm working on that uses code similar to the error handling code from my other most recent post.

    So summarize, I have multiple unbound text boxes and two combo boxes in an unbound form, see. The text boxes are for inputting data (employee ID, first and last name) and the combo boxes are used to select a job code and a site code from the respective tables jobs and sites.

    Once the user has filled out all the fields and selected the site and job code, there is the OnClick() event for the Enter button, which is as follows:

    Code:
    Private Sub BtnEnter_Click()
        
        Dim db As Database
        Dim rst As Recordset
        Dim err As Boolean
        
        Dim ProviderID As Long
        Dim Credential As String
        
        Credential = Me.CmbCredential
        ProviderID = Me.CmbProviderID
    
    ... 
    
        Dim LTotal As Single
        LTotal = DCount("[intID]", "[tblContractorCredentials]", "[intID]='" & ProviderID & "' AND [strCredentialID] = '" & Credential & "'")
        
        If LTotal > 0 Then
            MsgBox "A record for the credential and provider you have entered already exists.", vbCritical, "Duplicate Entry"
            err = True
        End If
                 
        If Not err Then
             With rst
                .AddNew
                .Fields("intID") = ProviderID
                .Fields("strCredentialID") = Credential
                .Fields("dteExpires") = Me.txtExpires
                .Update
            End With
        
            rst.Close
            db.Close
        
            Me.CmbCredential = ""
            Me.CmbProviderID = ""
            Me.txtExpires = ""
        
            Set rst = Nothing
            Set db = Nothing
        End If
    End Sub
    The point is, "check and see if there is already a record with info for this provider and credential", because intID and strCredentialID make up the primary key of the tblContractorCredentials table.

    when I test it, I get this error:

    "mismatched data types in criteria expression"

    and it takes me here:

    Code:
    LTotal = DCount("[intID]", "[tblContractorCredentials]", "[intID]='" & ProviderID & "' AND [strCredentialID] = '" & Credential & "'")
    I don't understand. the problem is with the strCredentialID field and Credential variable, because when I remove that bit from the criteria expression, it works fine, albeit not the way I need it to work.

    strCredentialID is defined in the table properties as a text field. Credential is declared as a String variable. where. is. the. problem.

    I'm GUESSING it has something to do with ...the combo box? but I just can't figure out why. when I put in break points in the code and check the values in my variables, they are all right.

    Could this have something to do with the combo box the credentialID is being selected from?
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Credential might be String / Text, but what about ProviderID / intID ??

    DCount("[intID]", "[tblContractorCredentials]", "[intID]='" & ProviderID & "' AND [strCredentialID] = '" & Credential & "'")
    There. Is. The. Problem!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    Quote Originally Posted by StarTrekker
    Credential might be String / Text, but what about ProviderID / intID ??



    There. Is. The. Problem!

    No, that isn't the problem, because as I said, when I take out the second part of the criteria, the function executes just fine, with no errors. But I need to test both of those fields [intID], and [strCredentialID].

    I'm getting a bunch of weird values in the debugger when I put breaks in the code, andI just... I'm just about ready to go a different direction with this.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Few ideas.

    Put a break in your code and in the immediate window,

    debug.print Ltotal or ("[intID]", "[tblContractorCredentials]", "[intID]='" & ProviderID & "' AND [strCredentialID] = '" & Credential & "'")

    To see exactly what is going on...

    strCredentialID is an interesting field name - prefixed with str to suggest a string, suffixed with ID which might suggest a numeric value - This is a string is it not (Both in the table and what is being stored in the combo)?

    Have a look at rowsource of the combo-box CmbCredential.

    Also debug.print Credential - It may not be returning what you think it is!
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by astukoarai86
    No, that isn't the problem
    But it is a valid point. It may not be the direct cause to the particular problem you are experiencing but it is syntactically incorrect and should be corrected.

    Having the right code will increase the speed of operation and decrease the chance of it falling over
    George
    Home | Blog

  6. #6
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    Quote Originally Posted by garethdart
    Put a break in your code and in the immediate window,

    debug.print Ltotal or ("[intID]", "[tblContractorCredentials]", "[intID]='" & ProviderID & "' AND [strCredentialID] = '" & Credential & "'")

    To see exactly what is going on...

    strCredentialID is an interesting field name - prefixed with str to suggest a string, suffixed with ID which might suggest a numeric value - This is a string is it not (Both in the table and what is being stored in the combo)?

    Have a look at rowsource of the combo-box CmbCredential.

    Also debug.print Credential - It may not be returning what you think it is!

    well, I've been stepping thru this with the debugger, but the variable values are just fine on their own... I don't know why it's thinking the field [strCredentialID] is numeric... because it's defined as a text field in the table properties...

    and yes... I now realize it's bad to have text primary keys...

    There's stuff going on when I run the debugger that's really confusing, and I don't think I can even begin to explain it.. : \ But I'll keep trying.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  7. #7
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    Quote Originally Posted by georgev
    But it is a valid point. It may not be the direct cause to the particular problem you are experiencing but it is syntactically incorrect and should be corrected.

    Having the right code will increase the speed of operation and decrease the chance of it falling over

    what's a valid point?

    And what about what is syntactically incorrect? You didn't quote anything relevant.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by atsukoarai86
    what's a valid point?
    http://www.dbforums.com/showpost.php...49&postcount=2

    Quote Originally Posted by atsukoarai86
    And what about what is syntactically incorrect? You didn't quote anything relevant.
    Code:
        Dim ProviderID As Long
    
        ... "[intID]='" & ProviderID & "' AND...
    Text goes between quotes, numerics do not.
    George
    Home | Blog

  9. #9
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    Quote Originally Posted by georgev
    http://www.dbforums.com/showpost.php...49&postcount=2



    Code:
        Dim ProviderID As Long
    
        ... "[intID]='" & ProviderID & "' AND...
    Text goes between quotes, numerics do not.

    Well, I don't know what to tell you about that, because it works fine when I take the second half of the criteria expression out.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  10. #10
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Suggestions

    Put a breakpoint on this line:-

    If LTotal > 0 Then

    then in the immediate window:

    *debug.print Ltotal

    *then debug.print "[intID]" & " " & "[tblContractorCredentials]" & " " & "[intID]='" & ProviderID & "' AND [strCredentialID] = '" & Credential & "'"

    Then post *the details here please...

    What is the rowsource of the combo box .CmbCredential?

    How many columns are their?

    Is the first column the bound column?

    Is the first column maybe invisible and holding a numerical primary key?

    ?Look at the (query?) behind the rowsource - How many columns? Datatypes?

    PS "Text goes between quotes, numerics do not" - Heed this advice!
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  11. #11
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    Quote Originally Posted by garethdart
    Put a breakpoint on this line:-

    If LTotal > 0 Then

    then in the immediate window:

    *debug.print Ltotal

    *then debug.print "[intID]" & " " & "[tblContractorCredentials]" & " " & "[intID]='" & ProviderID & "' AND [strCredentialID] = '" & Credential & "'"

    Then post *the details here please...

    What is the rowsource of the combo box .CmbCredential?
    The row source is the Credentials table.

    How many columns are their?
    there are two columns.
    Is the first column the bound column?
    None of the columns are bound
    Is the first column maybe invisible and holding a numerical primary key?
    there is no numerical primary key for that table. I designed this entire database myself.
    ?Look at the (query?) behind the rowsource - How many columns? Datatypes?
    I don't know what you mean by the query behind the rowsource. I didn't base this form on a query...
    PS "Text goes between quotes, numerics do not" - Heed this advice!

    I'll try this...
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  12. #12
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Exclamation

    Quote Originally Posted by garethdart
    Put a breakpoint on this line:-

    If LTotal > 0 Then

    then in the immediate window:

    *debug.print Ltotal

    *then debug.print "[intID]" & " " & "[tblContractorCredentials]" & " " & "[intID]='" & ProviderID & "' AND [strCredentialID] = '" & Credential & "'"

    Then post *the details here please...

    What is the rowsource of the combo box .CmbCredential?

    How many columns are their?

    Is the first column the bound column?

    Is the first column maybe invisible and holding a numerical primary key?

    ?Look at the (query?) behind the rowsource - How many columns? Datatypes?

    PS "Text goes between quotes, numerics do not" - Heed this advice!

    alright...

    mmkay... I opened up the Immediate window... and I typed in..
    debug.Print Ltotal

    debug.print "[intID]" & " " & "[tblContractorCredentials]" & " " & "[intID]='" & ProviderID & "' AND [strCredentialID] = '" & Credential & "'"
    all it gives me is [intID] = '0' And [strCredentialId] = ' '

    Ltotal = 1...even though the information I'm trying to enter is unique.... ProviderID = 10084657, and Credential = "CPR".

    /cry
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Wahey!

    So here's the issue.
    you know those quotes (the ones that don't go around numbers)... Well, if you have '' (no space), this is converted to a NULL (or zero, I forget) value implicitly. But what you've posted contains a single space and a single space cannot be implicitly converted to a number.

    So here's what you do.
    1. validate the data input (make sure it's a number!)
    2. then use the value in your concatenation WITHOUT the single quotes.
    George
    Home | Blog

  14. #14
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Good

    Hopefully now you have learned;

    How to debug with a breakpoint.

    Listen to georgev!

    I would also suggest that you use a naming convention for your variables and other system objects maybe:

    http://en.wikipedia.org/wiki/Leszyns...ing_convention

    (There are others)

    This can make it quicker / easier for you (and others) to debug problems like this and will also mean it is less likely for you to us reserved words for your variables.

    PS I should have said Listen to 'georgev!'
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  15. #15
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Thumbs up

    Quote Originally Posted by georgev
    Wahey!

    So here's the issue.
    you know those quotes (the ones that don't go around numbers)... Well, if you have '' (no space), this is converted to a NULL (or zero, I forget) value implicitly. But what you've posted contains a single space and a single space cannot be implicitly converted to a number.

    So here's what you do.
    1. validate the data input (make sure it's a number!)
    2. then use the value in your concatenation WITHOUT the single quotes.

    lol.... you know what's funny... Just before you posted this, I changed the code...
    I changed the code to this:
    Code:
    LTotal = DCount("[intID] AND [strCredentialID]", "[tblContractorCredentials]",_
                "intID = " & ProviderID & " AND strCredentialID = '" & Credential & "'")
    and now, it works just perfectly! whee hee.

    i kinda started thinking about it, and essentially, the DCount function is just like a SELECT statement, where the first parameter is the select clause... so I figured... maybe this is giving me an error on every entry because I'm... only selecting the intID column? <.< I don't know exactly why, but it works now.

    oh, and as you will see, I took the single quotes off the ProviderID variable.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •