Looking for part of a date

[Howlsta]

Simple problem I want to delete all records from a table where the year is the same as entered in a text box.

The format of the date column is dd/mm/yyyy

can't seem to get the syntax correct to look for the year within the date. I've searched but can't find an example, although it must have been asked before.

i've tried this and some other things as well

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

thanks

Rich

 

[RV]

Try this:

DoCmd.RunSQL "DELETE * FROM [Attendant circumstances] WHERE [Date] LIKE" _
& "'*' & Forms!<formname>!textboxname AND Forms!<formname>!textboxname Is Not Null"


P.S:

1) DON'T use spaces in tablenames
2) DON"T use Date as a columnname as it's a reserved word in Access.

RV

 

[Howlsta]

Thanks, but I Still can't get it working

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

P.S:

1) have you defined sSQL?

Yes defined, I already tested it with other fields and it deleted them okay, but as i'm just looking for the yyyy part having problems with the wildcard bit (I reckon)
2) DON'T use spaces in tablenames
Totally agree, but this is the way the databases have been created and that's the format they want to stick with - the full program has been on the market for years
3) DON"T use Date as a columnname as it's a reserved word in Access.
Same as above - I would change it if I could!

thanks, any other suggestions welcome

Rich

 

[Rich]

Shouldn't it be DatePart("yy",[MyField])=DatePart("yy",Forms! etc)

 

[Howlsta]

Sorry forgot to mention, this is in VB code, so I won't need to put in forms!etc at the moment, will I?. Well, when I tested it with another field it worked without it anyway.

 

[Howlsta]

thanks

ps got the problems sorted

sSQL = "DELETE * FROM [Attendant circumstances] WHERE (Year([Date]))LIKE " & MyYear

had to write a whole query, not just the field. Hence the second query became:

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