overly long formulas in Update Queries (1 Viewer)

Try putting the colour to number translations in a table and join it into the query.

use a function to do this, put in a module:

public function fnColorNumber(vInput As Variant) As String
vInput = vInput & ""
Select Case vInput
Case Is =""
fnColorNumber="X"
Case Is = "red"
fnColorNumber = "01"
Case Is = "yellow"
fnColorNumber = "02"
Case Is = "Blue"
fnColorNumber = "03"
Case Is = "orange"
fnColorNumber = "04"
Case Is = "green"
fnColorNumber = "05"
Case Is = "purple"
fnColorNumber = "06"
Case Else
fnColorNumber = "99"
End Select
End function

now use this function in your query or form/reports textbox controlsource.
for query: Expr: fnColorNumber([field])

for textbox controlsource:

=fnColorNumber([field])

arnelgp said:

use a function to do this, put in a module:

Click to expand...

The advantage of the function is that it definitely would not cause the recordset to be non-updateable which might happen depending on how the query is structured.

However the joining the table in the query has considerable advantages. Firstly, more colour codes can be added to the table without changing any code. Secondly its performance will be significantly better than the function.

also, using any expression in the query (whether function, or just plain concatenation of fields) will render the resulting recordsets non-updeatable.

Thank you for the replies!

Galaxiom and arnelgp offered two methods of approach. I'm a newbie and both solutions seemed a bit daunting. After thinking about it overnight I thought I might be able to do something with Galaxiom's suggestion:

> Try putting the colour to number translations in a table and join it into the query.

So I thought that a Switch formula such as follows might do the trick:

Expr1: Switch([Table_1]![data_1]=[Table_2]![colors],[Table_2]![numbers])

Where:

[Table_1]![data_1] is the the table and field where I want to check color values, and then entered translated values into a second field in the same Table.

[Table_2], the Table with translated pairs, has two relevant fields: [colors], [numbers] (which I indicated already in the Switch formula I posted earlier):

[color], [numbers]
red,01,
yellow,02,
blue,03,
orange,04,
green,05,
purple,06,
Is Null,x)

You might tell me if I'm on the right track.

I went to see if this would work in the actual Table I'm working on, in a Select Query first,. The results showed some promise, but something else bizarre was happening.

Now that I was entering two Tables into the Select Query and not just one:

[Table_1]: the Table where I was checking (and eventually translating) values
[Table_2]: the Table with the translation pairs

something odd was happening.

When I only had [Table_1] in the Select Query and view-ran it I gave 203 rows in the result. This is because [Table_1] has 203 rows and the Query did 203 rows of translations.

When I had both [Table_1] and [Table_2] in the Select Query and view-ran it I got 3045 rows in the result. This is exactly 15 times more than the 203 rows I got earlier with just the one [Table_1]. When I counted the number of rows in [Table_2], the table with the translation pairs, there were 15 rows. I concluded that the number of rows in [Table_2], 15 of them, somehow caused the number of rows in the results to be multiplied 15 times. The rows that successfully got translated though was less than 203, so it seems that the translations did not get multiplied 15 times, whereas the total number of rows did.

In the actual Table I am working on, the Table that I treated as a [Table_2] for the Select Query is also sourced as a Combo Box from a Form. I was wondering if the Combo Box link might have something to do with the bizarre behavior of multiplying the results by the number of rows in the translating table [Table_2]. I tried deleting the Combo Box link from that translating Table [Table_2] that I was working on. This didn't make a difference, as soon as I entered the second Table into the Query, running the Query would give me the multliplied-by-15 number of rows.

Now I'm going to go and start a Database from scratch and keep it free of Combo Box links and see if I will still get the bizarre multiplying of rows in a Select Query. If I don't get a similar bizarre multiplication of rows then I can deduce that the Combo Box Links are probably causing the bizarre row multiplication in a Select Query.

Any insights?

Any insights?

Click to expand...

1. Action queries (UPDATE, INSERT, MAKE TABLE) are usually band-aids used to cover up poorly structured tables or a misunderstanding of what a relational database is. Why are you running an UPDATE query?

2. You are getting more rows than you expected because of the JOIN in your query. Let's use a simple example:

Table1
T1ID
1
2
2
4


Table2
T2ID
1
1
2
2
2



Table1 has 4 records, Table2 has 6 records. If you were to make an INNER JOIN query from Table1 to Table2 ON the ID field in each, that query would return 8 rows:

