If Conditional by XXXXUSXX

[Rony]

Hi
I need to extract/input some data if the following condition is met in queries
Field_Name - [SWIFTID] - data e.g , CHASUS33 , LOYDGB2L

Here in data IF [SWIFID] = character US ( 5th and 6th) then
....................
I am trying to extract if character 5th and 6th is US (United states) / GB
(Great Britian) etc. Query should ignore first 4 characters pick only 5th and
6th characters.

Please guide me.

Thanks

 

[Van T. Dinh]

.... WHERE Mid([SWIFTID], 5, 2) = "US"

HTH
Van T. Dinh
MVP (Access)

 

[Rony]

Thanks it worked, one more request , if i want to update Multiple criteria
Like =
Update "h" if ="us"
Update "y" if = "us"
Update "q" if = "de"

how can i do that.

Is there any way to do the coding in VB or can we conert sql to VB

Thanks in advance
Ron

.... WHERE Mid([SWIFTID], 5, 2) = "US"

HTH
Van T. Dinh
MVP (Access)

Hi
I need to extract/input some data if the following condition is met in queries
Field_Name - [SWIFTID] - data e.g , CHASUS33 , LOYDGB2L

Here in data IF [SWIFID] = character US ( 5th and 6th) then
...................
I am trying to extract if character 5th and 6th is US (United states) / GB
(Great Britian) etc. Query should ignore first 4 characters pick only 5th and
6th characters.

Please guide me.

Thanks

 

[Van T. Dinh]

I am not sure of your question ...

Update what to "h", "y" & "q"???

Describe the Table Structure and what you want to update and what are the
new values.

Remember I can't see your database so you need to describe enough for
potential responders to have enough details to answer your question.

 

[Rony]

Hi
I have field in table with 900 [SWIFTID] AND another field [CAPADQ].
I Need to update through query
iif (Mid([SWIFTID],5,2 ="US", update [CAPADQ] ," H"
iif (Mid([SWIFTID],5,2 ="GB"update [CAPADQ] ,"G"
iif (Mid([SWIFTID],5,2 ="DE"UPdate [CAPADQ] ,"X"

and so on......

I am not able to contruct in the query

Thanks
Rony

 

[Douglas J. Steele]

One way is to have multiple queries:

UPDATE MyTable SET MyField = "H" WHERE Mid([SWIFTID], 5, 2) = "US"

UPDATE MyTable SET MyField = "G" WHERE Mid([SWIFTID], 5, 2) = "GB"

UPDATE MyTable SET MyField = "X" WHERE Mid([SWIFTID], 5, 2) = "DE"

Alternatively, use the SWITCH function:

UPDATE MyTable SET MyField = SWITCH(Mid([SWIFTID], 5, 2) = "US", "H",
Mid([SWIFTID], 5, 2) = "GB", "G", Mid([SWIFTID], 5, 2) = "DE", "X") WHERE
Mid([SWIFTID], 5, 2) IN ("US", "GB", "DE")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi
I have field in table with 900 [SWIFTID] AND another field [CAPADQ].
I Need to update through query
iif (Mid([SWIFTID],5,2 ="US", update [CAPADQ] ," H"
iif (Mid([SWIFTID],5,2 ="GB"update [CAPADQ] ,"G"
iif (Mid([SWIFTID],5,2 ="DE"UPdate [CAPADQ] ,"X"

and so on......

I am not able to contruct in the query

Thanks
Rony







--
Ron

I am not sure of your question ...

Update what to "h", "y" & "q"???

Describe the Table Structure and what you want to update and what are the
new values.

Remember I can't see your database so you need to describe enough for
potential responders to have enough details to answer your question.