asked on

Substring??? MID????

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?

Paurths

mid

mid(string, startposition, length)

cheers
Ricky

Paurths

or,

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

vbjohn

ASKER

Both of those do not work. Thanks anyways.

Paurths

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*"));

Paurths

ldb, should be ldg ofourse...

vbjohn

ASKER

Yes that query works.

Paurths

what is the exact sql-string u are using now?
and what is your goal?

vbjohn

ASKER

Yes that query works.

ASKER CERTIFIED SOLUTION

Paurths

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vbjohn

ASKER

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-

vbjohn

ASKER

Using another Question....

Paurths

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...

vbjohn

ASKER

Thanks for the help on the Wildcard thing!