Simple Substring Query

[pvmoor@verizon.]

I'm a rank beginner in Access, and apologize in advance for such an elementary question, but I need the basic syntax to run a Delete Query on the substring of the first character in a field. The question is #8 in Shelly Cashman's Office 2003 Course One p.AC168, Delete all records where Description starts with S.

p.AC137 deals with Delete Query, but only covers deletions based on whole fields. I tried 'Help', but ended up at a Microsoft site offering me a 30MB file on SQL. I may well get deeper into Access after a more advanced course, but this one is for beginners.

TIA for any help with this.

~~~Peter.

PS I thought I created a profile with an alias, but I seem to have ended up with pvmoor@verizon. instead. Oh, well.

 

[Sergeant]

DELETE * FROM MyTable WHERE Left(MyField,1) = 'S'

 

[pvmoor@verizon.]

Thanks Sergeant, but this didn't work for me. Perhaps I should have mentioned that I'm not writing code, but merely using [New Object >Query > Design View > Query Type > Delete Query]. At this stage I have on-screen the following:
Field: Description
Table: Item
Delete: Where
Criteria: ???
where ??? is the syntax I need to delete records where Description begins with 'S'. I tried all sorts of combinations of your code following WHERE, but so far, no cigar.

DELETE * FROM MyTable WHERE Left(MyField,1) = 'S'

 

[neileg]

If you switched to SQL view in the query you could use Sergeant's code.

Working in the Query Design Grid, create a new calculated field like this:
MyNewField:Left(MyField,1)
Then add a criterion to this calculated field of "S"

 

[Sergeant]

I'm a rank beginner in Access, and...

I should have read that a little closer.

I hope what Neileg added has enabled you to get it working.

 

[pvmoor@verizon.]

Thanks guys. It worked. Sorry to trouble y'all with such trivia. If anyone has the Cashman 2003 Course One, you'll see what I mean. The assigment on p.AC168(#8) asks for this query, but it doesn't get saved for submission, so if you can't find the solution using Help, you can ignore it. As a former dBaseIII programmer, I didn't want to shirk it, so I asked the instructor, who referred me to p.AC137, which does't cover it.

I don't know how long they've been using Cashman 2003 for this course at my local JC. Maybe this is the first year, and no-one has yet asked how to do this query. OTOH, if this is the 2nd or 3rd year, it's possible that no-one has bothered to look into it, including the instructor, simply because there are no consequences to failure.

One thing puzzles me. In dBase, when you delete a record, you can always recall it until you do a purge (pack), and the record will never show up again, but it's still there. In Access, there was a warning that the deletion is permanent. Please say it isn't so!

BTW, my 'major' will be Web Design, in May. Basic MS-Office is the pre-requisite. I found no inspiration in Word or Excel for side-tracking, until I came to compare Access with dBaseIII, and suddenly I found myself drawn to coding again. So I could find myself changing my major to VBA, (in the distant future). (I am now 66, but am optimistic enough to continue to make reference to distant futures )