troubleshooting Question

SQL in VB

Avatar of Howler
Howler asked on
Visual Basic Classic
6 Comments1 Solution173 ViewsLast Modified:
I've got a database open with a table of accidents, vehicles and casualties.
I want to delete all the records from the accidents table that a from a year that the user enters. The year is in a column called [Date] dd/mm/yyyy. I'm having problems trying to delete the records because I can't seem to get the syntax correct. I've tried deleting records from other columns and that works fine, so I must be having problems with the wildcard and like syntax. Someone suggested using datepart, but couldn't get that right either. Here's what I tried

sSQL = "DELETE * FROM [Attendant circumstances] WHERE [Date]LIKE '*' " & txtYear.Text
g_DataConnection.Execute sSQL

then I want to delete all records from Casualties table if they have a Crash reference number which does not exist in the corresponding crash reference column in Attendant circumstances table. e.g if 12345 was in the crash ref field in casualty details but not in attendent circumstances!crash reference then I would like the entire row to be deleted.

sSQL = "DELETE * FROM [Casualty Details] WHERE [Casualty Details]![Crash Reference] NOT IN ([Attendant circumstances]![Crash Reference])"
g_DataConnection.Execute sSQL

sorry if this is easy, but i'm having problems.

thanks

rich
   
ASKER CERTIFIED SOLUTION
Flynnious

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros