By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,299 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,299 IT Pros & Developers. It's quick & easy.

Data not shows correctly

P: n/a
Hi:
When i run my query many information are cut specially Expr, Expr2 and
not shows correctly:

SELECT tblCustomers.AccountNum, tblCustomers.ReturnDate,
tblCustomers.CreditAmount,
Right(Trim([CityStateZip]),Len(Trim([CityStateZip]))-InStr(1,[CityStateZip],",
")) AS Zip, Left([AccountNum],5) AS Corp, tblCustomers.BoxType,
tblCustomers.Comments, Left([Name],InStr(1,[Name]," ")-1) AS Expr,
Right(Trim([Name]),Len(Trim([Name]))-InStr(1,[Name]," ")) AS Expr2,
"Yes" AS NewWork, tblCustomers.BoxQty, tblCustomers.ReturnMethod,
tblCustomers.ImportDate, tblCustomers.ConverterNumbers, "E Mail" AS
RecdHow, "KDB" AS RecdDetail, Left([RequestDate],10) AS ReqDate, "Open"
AS ReqStat, "Open" AS ErrTyp, tblCustomers.Subject,
Left([Sender],InStr(1,[Sender]," (")-1) AS SenderInits,
Right([Sender],4) AS SenderCorp, Left((Right([Sender],8)),3) AS CDID,
tblCustomers.TechNmbr
FROM tblCustomers;

tblCustomers
------------
RequestDate: WED SEP 13 09:23:59 EDT 2006
Name: STEPHEN NG
Sender: SNG
Street: 1 MICHAEL RD
CityStateZip: SYOSSET NY, 11791
Subject: EQUIPMENT CREDIT/RESEARCH - 297665
ReturnMethod: TECH
AccountNum: 07801-300112-03
ReturnDate: 09/13/2006
BoxType:
BoxQty
CreditAmount: 123
ConverterNumbers: 123,CABLE BOX 234,SMART CARD 345,OOL/OV MODEM
Comments: TEST
TechNmbr: 123

---------------------------------------------------------------------
ReqDate: WED SEP 1
Corp: Left([AccountNum],5): Left([AccountNum],5)->07801
SenderCorp: Right([Sender],4): SNG
AccountNum: 07801-300112-03
Return Date: 09/13/2006
CreditAmount: 123
Zip:
Right(Trim([CityStateZip]),Len(Trim([CityStateZip]))-InStr(1,[CityStateZip],",
")): 11791
Corp: Left([AccountNum],5): 07801
BoxType:
Comments: TEST
Expr: Left([Name],InStr(1,[Name]," ")-1):
Expr2: Right(Trim([Name]),Len(Trim([Name]))-InStr(1,[Name]," ")):
TEPHEN NG
NewWork: Yes
BoxQty:
ReturnMethod: TECH
ImportDate: 10/3/2006 11:25:55 AM
ConverterNumbers: 123,CABLE BOX 234,SMART CARD 345,OOL/OV MODEM
RecdHow: E Mail
RecdDetail: KDB
ReqStat: Open
ErrTyp: Open
Subject: EQUIPMENT CREDIT/RESEARCH - 297665
SenderInits: #Error
CDID: SN
TechNmbr: 123

In Expr I need Customer First Name and Expr2 the Last Name. I try to
cut the name by means of space between first and last name in Expr1 and
Expr2 but it shows wrong value.

Thanks.

Oct 3 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
If there is a leading space you will not get what you want. You will
get what you actually are getting.

Right(Trim([Name]),Len(Trim([Name]))-InStr(1,[Name]," ")):
converts to
Right(Trim([Name]),10-1):
should be

Right(Trim([Name]),Len(Trim([Name]))-InStr(1,Trim([Name])," ")):
And the expr would get nothing since the FIRST character is a space.

Ron

Oct 3 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.