T1ID, T2ID
1, 1
1, 1
2, 2
2, 2
2, 2
2, 2
2, 2
2, 2

The number of rows that get returned is the product of how many match between the 2 tables. ID=1 has 1x2 records, ID=2 has 2x3 records, ID=4 has 1x0 records. Therefore, you have duplicates in at least one of your tables in the field you are joining it to the other table.

3. It's a database, but your colors in a table. And make numbers numeric--'01' probably isn't a number in your database but short text.

Yes your "weird" results were because of the lack of a join. Without a JOIN, each record in Table1 matched each record in Table 2 yielding [NumberOfRecordsInTable1] x [NumberOfRecordsInTable2] total records in your query. In very fringe cases would you ever have a query without a JOIN between tables.

For the issue you initiall posted, you are trying to convert a color to a number. Let's call the table your query is based on YourTable and the field with the color string [field]. First you would move your color/number values to a table so:

Colors
ColorName, ColorNumber
red, 1
yellow, 2
blue, 3
...
...

Then instead of a Switch where you store all your data, you would JOIN Colors to YourTable. You would JOIN them via [field] to [ColorName]. Then instead of the Switch field, you would simply bring ColorNumber into your query.

I would suggest a 1 paragraph explanation in plain english (no database jargon) of what real world system you are modeling with your database. Just tell us what it is your organization does and what you hope to achieve with your database. Also, a screenshot of your relationships or just upload the database itself.

personal circumstances:

I am working on a project on my own. This project is simply a database to store and manage data about the software I've accumulated. This database will become a model for further databases I will create to store and manage data, presumably just personal matters unless other needs arise.

the database:

Everything with the database is working except for one issue. I will try and describe those elements involved with just that issue.

Really only one Table [Table_1] is involved with the issue, but a second Table [Table_2] is indirectly involved and could be made to be involved directly. I will provide simplified versions of the Tables I am actually working on.

[Table_1]
![Value]

high
medium
low

If I do an ascending alphanumeric sort on the Value Field I would get:

[Table_1]
![Value]

high
low
medium

This is not what I want. I want the sort to follow the values of the values: high > medium > low. The importance of this is in my being able to observe other Fields in the Table as they are sorted according to the rating I have given them, or the importance I have assigned them.

I could add numbers to the values, placing them at the beginning of values.

[Table_1]
![Value]

01 high
02 medium
03 low

An ascending sort on these values will give me the order that I want. But it doesn't look so good to have these numbers, more so maybe for a Form.

Solution:

create a second column that would be autopopulated with appropriate numerical values determined by a Switch function.

[Table_1]
![Value], ![Number]

high, 01
medium, 02
low, 03

So in an Update Query, I've recently learned that a Switch formula written as follows will automatically populate the ![Number] Field with the numbers I need for a desired value-based sort.

Switch([Table_1]![Value]="high","01",
([Table_1]![Value]="medium","02",
([Table_1]![Value]="high","03")

So far so good. Things are working.

If the Switch formula goes beyond a certain length Access gives the error that there is too much text.

I tried splitting the long Switch formula into two columns in the Update Query. That is not allowed—a Query sourcing the same Field twice. I split the long Switch formula into two Queries. The problem with that is that each Query will wipe out the values in

![Number] that were entered by the other Query. I might be able to get around this problem if I new how to write an equation that would go approximately as follows:

Switch([Table_1]![Value]="high","01", ([Table_1]![Value]="medium","02", ([Table_1]![Value]="low","03", If False, Ignore) How would I correctly write the "If False, Ignore" part. This would have Access ignore any value in [Table_1]![Value] that is neither high, medium or low and not wipe out any number entries in the corresponding [Table_1]![Number] cell. I just would need to know how to write the "If False, Ignore" part. I've actually started a new thread in the Forum asking about this. If you (whoever you may be) might know the answere, feel free to provide it in this thread. the method using an additional Table A few people at the Forum have suggested that I create a second Table listing the value and translation pairs. That I then import them into the Query. Such a second Table might go as follows: [Table_2] ![Value], ![Number]

I would suggest a 1 paragraph explanation in plain english (no database jargon) of what real world system you are modeling with your database. Just tell us what it is your organization does and what you hope to achieve with your database. Also, a screenshot of your relationships or just upload the database itself.

Click to expand...

I don't know about others, but I'm not reading that last post of yours.