devanicole
New member
- Local time
- Today, 07:32
- Joined
- Jun 29, 2020
- Messages
- 2
Hello Everyone!
I am working on building a query that will update a specified field based on the values input into a textbox on a form. Basically, if certain years are entered into the textbox, the query will assign a specific letter to the field. If not, it will assign a different letter. There is also a clause where if one field in the table is equal to a specific value, it is assigned a third letter. Below is the expression I currently have for the field:
Indicator: Switch([Table1].[SpecificNumber]="1507","M",[Table1].[SpecificNumber]<>"1507" and [Table1].[ModelYear] In ([Forms]![YearForm]![SpecificYears]),"B",[Table1].[SpecificNumber]<>"1507" and [Table1].[ModelYear] Not In ([Forms]![YearForm]![SpecificYears]),"L")
When I enter a range of years in the "SpecificYears" textbox on the YearForm, the query sets all values to "L". I'm thinking the issue is that the expression is not able to interpret multiple values from the Form but I'm not sure how else to do this. I've tried it with the years separated by commas, semicolons and Like. I have gotten the query to work if I type in a single year. I've seen Textbox input used as parameters to filter queries but not how to use them within an expression. Any help you can provide would be appreciated!
Thanks,
Dev
I am working on building a query that will update a specified field based on the values input into a textbox on a form. Basically, if certain years are entered into the textbox, the query will assign a specific letter to the field. If not, it will assign a different letter. There is also a clause where if one field in the table is equal to a specific value, it is assigned a third letter. Below is the expression I currently have for the field:
Indicator: Switch([Table1].[SpecificNumber]="1507","M",[Table1].[SpecificNumber]<>"1507" and [Table1].[ModelYear] In ([Forms]![YearForm]![SpecificYears]),"B",[Table1].[SpecificNumber]<>"1507" and [Table1].[ModelYear] Not In ([Forms]![YearForm]![SpecificYears]),"L")
When I enter a range of years in the "SpecificYears" textbox on the YearForm, the query sets all values to "L". I'm thinking the issue is that the expression is not able to interpret multiple values from the Form but I'm not sure how else to do this. I've tried it with the years separated by commas, semicolons and Like. I have gotten the query to work if I type in a single year. I've seen Textbox input used as parameters to filter queries but not how to use them within an expression. Any help you can provide would be appreciated!
Thanks,
Dev