SELECT query help

[jribbe101]

Hi everyone,

I am trying to pull a value from a field in a program database and display it on my custom form. I am not sure if its my syntax or what, but it keeps crashing with the SELECT query i am using. I want to grab the G/Liability expiration date from the vndcrt table. Here is the query I have most recently tried.

[SELECT vndcrt.expdte FROM vndcrt
WHERE vndcrt.recnum = subcon.vndnum AND vndcrt.dscrpt = 'G/LIABILITY']

here is an attachment showing the tables and data I am trying to retrieve.

www.indesignmedia.com/tables.jpg

Thanks for any help.

[Phil Weber]

You're referencing the subcon table in the WHERE clause, but it's not listed in the FROM clause. Also, you're not passing a specific recnum value. Try something like this:

SELECT vndcrt.expdte
FROM vndcrt
WHERE vndcrt.recnum = 616
AND vndcrt.dscrpt = 'G/LIABILITY'

[jribbe101]

I have to reference the subcon table because every subcontract that is printed will usually reference a different subcon.vndnum other than 616, depending on the job. So i am trying to tell it to match the subcon.vndnum (vendor #) with the vndcrt.recnum (record #) and also match where the description reads G/LIABILITY.

so if the vendor number 616 in this case matches the vendor certification record number of 616 then we have a match, and to pull the expiration date from the G/LIABILITY.

make sense. thx

[Phil Weber]

Are you trying to print the records for ALL subcontractors where the description is G/LIABILITY, or only those records for a specific subcontractor or job? If it's the latter, you must somehow provide the ID of the subcontractor or job you're looking for.

[jribbe101]

yea, I want to do the later, for a specific job. The vendor id is the same as the record number in the vndcrt table. Is there any way to have it match the 2 and if its a match for the vendor i want to print, it will pull the value for that vendor?

I posted this in another forum, so the info may or may not help. these are screens from the actual program master builder i am trying to make the query for. i am printing these reports from the subcontracts screen of 6-7-1

www.indesignmedia.com/masterbuilder.jpg

[Phil Weber]

if its a match for the vendor i want to print...

That's the key: How does the query know which vendor you want to print? If you want to pass in the record number you're viewing in the subcontractors table, you can do this:

SELECT vndcrt.expdte
FROM vndcrt INNER JOIN subcon ON vndcrt.recnum = subcon.vndnum
WHERE subcon.recnum = [record #]
AND vndcrt.dscrpt = 'G/LIABILITY'

[jribbe101]

the program knows which vendor to print because on the screen I use to print the subcontracts, it has what vendor I am using displayed under vendor name.
If you look on the masterbuilder.jpg picture, the top box of 6-7-1 has the vendor displayed there. So whichever subcontract I am printing, it shows what vendor its for.

I also tried your last query and it seemed to crunch numbers longer before it crashed the program, which is better than what I was getting before.