Solved

Substring??? MID????

Posted on 2002-04-30
13
452 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
[X]
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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

726 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