Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2002
    Posts
    63

    Unanswered: Data type Mismatch error?????

    Hi, I have a form called BackupRulesCreate with a number of text boxes. I then a have a button that executes some VBA, this is supposed to stick all the data together into an INSERT SQL statement that I will then execute. But I'm getting stuck! two of the fields are date fields, so include slashes, when I try to add these values to the variable sqlrule i get the error message data type mismatch just after echoing out mooo4.

    HELP!!!!!

    thanks
    Code:
    Private Sub cmdAddRule_Click()
    
    Dim sqlrule As String
    Dim sqlitem As String
    Dim message As String
    
    On Error GoTo Err_cmdDelRule_Click
    
        message = MsgBox("mooooo", vbCritical, "mooo2")
        message = MsgBox("mooooo", vbCritical, [Forms]![BackupRulesCreate].[Machine_ID].[Value])
        
    
        sqlrule = "INSERT INTO BackupRules (Machine_ID, description, startdate, enddate, interval) VALUES ("
    
        sqlrule = sqlrule + [Forms]![BackupRulesCreate].[Machine_ID].[Value] + ", "
        message = MsgBox("mooooo", vbCritical, "mooo3")
        sqlrule = sqlrule + [Forms]![BackupRulesCreate].[description].[Value] + ", "
        message = MsgBox("mooooo", vbCritical, "mooo4")
        sqlrule = sqlrule + [Forms]![BackupRulesCreate].[startdate].[Value] + ", "
        message = MsgBox("mooooo", vbCritical, "mooo5")
        sqlrule = sqlrule + [Forms]![BackupRulesCreate].[enddate].[Value] + ", "
        message = MsgBox("mooooo", vbCritical, "mooo6")
        sqlrule = sqlrule + [Forms]![BackupRulesCreate].[interval].[Value] + ");"
        
        message = MsgBox(sqlrule, vbCritical, sqlrule)
        
        Rem DoCmd.RunSQL sqlrule
        
        
    Exit_cmdDelRule_Click:
        Exit Sub
    
    Err_cmdDelRule_Click:
        MsgBox Err.description
        Resume Exit_cmdDelRule_Click
        
    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try using & instead of +
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2002
    Posts
    63
    still does it

  4. #4
    Join Date
    Dec 2002
    Location
    Glasgow, UK
    Posts
    100
    what format is your date? - you have to use the american date format when executing a sql statement. (mm/dd/yyyy)
    Access XP & WinXP Pro

  5. #5
    Join Date
    Jun 2002
    Posts
    63
    format is mm/dd/yyyy

    however I'm not actually executing the SQL statement (haven't got that far yet, I'm merely creating one) - this should be so easy, i'm just concatenating string values!

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What a strange one eh?

    Have you tried debugging all your values to ensure they're what you think they are?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jun 2002
    Posts
    63
    I think there might be somthing with my DB, it just crashed access 5 times, and now the form has gone????????

  8. #8
    Join Date
    Jun 2002
    Posts
    63
    have resolved, if I don't use .[Value] then it works!!! weird.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Bizarre... the default property when you call a data-bearing control is .value

    Learn something new everyday I guess, access has its quirks.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Dec 2002
    Location
    Glasgow, UK
    Posts
    100
    I'm sure I had the opposite problem before ie I never used the .value and it never worked but when I did use it the code worked.

    as you say, access has it quirks
    Access XP & WinXP Pro

Posting Permissions

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