0

I have homework from Microsoft Access for school and one of the tasks was to make a search form in query, where you type for example 1. grade, 2. grade or 1st year, etc... But the field contains the name of the classes such as IT1, IT2, E1A, E3C, etc... So I could not just make a search form with [Enter Class:] or Like [Enter Class:]&"*" in the criteria column. So I was thinking, that I can use the IIF statement but it doesn't work how I imagined. I think there is a problem with the LIKE statement. I read on forums, that the IIF statement should be used in the field column but I tried the simple examples in the criteria and it worked just fine. So my question is, how I can make a search form, where I type numerous letters with one certain number in it BUT only the number will be read and returned with the same number in classes. Example: I type to search 1. grade and the value will return classes IT1, E1A, E1B, E1C, E1D. This is the line I used in the criteria column:

Like IIf([Enter the grade:]="*1*";"*1*";IIf("*2*";"*2*";IIf("*3*";"*3*";IIf("*4*";"*4*"))))

Just to qualify, I'm beginner in access and databases overall, so there is big possibility, that I'm missing something.

Thank you for help! Cheers!

ErikHG10
  • 1
  • That criteria is nonsensical. You want user to type "1. grade" or "1st year"? Input will always start with a number? Better to give users choices with a combobox or listbox. Is the assignment specifically to use a dynamic parameterized query? Have you studied using VBA? – June7 Mar 23 '21 at 21:59

1 Answers1

0

Assuming input will always start with a number and you want all values containing that number, consider:

WHERE fieldname LIKE "*" & Val([Enter grade]) & "*"

Also assumes values contain only single digit.

June7
  • 19,874
  • 8
  • 24
  • 34
  • Thanks a lot! That helped, I just put the ```LIKE "*" & Val([Enter grade]) & "*"``` in criteria and it worked! Is there also a way to do it if we consider, that the number can be on any place in the text? – ErikHG10 Apr 06 '21 at 16:57
  • If user types number anywhere else in string, will need a VBA custom function. https://stackoverflow.com/questions/7239328/how-to-find-numbers-from-a-string – June7 Apr 06 '21 at 18:08