Problem with Right(), Access 2000 & 2003

J

jfcby

Hello,

I've created a query that accesses a OBDC data base that I cannot
change any data. In my query I have

Field1 Field2 Field3

AHU-A TASK1 DESCRIPTION1
AHU-A TASK2 DESCRIPTION2
AHU-S TASK3 DESCRIPTION3
AHU-S TASK4 DESCRIPTION4
AHU-Q TASK5 DESCRIPTION5
AHU-Q TASK6 DESCRIPTION6
FCU/UV-A TASK7 DESCRIPTION7
FCU/UV-A TASK8 DESCRIPTION8
FCU/UV-S TASK9 DESCRIPTION9
FCU/UV-S TASK10 DESCRIPTION10
FCU/UV-Q TASK11 DESCRIPTION11
FCU/UV-Q TASK12 DESCRIPTION12
FCU/UV-Q TASK13 DESCRIPTION13
TTWHVAC-A TASK14 DESCRIPTION14
TTWHVAC-A TASK15 DESCRIPTION15
TTWHVAC-S TASK16 DESCRIPTION16
TTWHVAC-S TASK17 DESCRIPTION17
TTWHVAC-Q TASK18 DESCRIPTION18
TTWHVAC-Q TASK19 DESCRIPTION19

so I put in field6 Expr2: Right([ROOT_AE_R_PMS_E.PMI_BASIC],1) but my
results in field6 are the last letter from the cells with the most
number of letters and the others are blank.

Field1 Field2 Field3 Field6

AHU-A TASK1 DESCRIPTION1
AHU-A TASK2 DESCRIPTION2
AHU-S TASK3 DESCRIPTION3
AHU-S TASK4 DESCRIPTION4
AHU-Q TASK5 DESCRIPTION5
AHU-Q TASK6 DESCRIPTION6
FCU/UV-A TASK7 DESCRIPTION7
FCU/UV-A TASK8 DESCRIPTION8
FCU/UV-S TASK9 DESCRIPTION9
FCU/UV-S TASK10 DESCRIPTION10
FCU/UV-Q TASK11 DESCRIPTION11
FCU/UV-Q TASK12 DESCRIPTION12
FCU/UV-Q TASK13 DESCRIPTION13
TTWHVAC-A TASK14 DESCRIPTION14 A
TTWHVAC-A TASK15 DESCRIPTION15 A
TTWHVAC-S TASK16 DESCRIPTION16 S
TTWHVAC-S TASK17 DESCRIPTION17 S
TTWHVAC-Q TASK18 DESCRIPTION18 Q
TTWHVAC-Q TASK19 DESCRIPTION19 Q

How can I get the last letter in field1 one to field6? If you need to
see my SQL let me know!

Thank you for your help,
jfcby
 
D

Dirk Goldgar

jfcby said:
Hello,

I've created a query that accesses a OBDC data base that I cannot
change any data. In my query I have

Field1 Field2 Field3

AHU-A TASK1 DESCRIPTION1
AHU-A TASK2 DESCRIPTION2
AHU-S TASK3 DESCRIPTION3
AHU-S TASK4 DESCRIPTION4
AHU-Q TASK5 DESCRIPTION5
AHU-Q TASK6 DESCRIPTION6
FCU/UV-A TASK7 DESCRIPTION7
FCU/UV-A TASK8 DESCRIPTION8
FCU/UV-S TASK9 DESCRIPTION9
FCU/UV-S TASK10 DESCRIPTION10
FCU/UV-Q TASK11 DESCRIPTION11
FCU/UV-Q TASK12 DESCRIPTION12
FCU/UV-Q TASK13 DESCRIPTION13
TTWHVAC-A TASK14 DESCRIPTION14
TTWHVAC-A TASK15 DESCRIPTION15
TTWHVAC-S TASK16 DESCRIPTION16
TTWHVAC-S TASK17 DESCRIPTION17
TTWHVAC-Q TASK18 DESCRIPTION18
TTWHVAC-Q TASK19 DESCRIPTION19

so I put in field6 Expr2: Right([ROOT_AE_R_PMS_E.PMI_BASIC],1) but my
results in field6 are the last letter from the cells with the most
number of letters and the others are blank.

Field1 Field2 Field3 Field6

