• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • 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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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