I know Excel VBA but New to MS Access VBA...question re: creating function

[yomero]

Hi, can you help me translate this function into VBA for MS access?
Totally new, I have tried withouth luck. Your help is much appreciated. Once I know whihc objects I need I could take it from there.

Table name: Data
Field1: Platform
Field2: Screenview

Function UpdateColumn

for each field in Table(Data).field1
 select case true
  case like '*xyz*'
     Field2 = 'GoodJob'
  case like 'WWW'
     Field2 = 'GreatJob'
end select
 
end function

 

[Norie]

You wouldn't use code for something like that in Access, you'd use a query.

 

[mrmmickle1]

Maybe try using IIF or SWITCH in a Query

is it possible to using select case statement in access query? - Stack Overflow

 

[yomero]

You wouldn't use code for something like that in Access, you'd use a query.

I have about 4.cases. i am able to Update using a query, but only one case at a.time. What would.be the syntax in access to update all 4 cases in the same Update query?

 

[Norie]

Like this.

UPDATE Data SET ScreenView = Switch(Platform Like '*xyz*', 'GoodJob', Platform Like 'WWW', 'GreatJob')

 

[yomero]

Like this.

UPDATE Data SET ScreenView = Switch(Platform Like '*xyz*', 'GoodJob', Platform Like 'WWW', 'GreatJob')

I am getting an "UPDATE syntax error". Where am I wrong? Thx

UPDATE [RAW - Active]
SET [RAW - Active].Screentype = Switch ([RAW - Active].[Client Version]) Like '*iOS*','iOS', [RAW - Active].[Client Version]) Like '*Android*','Android', [RAW - Active].[Terminal Model]) Like '*Windows*','PC',[RAW - Active].[Terminal OS]) Like '*OSX*','MAC');

 

[Norie]

You appear to have added an unneeded ) after the field name in the Switch statement.

UPDATE [RAW - Active] SET [RAW - Active].Screentype = Switch([RAW - Active].[Client Version] Like '*iOS*','iOS',[RAW - Active].[Client Version] Like '*Android*','Android',[RAW - Active].[Terminal Model] Like '*Windows*','PC',[RAW - Active].[Terminal OS] Like '*OSX*','MAC');

 

[yomero]

Just to close this thread.

I created a query and with the Expression Builder I added Expressions in new columns such as:

Screen: IIf([00_Data]![Terminal Model] Like '*iPhone*','iPhone',IIf([00_Data]![Terminal Model] Like '*AndroidTablet*','Android_Tablet',IIf([00_Data]![Terminal Model] Like '*iPad*','iPad',IIf([00_Data]![Terminal Model] Like '*Windows*','PC',IIf([00_Data]![Terminal OS Version] Like '*OSX*','MAC','Android_SP')))))

With this query then I create another table that I use to do...everything else I need to do .

Thanks for your replies.
Yomero