Update To Field in query using Function with Select Case (1 Viewer)

[kvar]

This doesn't seem like it should be complicated, but for some reason I'm not figuring it out.

I just want to update a field in a table based what is found in another field in another table.
So, I have a Public Function with a Select Case statement. Then I'm trying to call to that function in the "Update To" field of an update query.

I'm having a hard time trying to find the proper Syntax to do this.
After Public Function.....do I want to first put the field that I'm updating, or the field that I'm looking at to determine what to update to?

In the following (very rough) example, Description would be a field in the "ItemsOrdered" table, and Product would be the field that I want to update in the "Contracts" table. The tables are joined (1 to many), and in the Criteria row for the Product field I have "Unassigned".

EX. Public Function Product(Description As String) As String

Select Case Description
Case "Advance"
Product = "Honors"
Case "Other"
Product = "Alternate"
Case Else
Product = "Unknown"
End Select

Then in the query, the only column I would have is the Product field from the "Contracts" table, and in the Update To field it is Productxxxx???

I believe in the update to field it should be FunctionName(Product) for this example. But that doesn't seem to work.

Or do I need to set the table name for where Description is somewhere in the Function?
Or maybe a Do While Product = "Unassigned" type of loop? Although I was thinking that this would be covered by putting that in the criteria of the query.

Any examples or references would be GREATLY appreciated! I think I just need to see it in use to get myself on the right track. I'm not finding any posts, or references anywhere to a Select Case statement being used when the fields are in 2 seperate tables. And my attempts just to try it with fields in the same table (for testing) have resulting it the fields being updated to just Null or seemingly blank values. Although it does only update the records where Product = "Unassigned", so I guess I've got that part right!

 

[Zaeed]

what is the sql you are using? Is this a coded sql update or done using the query builder?

I just did a test with the following that worked..

UPDATE Table1 SET Table1.name = testCase(1);

Public Function testCase(bob As Integer) As String
    Select Case bob
    
    Case 1
        testCase = "one"
    Case 2
        testCase = "two"
    Case 3
        testCase = "three"
    End Select
End Function

 

[smig]

in the update to... field it should be:
=Product([Description])


assume [Description] is one of your fields in the table
product IS the function name

 

[kvar]

Zaeed - In your example, is "Bob" the field that I'm updating or the field that I'm searching??

So in no way do I need to clarify in the statement that I'm using 2 different tables? I really thought that's where my issue was coming from. My mistake.

Smig - Are you supposed to name the function after the field name that you're updating? So, in the "Update To" row of the field I want to update, it's "Name of Function([Name of Field to Search]) ?? Is that correct? And not what table to searcht for that field in, just the name of the field?

Thank you both for your help!

 

[smig]

it seems you need to learn the basic idea of subs and functions.

sub do some job, any job you put in it.

function on the other hand is like some of a black box that you put something in it and it will give you something back.
the data you get back will be called by the name of the function itself.

let's say you create a function that will add 1 to the number you send it. it will look like this:

function AddOne(Number)

AddOne = Number + 1

End Function


Now you want to call this function and you will use a sintex similer to this:

MyOldNumber = X ' ---- Put any number here
MyNewNumber = AddOne(MyOldNumber)


in your example the function called PRODUCT and what you put in is DESCRIPTION.
the end result is the result of PRODUCT


In access you can't call a sub to do a job, so many times you create functions that will do jobs but this is NOT the main idea of functions.


some functions simpy use no data inside. example:
now() ' ---- this function will give you the time of now in unformated style.

but if you want to have only the date of now, you will use the FORMAT function, and you send this function more then a single data - you send it the required date, and the style of format..
DateOfNow = Forma(Now(), "dd/mm/yyyy")



Now an example for the Update query.

let's say you have a table with a field called DateOfFlight that it full of many dates. you also have another field that is called WeekDay. the last one is empty now, and you want to fill it with the correct week days based on the correct dates.

you create a simple function:

function CreateWeekDayBasedOnDate(AnyDate)

CreateWeekDayBasedOnDate = format(AnyDate, "wwww")

end function


now back to the update query...
in this query you put the WeekDay field and set it's Update to... into:
=CreateWeekDayBasedOnDate([DateOfFlight])




I realy hope this make it more clear now

 

[Zaeed]

also make sure you're including both tables in your update query..

UPDATE ItemsOrdered, Contracts SET Contracts.Product = Product([Description])