You’re offline. This is a read only version of the page.
205
*This post is locked for comments
I solved it by converting the TXTFIELD to a varchar, it seems that VB has problems displaying text data type or memo
this was the code
CREATE VIEW dbo.CheckRegRp2t
AS
SELECT dbo.PM00200.VNDCHKNM, dbo.PM20000.VCHRNMBR, dbo.PM20000.DOCDATE,dbo.PM20000.DOCNUMBR, dbo.PM20000.DOCAMNT,
dbo.PM20000.TRXDSCRN,dbo.PM20000.VOIDED, dbo.PM20000.CHEKBKID,dbo.PM20000.PPSAMDED, CONVERT(VARCHAR(4000),dbo.SY03900.TXTFIELD) AS TXTFIELD, dbo.PM20100.APTODCNM,
dbo.PM20100.APTODCDT,dbo.PM20100.APPLDAMT,dbo.PM20100.WROFAMNT, dbo.PM20100.DOCTYPE,dbo.PM20100.DISTKNAM, dbo.PM20100.APFRDCNM,
dbo.PM20100.APTVCHNM
FROM dbo.PM00200
INNER JOIN dbo.PM20000
ON dbo.PM00200.VENDORID = dbo.PM20000.VENDORID
INNER JOIN dbo.SY03900
ON dbo.SY03900.NOTEINDX = dbo.PM20000.NOTEINDX
INNER JOIN dbo.PM20100
ON dbo.PM20000.VCHRNMBR = dbo.PM20100.VCHRNMBR
UNION ALL
SELECT dbo.PM00200.VNDCHKNM, dbo.PM30200.VCHRNMBR,dbo.PM30200.DOCDATE,dbo.PM30200.DOCNUMBR, dbo.PM30200.DOCAMNT,
dbo.PM30200.TRXDSCRN,dbo.PM30200.VOIDED, dbo.PM30200.CHEKBKID, dbo.PM30200.PPSAMDED,CONVERT(VARCHAR(4000),dbo.SY03900.TXTFIELD) AS TXTFIELD, dbo.PM30300.APTODCNM,
dbo.PM30300.APTODCDT, dbo.PM30300.APPLDAMT,dbo.PM30300.WROFAMNT, dbo.PM30300.DOCTYPE,dbo.PM30300.DISTKNAM, dbo.PM30300.APFRDCNM,
dbo.PM30300.APTVCHNM
FROM dbo.PM00200
INNER JOIN dbo.PM30200
ON dbo.PM00200.VENDORID = dbo.PM30200.VENDORID
INNER JOIN dbo.SY03900
ON dbo.SY03900.NOTEINDX = dbo.PM30200.NOTEINDX
INNER JOIN dbo.PM30300
ON dbo.PM30300.APTODCNM = dbo.PM30200.VCHRNMBR
Have you tried to run the above query in SQL server?
For example:
Select *From dbo.CheckRegRp2t where DOCDATE between 'Enter start date' and 'Enter end date'
If the query is not return any values for the column TXTFIELD, check the same column value in the table SY03900 for the particular document.
Hope this helps!!!
I seem to have a new problem, Im using crystal reports and connected the table I made to this particular report, I successfully dragged all my fields into the report, the VNDCHKNM, DOCTYPE, etc... and they are all displaying in my report.
Heres my problem when in Visual Basic, when I click a button of mine that shows the report all of the fields are correctly displayed except for TXTFIELD which is a memo field it just shows an empty space with no text.
Here is my code
rsCheck.Open "SELECT * from CheckRegRp2t where DOCDATE between '" & strStartDate & "' and '" & strEndDate & "' " & _
" order by apfrdcnm ", gconPGCI
Set crystal = New CRAXDRT.Application
Set report = crystal.OpenReport("D:\Great-Plains Reports\Reports\rptCheckRegister.rpt")
strmontha = Val(strmonth)
report.DiscardSavedData
report.ReportTitle = " CHECK REGISTER FOR " & MonthName(strmonth) & " " & intYear & " "
report.Database.SetDataSource rsCheck
crptCheck.ReportSource = report
crptCheck.ViewReport
rsCheck.Close
Actually I think its working now this is my new Query you were right it was a UNION that I needed
CREATE VIEW dbo.CheckRegRp2t
AS
SELECT dbo.PM00200.VNDCHKNM, dbo.PM20000.VCHRNMBR, dbo.PM20000.DOCDATE,dbo.PM20000.DOCNUMBR, dbo.PM20000.DOCAMNT,
dbo.PM20000.TRXDSCRN,dbo.PM20000.VOIDED, dbo.PM20000.CHEKBKID,dbo.PM20000.PPSAMDED, dbo.SY03900.TXTFIELD, dbo.PM20100.APTODCNM,
dbo.PM20100.APTODCDT,dbo.PM20100.APPLDAMT,dbo.PM20100.WROFAMNT, dbo.PM20100.DOCTYPE,dbo.PM20100.DISTKNAM, dbo.PM20100.APFRDCNM,
dbo.PM20100.APTVCHNM
FROM dbo.PM00200
INNER JOIN dbo.PM20000
ON dbo.PM00200.VENDORID = dbo.PM20000.VENDORID
INNER JOIN dbo.SY03900
ON dbo.SY03900.NOTEINDX = dbo.PM20000.NOTEINDX
INNER JOIN dbo.PM20100
ON dbo.PM20000.VCHRNMBR = dbo.PM20100.VCHRNMBR
UNION ALL
SELECT dbo.PM00200.VNDCHKNM, dbo.PM30200.VCHRNMBR,dbo.PM30200.DOCDATE,dbo.PM30200.DOCNUMBR, dbo.PM30200.DOCAMNT,
dbo.PM30200.TRXDSCRN,dbo.PM30200.VOIDED, dbo.PM30200.CHEKBKID, dbo.PM30200.PPSAMDED,dbo.SY03900.TXTFIELD, dbo.PM30300.APTODCNM,
dbo.PM30300.APTODCDT, dbo.PM30300.APPLDAMT,dbo.PM30300.WROFAMNT, dbo.PM30300.DOCTYPE,dbo.PM30300.DISTKNAM, dbo.PM30300.APFRDCNM,
dbo.PM30300.APTVCHNM
FROM dbo.PM00200
INNER JOIN dbo.PM30200
ON dbo.PM00200.VENDORID = dbo.PM30200.VENDORID
INNER JOIN dbo.SY03900
ON dbo.SY03900.NOTEINDX = dbo.PM30200.NOTEINDX
INNER JOIN dbo.PM30300
ON dbo.PM30300.APTODCNM = dbo.PM30200.VCHRNMBR
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (