Problem: dealing with Switch formulas that are too long.
step 1: The following Switch formula will work in an Update Query.
Switch([field]= "red","01",
[field]= "yellow","02",
[field]= "blue","03",
[field]= "orange","04",
[field]= "green","05",
[field]= "purple","06",
[field] Is Null,"x")
step 2: However, if a Switch formula such as the above were some length longer there would be a point where Access would return the error that "The text is too long to be edited."
I understand that there is a limit of about 14 conditions in a Query. You can get around this by using the concatinator "&" and repeating the Switch formula as follows:
Switch([field]= "red","01",
[field]= "yellow","02",
[field]= "blue","03",
[field]= "orange","04")&
Switch([field]= "green","05",
[field]= "purple","06",
[field] Is Null,"x")
But there seems to also be a limit to the total amount of text in the formula as well. With such a situation breaking up the formula with inclusions of
&Switch
will not help.
step 3: I tried splitting the formula into two columns in the Update Query, each headed by the same heading (the same Field). It doesn't seem that Access will allow having two columns in a Query sourcing the same Field. Is there any way to split a formula between two columns in a Query where it does work?
step 4: My solution to the above was to split the formula between two Update Queries. The problem with that is that each time one of the two Update Queries is run it will delete the values that the other Query entered.
I was thinking that if I entered script that said something like: "Ignore the remaining fields and do nothing to the value in the corresponding field" that that might keep the Query from wiping out the results of the other of the two Queries. I tried something to achieve this, but it didn't work.
Ideally, if I could have all this (a long Switch formula) done in one Query that would be best. Is that possible? If I can't get around splitting the formula up into two Queries, how do I get around the cancelling of the value change done by the other of the two Queries?
Thank you
step 1: The following Switch formula will work in an Update Query.
Switch([field]= "red","01",
[field]= "yellow","02",
[field]= "blue","03",
[field]= "orange","04",
[field]= "green","05",
[field]= "purple","06",
[field] Is Null,"x")
step 2: However, if a Switch formula such as the above were some length longer there would be a point where Access would return the error that "The text is too long to be edited."
I understand that there is a limit of about 14 conditions in a Query. You can get around this by using the concatinator "&" and repeating the Switch formula as follows:
Switch([field]= "red","01",
[field]= "yellow","02",
[field]= "blue","03",
[field]= "orange","04")&
Switch([field]= "green","05",
[field]= "purple","06",
[field] Is Null,"x")
But there seems to also be a limit to the total amount of text in the formula as well. With such a situation breaking up the formula with inclusions of
&Switch
will not help.
step 3: I tried splitting the formula into two columns in the Update Query, each headed by the same heading (the same Field). It doesn't seem that Access will allow having two columns in a Query sourcing the same Field. Is there any way to split a formula between two columns in a Query where it does work?
step 4: My solution to the above was to split the formula between two Update Queries. The problem with that is that each time one of the two Update Queries is run it will delete the values that the other Query entered.
I was thinking that if I entered script that said something like: "Ignore the remaining fields and do nothing to the value in the corresponding field" that that might keep the Query from wiping out the results of the other of the two Queries. I tried something to achieve this, but it didn't work.
Ideally, if I could have all this (a long Switch formula) done in one Query that would be best. Is that possible? If I can't get around splitting the formula up into two Queries, how do I get around the cancelling of the value change done by the other of the two Queries?
Thank you