Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Substring??? MID????

Posted on 2002-04-30
13
448 Views
Last Modified: 2008-03-17
I am trying to get this query to work and I am getting this error on this part...


ISNULL((SELECT SUM(dbo_pm30200.DOCAMNT) FROM dbo_PM30200
WHERE SUBSTRING(dbo_pm30200.DOCNUMBR,5,3) = "LDG" and dbo_upr00100.employid = dbo_pm30200.vendorid),0)

It says that it doesn't recognize SUBSTRING().

SUBSTRING
---------
APR-LDG
MAY LDG
OCT/LDG
LDG ACD
FEB LDG 02

(5) Records.


What else could I use instead?
0
Comment
Question by:vbjohn
  • 7
  • 6
13 Comments
 
LVL 12

Expert Comment

by:Paurths
ID: 6981820
mid


mid(string, startposition, length)

cheers
Ricky
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6981826
or,

... where dbo_pm30200.DOCNUMBR like '*LDG*'...
0
 

Author Comment

by:vbjohn
ID: 6981847
Both of those do not work.  Thanks anyways.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 12

Expert Comment

by:Paurths
ID: 6981882
ok,

the name of your table is 'dbo_pm30200', and the name of the field is 'DOCNUMBR', correct?


does this query work?

SELECT dbo_pm30200.DOCNUMBR
FROM dbo_pm30200
WHERE (((dbo_pm30200.DOCNUMBR) Like "*ldb*"));
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6981884
ldb, should be ldg ofourse...
0
 

Author Comment

by:vbjohn
ID: 6981896
Yes that query works.
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6981916
what is the exact sql-string u are using now?
and what is your goal?
0
 

Author Comment

by:vbjohn
ID: 6981927
Yes that query works.
0
 
LVL 12

Accepted Solution

by:
Paurths earned 50 total points
ID: 6981931
SELECT Sum(dbo_pm30200.DOCAMNT) FROM dbo_pm30200
WHERE (((dbo_pm30200.DOCNUMBR) Like "*ldb*") AND (([dbo_upr00100].[emplyid])=[dbo_pm30200].[vendorid]));
0
 

Author Comment

by:vbjohn
ID: 6983495
It seems like you can not have a query inside of a query.

For example:

SELECT RTRIM(LASTNAME) + ', ' + RTRIM(FRSTNAME)
    + ' ' + RTRIM(MIDLNAME) AS NAME, INACTIVE, ISNULL
        ((SELECT (dbo_UPR00400.MTDWAGES_1 + dbo_UPR00400.MTDWAGES_2+ dbo_UPR00400.MTDWAGES_3 + dbo_UPR00400.MTDWAGES_4+ dbo_UPR00400.MTDWAGES_5 + dbo_UPR00400.MTDWAGES_6+ dbo_UPR00400.MTDWAGES_7 + dbo_UPR00400.MTDWAGES_8+ dbo_UPR00400.MTDWAGES_9 + dbo_UPR00400.MTDWAGES_10+ dbo_UPR00400.MTDWAGES_11 + dbo_UPR00400.MTDWAGES_12) AS Wages
FROM dbo_UPR00400
WHERE dbo_UPR00100.EMPLOYID = dbo_UPR00400.EMPLOYID AND
dbo_UPR00400.PAYTYPE = 5 AND
dbo_UPR00400.PAYRCORD = '1SPSES'), 0) As PDTotal
FROM dbo_UPR00100 INNER JOIN dbo_UPR00400 ON dbo_UPR00400.EMPLOYID = dbo_UPR00100.EMPLOYID
ORDER BY dbo_UPR00100.EMPLOYID;


Is there a way to do it in Access?



John-
0
 

Author Comment

by:vbjohn
ID: 6983815
Using another Question....
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6983934
sure u can use another 'Select...' statement in another

u should have made clear u wanted a 0, or a number with that ISNULL statement...
0
 

Author Comment

by:vbjohn
ID: 6986494
Thanks for the help on the Wildcard thing!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question