AHU-A TASK1 DESCRIPTION1
AHU-A TASK2 DESCRIPTION2
AHU-S TASK3 DESCRIPTION3
AHU-S TASK4 DESCRIPTION4
AHU-Q TASK5 DESCRIPTION5
AHU-Q TASK6 DESCRIPTION6
FCU/UV-A TASK7 DESCRIPTION7
FCU/UV-A TASK8 DESCRIPTION8
FCU/UV-S TASK9 DESCRIPTION9
FCU/UV-S TASK10 DESCRIPTION10
FCU/UV-Q TASK11 DESCRIPTION11
FCU/UV-Q TASK12 DESCRIPTION12
FCU/UV-Q TASK13 DESCRIPTION13
TTWHVAC-A TASK14 DESCRIPTION14 A
TTWHVAC-A TASK15 DESCRIPTION15 A
TTWHVAC-S TASK16 DESCRIPTION16 S
TTWHVAC-S TASK17 DESCRIPTION17 S
TTWHVAC-Q TASK18 DESCRIPTION18 Q
TTWHVAC-Q TASK19 DESCRIPTION19 Q

How can I get the last letter in field1 one to field6? If you need to
see my SQL let me know!

Thank you for your help,
jfcby
Click to expand...

I'm only guessing, but maybe the fields in the ODBC database are padded
with spaces to a fixed length. How about using the Trim function to
trim off the extra spaces, and then use the Right function against the
result:

Right(Trim([ROOT_AE_R_PMS_E.PMI_BASIC]),1)
 
J

jfcby

Hello Dirk,

works great

Thank you,
jfcby


Dirk said:
jfcby said:
Hello,

I've created a query that accesses a OBDC data base that I cannot
change any data. In my query I have

Field1 Field2 Field3

AHU-A TASK1 DESCRIPTION1
AHU-A TASK2 DESCRIPTION2
AHU-S TASK3 DESCRIPTION3
AHU-S TASK4 DESCRIPTION4
AHU-Q TASK5 DESCRIPTION5
AHU-Q TASK6 DESCRIPTION6
FCU/UV-A TASK7 DESCRIPTION7
FCU/UV-A TASK8 DESCRIPTION8
FCU/UV-S TASK9 DESCRIPTION9
FCU/UV-S TASK10 DESCRIPTION10
FCU/UV-Q TASK11 DESCRIPTION11
FCU/UV-Q TASK12 DESCRIPTION12
FCU/UV-Q TASK13 DESCRIPTION13
TTWHVAC-A TASK14 DESCRIPTION14
TTWHVAC-A TASK15 DESCRIPTION15
TTWHVAC-S TASK16 DESCRIPTION16
TTWHVAC-S TASK17 DESCRIPTION17
TTWHVAC-Q TASK18 DESCRIPTION18
TTWHVAC-Q TASK19 DESCRIPTION19

so I put in field6 Expr2: Right([ROOT_AE_R_PMS_E.PMI_BASIC],1) but my
results in field6 are the last letter from the cells with the most
number of letters and the others are blank.

Field1 Field2 Field3 Field6

AHU-A TASK1 DESCRIPTION1
AHU-A TASK2 DESCRIPTION2
AHU-S TASK3 DESCRIPTION3
AHU-S TASK4 DESCRIPTION4
AHU-Q TASK5 DESCRIPTION5
AHU-Q TASK6 DESCRIPTION6
FCU/UV-A TASK7 DESCRIPTION7
FCU/UV-A TASK8 DESCRIPTION8
FCU/UV-S TASK9 DESCRIPTION9
FCU/UV-S TASK10 DESCRIPTION10
FCU/UV-Q TASK11 DESCRIPTION11
FCU/UV-Q TASK12 DESCRIPTION12
FCU/UV-Q TASK13 DESCRIPTION13
TTWHVAC-A TASK14 DESCRIPTION14 A
TTWHVAC-A TASK15 DESCRIPTION15 A
TTWHVAC-S TASK16 DESCRIPTION16 S
TTWHVAC-S TASK17 DESCRIPTION17 S
TTWHVAC-Q TASK18 DESCRIPTION18 Q
TTWHVAC-Q TASK19 DESCRIPTION19 Q

How can I get the last letter in field1 one to field6? If you need to
see my SQL let me know!

Thank you for your help,
jfcby
Click to expand...

I'm only guessing, but maybe the fields in the ODBC database are padded
with spaces to a fixed length. How about using the Trim function to
trim off the extra spaces, and then use the Right function against the
result:

Right(Trim([ROOT_AE_R_PMS_E.PMI_BASIC]),1)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report Grouping, Access 2000 & 2003 2

Top