Filtering for numeric value

[Bear]

This is my first time on this site. I just found it and hope it will be of some help. If this question has already been asked and answered I apologize. I spent about 15 minutes looking for my problem and finally gave up.



I am using MS Access 2007 to build a database for my DVD collection. One of the features of the database is an index. I have already made 26 pages (One for each letter of the alphabet) using the Query Wizard and the expression:

Left([Title],1)

and using the needed letter in the criteria. No problem so far.

The Problem is that I have a need to have a query for titles that are or begin with numbers. (example: 2001 a Space Odyssey or 1984). I have spent hours searching through "Help" and cannot find what I need. I am sure it is a simple word or symbol that I need to enter in the criteria but I simply do not know how to phrase my search in "Help".

I have been pulling my hair out! (Sorry - That's a lie. I was already bald. Let's just say I am stressed.)

You may think that this is a silly problem but I would really appreciate any advice.

[RuralGuy]

All of your Titles are strings (text) so I guess I don't understand the problem.

[Bear]

Well, Uh, I, HUH?

I promise I am not trying to be a smarta__! I don't understand why it should matter if the titles are text.

Let me try to explain by going into more detail:

I have a query titled "A" that gives all of the titles that start with the letter "A". The expression I used to make this query is

Left([Title],1)

and I entered the letter "A" into the criteria box. (Query Wizard)

I have 26 queries that are done the same way and I have no problems with them - they work perfectly. However, I have tried several times with different words (Number, Numeric, etc...) in the criteria box and cannot get anything but a blank query when I try to show the titles that are numbers or begin with numbers.

I promise I am not stupid. (Well, I won't admit it anyway) I am sure that there is some very simple answer but I just can't seem to find it. Maybe I am thinking about it wrong.

[alansidman]

I question why you need to have 26 or 27 queries. It would be much simpler to have one query and a search form. In the search form you can put your criteria and it will be transferred to your query and the results created. Here is a tutorial on how its done.

http://www.datapigtechnologies.com/f...earchform.html

Alan

[Bear]

Thank you so much for your reply. I really appreciate you trying to help. However, this does not suit my needs. Because of the way I am using the queries I need them separate. I do not know how to tweak this for my purposes. It is great and I can see that it is something that I will definitely use in the future for another project I am planning. (after I study it for a while and figure it out.)

What I really need is a word or symbol or expression that will select data only if it is a number. I know there is something very simple and I feel as if I am close. Can you help me?

[alansidman]

It is time for you to post your SQL statement for the query that does not work. Until that happens, we are throwing darts while blindfolded.

Alan

[Bear]

The expression I used to make this query is

Left([Title],1)

and I entered the letter "A" into the criteria box. (Query Wizard)

I have tried several times with different words (Number, Numeric, etc...) in the criteria box...

I honestly don't know how to explain it any better.

[Bear]

After a little contemplation I feel that maybe I can explain my problem from a different direction:

When I go into help I am shown many different ways to filter/sort based on a number.

Example: If I want to find a "2" in a certain column I can easily find a way to do it.

I can understand why Access is designed to find numbers and I have done so myself many times. However, I cannot find a method to identify IF a certain entry or piece of data in a column is a number - regardless of its value. That is what I need. A simple decision. Is this a letter or a number? A number? Then show that data in the query.

[RuralGuy]

How about the IsNumeric() function?

[orange]

You could try a query such as

Select * from yourTable
where
Left(Title,1) In ("1","2","3","4","5","6","7","8","9","0")

[Bear]

I had tried "IsNumeric" without the parentheses and it did not work. When I saw your post I got excited. I immediately went to the query, clicked "Design View" and put "()" behind "IsNumeric" that was already there in the criteria box. I saved it, crossed my fingers and clicked "Datasheet View".

NOTHING!

Golly Gee Whilikers this is frustrating. Am I doing something wrong?

I will quickly admit, with no reservations, that I am self taught when it comes to Access. I usually copy and paste things from other people and plug in the values that I need. It's not working now.

What I am trying to say is I am a bare beginner with Access and may not understand terms or methods that may be common knowledge to everyone else. When trying to help, Please be gentle.

I am trying hard and I really do appreciate your help.

[RuralGuy]

Look IsNumeric() up in VBA Help to see how to use it.

[Bear]

Dear Orange,

HUH?

That looked really neat and interesting but I have to admit - I'm lost! I honestly have to admit that I didn't understand a word of that. I want to try it but have a few questions:

1. What do you mean, "Select * from yourTable"? My table has no asterisk. Or do you mean that I should
2. copy and paste this? or
3. copy and past all of it?
4. where? in the criteria box?

I'm so confused! I don't mean to sound so stoopyd! Sorry.

[Bear]

Look IsNumeric() up in VBA Help to see how to use it.

Hello!?!? What's this? VBA Help?

You know, the problem with using Help is you almost have to know the answer before you use it. I have trouble asking the right question. Thanks, I'll try that right now and get back to you.

[RuralGuy]

In a code module type IsNumeric() and highlight it and then hit F1 and see if it will get you there.