Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Where Primary key is equal to recordset item

Hello
     I'm trying to pull different data from two tables in the same ODBC Database. The first recordset draws fine, the second runs into problems when I try to use the first set item from the first list as the criteria to find the proper line in the second table. Vend Num is the Primary key in the second table, please help
Thanks
Samme
Set rsBidPO = gdbOp.OpenRecordset("Select BidHist.VendNum, BidHist.Cost, BidHist.EstWt, BidHist.MOQ, BidHist.Lead, BidHist.FOB, BidHist.BidDate, BidHist.Valid, BidHist.Notes From BidHist Where CatNum = " & fa.TextMatrix(rowctr, 5))

Set rsBidPO2 = gdbOp.OpenRecordset("Select VendHdr.MinOrd, VendHdr.PaymentTerms From VendHdr Where VendNum = " & rsBidPO(0))

Open in new window

0
samme
Asked:
samme
  • 3
  • 2
1 Solution
 
enriquecadalsoCommented:
Hello. To receive more response try to define what do you mean with "runs into problems". Any error messages? Or not the expected results?
0
 
sammeAuthor Commented:
Here is the error I'm getting
37000[Microsoft][ODBC SQL Server]Line 1: Incorrectsyntax near "L527"

The value of rsBidPO(0) is "7L527"

VendNum in both tables is Text feilds

If there is any other info that will help please let me know
0
 
sammeAuthor Commented:
Also if I remove " Where VendNum = " & rsBidPO(0)" from the line it will run the code without throwing an error, it just leaves the recordset empty.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
3_SCommented:
You must enclose rsBidPo(0) within single quotes since it contains characters as you stated

VendNum in both tables is Text feilds
Set rsBidPO2 = gdbOp.OpenRecordset("Select VendHdr.MinOrd, VendHdr.PaymentTerms From VendHdr Where VendNum = '" & rsBidPO(0) & "'")

Open in new window

0
 
3_SCommented:
If you leave out the where part and you don not have any return records, this means you VenHdr table is empty? is this correct? Does this table contains records?
0
 
sammeAuthor Commented:
The Single Quotes did work, The VendHdr does contain records, but not all the records in BidHist are there, but I can get around that thanks for the help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now