Update Query based on Textbox Values (1 Viewer)

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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:32
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

You are correct. You can try a different approach, or you could also check out this article, in case it helps.
 

devanicole

New member
Local time
Today, 07:32
Joined
Jun 29, 2020
Messages
2
Thanks for your reply! Is there a way to add this to my expression above though? I already have an input based on a listbox on my form. I'm not sure the Where clause would work for this since I'm not filtering anything out.

Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:32
Joined
Oct 29, 2018
Messages
21,357
Thanks for your reply! Is there a way to add this to my expression above though? I already have an input based on a listbox on my form. I'm not sure the Where clause would work for this since I'm not filtering anything out.

Thanks!
HI. Either approach should work in your calculated column, but you could try using the InStr() approach first.
 

Users who are viewing this thread

Top Bottom