Help To extract the field which has Alphanumeric value

[akhon]

[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..

 

[theDBguy]

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

 

[akhon]

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

 

[akhon]

what happen guys still no reply

 

[The_Doc_Man]

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.

 

[mike60smart]

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

 

[akhon]

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

 

[mike60smart]

Hi

If you construct the query like this:-



It will produce this output:-

 

[akhon]

thanks for helping my problem is solved.