Sql statement for forpro free tables in access

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Sql statement for forpro free tables in access

Author

Topic

Alexos
Starting Member

1 Post

Posted - 2010-04-15 : 14:50:56

I am attempting to write a sql statement to query 4 foxpro free tables in access. i am able to select all the fields and inter join the tables without any problems, but when i run the statement it doesn’t return any results; only the column titles show up, none of the data is returned.

here is what i have so far:

SELECT acpinv.recnum, acpinv.invnum, acpinv.pchord, acpinv.ctcnum, acpinv.vndnum, acpinv.jobnum, acpinv.phsnum, acpinv.dscrpt, acpinv.invdte, acpinv.duedte, acpinv.refnum, acpinv.dscdte, acpinv.invtyp, acpinv.status, acpinv.payee2, acpinv.usrdf1, acpinv.usrdf2, acpinv.dscavl, acpinv.retain, acpinv.setpay, acpinv.amtpad, acpinv.dsctkn, acpinv.invttl, acpinv.adjust, acpinv.freigh, acpinv.ttlpad, acpinv.invbal, acpinv.invnet, acpinv.actper, acpinv.entdte, acpinv.lgrrec, acpinv.usrnme, acpinv.btcnum, acpinv.hotlst, acpinv.shpnum, acpinv.cmpamt, acpinv.usetax, acpinv.ntetxt

FROM ((acpinv INNER JOIN actpay ON acpinv.recnum = actpay.recnum) INNER JOIN actrec ON acpinv.recnum = actrec.recnum) INNER JOIN jobphs ON acpinv.recnum = jobphs.recnum;

I also added

WHERE acpinv.recnum = NOT NULL;

to the end to see if that would give me a different result. it didn't.

i am not a sql expert... obviously... so i appreciate any help you can give.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-04-16 : 04:27:40

1. = not null => is not null
2. no data returned because your join conditions are failing.
step back and select data from each table on it's own and then see if you have the coding/data right to get some results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources