Type mismatch error

[gchichester]

I can't figure out why I keep getting a "type mismatch" error when this SQL statement runs from my form.
I have this same statement as a query and it runs correctly.
Data type for the "UsedBookingNumbers" fields are Text.
Users may not have a booking number at the time of entry so they add a " - " to the field for aesthetics purposes, it's using in a Email subject line.
I'm using a table to store and then lookup numbers to prevent the duplicating of booking numbers.
Because there can be a lot of " - " and I'm checking for used numbers at data entry, the user gets my message that the number has been used.
So I'm trying to delete only records with a " - ". before the user enters another record.

I've tried using the Report Close and Lost Focus Event to run the DoCmd.RunQSL but both get the same error.

SQL Code used:

Sql_ClearNoBookingNumber = "Delete tblUsedBookingNumbers.UsedBookingNumbers FROM tblUsedBookingNumbers
WHERE (((tblUsedBookingNumbers.UsedBookingNumbers)=" - "))"
DoCmd.RunSQL Sql_ClearNoBookingNumber


Also I tried modifying this statement to prevent Inserting the " - " .

Sql_InsertAsiaBookingNo = "INSERT INTO tblUsedBookingNumbers (UsedBookingNumbers)SELECT [Asia Booking Notice].[Booking no] FROM [Asia Booking Notice] WHERE ((([Asia Booking Notice].[Booking no])Is Not Null ))"

WHERE ((([Asia Booking Notice].[Booking no])Is Not Like " - " ))"
And again I got the same error.

Thank You for any and all feedback

Gilc

 

[namliam]

You cannot use " inside ""

Try this instead:
Sql_ClearNoBookingNumber = "Delete tblUsedBookingNumbers.UsedBookingNumbers FROM tblUsedBookingNumbers
WHERE (((tblUsedBookingNumbers.UsedBookingNumbers)=' - '))"
DoCmd.RunSQL Sql_ClearNoBookingNumber

 

[gchichester]

That was just to simple!
I thing I will remember this one.
Thank you

Gilc

 

[DCrake]

Another solution would be to create a new query that filtered out all the records with a '-' in it

Then simply

DoCmd.RunSQL "Delete * From QueryName"

 

[gchichester]

That helps!
Thanks for your input,
Gilc