query statement

[LABMAN]

Hi all,
I recently started a business selling 1st edition books and decided to use Access for my records. I have a limited past experience with Access and am OK with the basics of creating tables, forms, reports and basic queries.

I am trying to create a query to extract records by AUTHOR SURNAME. Everything I've tried so far requires me to type the FULL surname and forename to extract the info. The "author" column contains the full name, eg "DEIGHTON Len" (no comma).

Can anyone help, please?

[missinglinq]

Not sure exactly how you're doing this query, but if your author names are always in the same format, i.e. "Deighton Len" then

[CODE=vb]AuthorSurname = Left(AuthorWhol eName,instr(Aut horWholeName, " "))[/CODE]
will parse out the surname, and you can use this.

If you're using a query as a base for a form, and are then searching to pull up your data, you could also use the same thing to create a calculated surname field in your query. In a new field enter

[CODE=vb]AuthorSurname: Left(AuthorWhol eName,instr(Aut horWholeName, " "))[/CODE]
and now you can search on just the surname field.

Welcome to TheScripts!

Linq ;0)>

[LABMAN]

Hi!
Many thanks for your welcome and for your prompt reply.

OK, well I did say I only had limited experience of Access!!

What I am trying to do is produce a report detailing a particular author's books that I have in stock. So I created a parameter query but the only parameter I used that gave me any sort of result was typing "Like[DEIGHTON Len]" (my quotation marks). I can then type in any author in the parameter box with the desired result. However, I thought I would be able to create a query so that I can just type the Surname, or even simply "DEI" to get the desired result.

The other thing is, I assume your solution was using Visual Basic. Now i'm not asking for a tutorial, but could you please give me a clue as to how to use your suggestion! I would be ever your servant!!!!

kindest regards