Solved Data Type Mismatch in Delete Query SQL

[LanaR]

The following SQL is throwing a 3464 Run time error Data type mismatch in criteria expression

    'SQL statment to delete exsiting record(s) relating to current wine
    StrSQL = "DELETE TBL_WineGrape.WineID, TBL_WineGrape.WineGrapeID " & _
            "FROM TBL_WineGrape " & _
            "WHERE (((TBL_WineGrape.WineID)= '" & [Text12] & "'));"

I'm referring to [Text12] in other SQL in other events, without issue. The only thing I can think of is that in this instance [Text12] is being populated by Left(OpenArgs, Len(OpenArgs) - 4) (yes [Text12] is holding the value I'm expecting) whilst in all other instance it is simply being populated by the raw OpenArgs. I can't for the life of me see where I've gone wrong

 

[arnelgp]

you probably need to translate [Text12] into a Tempvar.

 

[Galaxiom]

Debug.Print the value of StrSQL before you execute it and see what it saying.

 

[LanaR]

@arnelgp Bingo thank you once again

 

[LanaR]

So I'm guessing the issue was because [Text12] was being used as part of a WHERE clause. As in all other instances, it was simply providing a value to be inserted into a table.

 

[Gasman]

I would have said ID was likely numeric, but you were treating the criteria as a string with the ' marks?
Plus you cannot delete individual fields in a table, just the row, so Delete * would work just as well, especially if you have more fields in the table?

https://docs.microsoft.com/en-us/of...ference/delete-statement-microsoft-access-sql

 

[Galaxiom]

So I'm guessing the issue was because [Text12] was being used as part of a WHERE clause.

You have concatenated Text12 into the SQL command so it should be fine. However I believe if you did a Debug.Print of Strsql you would find that there is nothing being inserted.

Hard to be sure without knowing more about your code but it may be that OpenArgs has only populated the Text property of Text12 but the Value property has not yet been updated when you are reading it.