Filter upper case

[emartel]

Hi,
Is it possible to filter out entries beginning with a capital letter with a query?
Ive tried Like UCase, but that doesn't work, and there is no upper case wild card.
(I am creating a book index and want to have a look only at the entries that are people's names)

many thanks

 

[richary]

Use

>="A" and <="Z"

as your criteria in the query for the name field

 

[john471]

Try using criteria something like the below; see if it works for you.

InStr(1,Left([tblYourTableName].[YourFieldName],1),UCase$(Left([tblYourTableName].[YourFieldName],1)),0)>0

Someone else may well have a more efficient solution.

 

[emartel]

thanks vvery much!!

 

[richary]

Actaully, I take that back (to a degree). Access doesn't appear to include names begining with "Z" in the above.

You would need (I think)

Asc([Name])>="65" And Asc([Name])<="90"

This seems cumbersome. There must be a simpler way??

 

[emartel]

Bah, they dont work. Well, richary's doesnt Im afraid, and John, thanks for your reply but it would take me longer to set up a form than it would to just manually go through my own index. Thanks anyway. If anyone knows of a quick simple solution....?

 

[john471]

...John, thanks for your reply but it would take me longer to set up a form than it would to just manually go through my own index. Thanks anyway. If anyone knows of a quick simple solution....?

You don't ned to set up a form to use my suggestion. Just modify the statement in my previous post to suit your underelying table/query and field names (where I've used "tblYourTableName" and "YourFieldName") then put that as criteria in a query.

 

[emartel]

thanks for that, but I am confused and can't get it to work. Do you mean that I simply replace [tblYourTableName].[YourFieldName] for my names in the query criteria??

InStr(1,Left([tblYourTableName].[YourFieldName],1),UCase$(Left([tblYourTableName].[YourFieldName],1)),0)>0

 

[john471]

Yes, that is exactly what I am saying.

Can you post the SQL that you have that gives you the data you are trying to examine ?

From design view of the query, go to the View menu, than choose SQL view, and copy what you've got there and paste it into a reply.

 

[emartel]

Here it is: The first part is from when I was having a go at Richary's suggestion, but it gives the field names.

SELECT IndexArt.[Indexed Word], IndexArt.Pages, IndexArt.Coloured, IndexArt.Notes
FROM IndexArt
WHERE (((IndexArt.[Indexed Word]) Between "A*" And "Z*") AND ((IndexArt.Notes) Not Like "*"))
ORDER BY IndexArt.[Indexed Word];

thanks
Ed

 

[john471]

OK,

I don't know what your intention is with the "not like "*"" bit, are you saying you want to exclude anything that has a note ? Consider using Is Null instead.

Here it is disregarding the "Notes" criteria

SELECT IndexArt.[Indexed Word], IndexArt.Pages, IndexArt.Coloured, IndexArt.Notes
FROM IndexArt
WHERE (((InStr(1,Left([IndexArt].[Indexed Word],1),UCase$(Left([IndexArt].[Indexed Word],1)),0))>0))
ORDER BY IndexArt.[Indexed Word];

Here is one including your notes Not like "*"

SELECT IndexArt.[Indexed Word], IndexArt.Pages, IndexArt.Coloured, IndexArt.Notes
FROM IndexArt
WHERE (((IndexArt.Notes) Not Like "*") AND ((InStr(1,Left([IndexArt].[Indexed Word],1),UCase$(Left([IndexArt].[Indexed Word],1)),0))>0))
ORDER BY IndexArt.[Indexed Word];

And here is one using Is Null instead

SELECT IndexArt.[Indexed Word], IndexArt.Pages, IndexArt.Coloured, IndexArt.Notes
FROM IndexArt
WHERE (((IndexArt.Notes) Is Null) AND ((InStr(1,Left([IndexArt].[Indexed Word],1),UCase$(Left([IndexArt].[Indexed Word],1)),0))>0))
ORDER BY IndexArt.[Indexed Word];

Let me know how you go.

 

[emartel]

Fantastic!!
That's really kind of you; You can't possibly understand how boring creating this Art Index is, and how much further boredom you will have saved me by showing me how to do this!!!
many many thanks.

 

[john471]

I'm going to take that to mean you got the result you wanted

 

[emartel]

Yes, just about!!!