Transform sql query in vba access query 1

[georgesOne]

Hi All:
I have this 'simple' (and working) query

SELECT A.ID, A.[201409ST], Switch([A].[201409ST]="New",1,[A].[201409ST]="M0",2,[A].[201409ST]="M1",3,[A].[201409ST]="M2",4,[A].[201409ST]="M3",5,[A].[201409ST]="vHVM",6,[A].[201409ST]="rhVM",7,[A].[201409ST] Like "*XxX*",8, IsNull([A].[201409ST]),9) AS Rating FROM tblWDefinition As A;

and want to change it into vba, but I cannot.
The full query is more complicated, but I am sure this is the culprit.
Any help available?
Thank you so much, georges

 

[PHV]

strSQL = "SELECT A.ID, A.[201409ST], Switch(A.[201409ST]='New',1,A.[201409ST]='M0',2,A.[201409ST]='M1',3,A.[201409ST]='M2'" _
 & ",4,A.[201409ST]='M3',5,A.[201409ST]='vHVM',6,A.[201409ST]='rhVM',7,A.[201409ST] Like '*XxX*',8, IsNull(A.[201409ST]),9)" _
 & " AS Rating FROM tblWDefinition As A"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

How many possible values are there for [201409ST]? I would consider creating or using a small table of unique values of [201409ST] with the rating numbers. Then you would simply join to the table and display the rating field. You could use Nz() to display the 9 rating.

Duane
Hook'D on Access
MS Access MVP

 

[georgesOne]

Thanks a lot... finally it worked with your help - I obviously always need confirmation by others!
There was actually another error, which I introduced when combining two strings, so even when my 'switch' string was correct, I got an errror (and after many hours I did not realize that anymore).
The field 201409ST indicates a date (September 2014) and accordingly that field name changes every month (used in a kind of 'status' query - that is the other part of the SQL).
Thanks again, I always appreciate your professional support!
Best, georges