Help To extract the field which has Alphanumeric value (1 Viewer)

[SOLVED] Help To extract the field which has Alphanumeric value

i have created the database in which one of the field has alphanumeric value like 5th,6th,7th,9th etc now i have created a query and in query design in the criteria row i just type 9th it will not show the result of all the 9th i tried using "* and like" too but no success.....plss help me..... is there any special string or expression for alphanumeric valued field.
I have also attached an sample file too..

Hi. Which field are we talking about? I don't see any "9th."

sorry my mistake i have uploaded wrong file now i replace the file you can check the database now.
thanks for reply

what happen guys still no reply

In your student details table, class is of type NUMBER but in your table that lists IDs, class is a short text field and the ID is numeric. Your relationship between the classes and student details tables exists but it doesn't appear to be quite right. (More on that in a moment.)

I am confused by what I see because the design view for the student details table shows class as a number YET if I switch back and forth between design view and datasheet view I see 1ST, 2ND, etc. - and the alphabetic part CANNOT be there (in the table). In design view it does not show the class field in the student details table as being a Lookup. It says "NUMBER."

That student table has a one/one relationship between ID of the Classes table and Class of the student details table. Probably should be one/many... every record of the student details table and only matching records of the classes table. But in the absence of a lookup field, I don't see how that thing shows me the translations on-the-fly.

What is crazier is that I do not see a query in what you posted, even though your post #1 says you were having query troubles. I don't know how to address a problem if I can't open the query in design view, and there are only two tables (plus system tables) in what you posted.

EDIT: The sub-datasheet escaped my notice at first. Don't do that. Instead, have a stand-alone translation table that isn't a sub of anything. In the query, you can link the two of them in a JOIN. The query builder will honor the relationship and build the JOIN for you.

However, your problem is that when you have that sub-datasheet, there is a major question as to what field you compare to what field. It is confusing the issue and is a prime example of why we recommend to not use lookup fields bound at the table level.

Hi

See if this solves your problem

View attachment database.zip

The problem was that Class was set as a Lookup in the Students table

I removed the lookup and added a Form to View Students

Thanks For reply Sir I have try Your uploaded Database and it didn't work as i want.
Also i Have uploaded a screenshot of my database
1) Screenshot1: is the table screenshot in which there is a field called class and in this field there are name of classes like 9th,8th,7th etc.
2) screenshot2: is the query windows in this screenshot i have use the following field Roll Number, Student Name, Parentage and Class. now in class field i only want the student who are studied in class 9th so in criteria row i have put 9th.
3) screenshot3: after i click on run button it will show the error instead of the list of student who are studied in class 9th i also use the syntax like like 9th, 9*, like 9* etc but same error. tried of saving the query instead of run still same error.
there what i face the problem i want only 4 filed which is shown in the screenshot2 but only the student who are studied in class 9th...


hope u all undersatnd what is my problem.....
sorry for bad english

Hi

If you construct the query like this:-



It will produce this output:-

thanks for helping my problem is solved.