Join Statements

[svm]

I am new to VB and I have a hard time coming up with the correct syntax coding for this.

strSQL = Select * From tblProjectInfo Inner Join_ tblBidInfo on tblProjectInfo.F_Number =_
tblBidInfo.F_Number _
Where tblProjectInfo.Probability% > 74 _
And tblProjectInfo.Job_Status = "Pending" And tblBidInfo.Base_Alt = "Base""


What's the correct syntax. Appreciate to whatever help you can give me on this. Thanks.

 

[dpaulson]

You enclose string values in single tick (') instead of the quotation mark. David Paulson

 

[svm]

I tried doing that but still i'm getting a compile error.

 

[JohnYingling]

That is because you are not enclosing the ENTIRE string literal start with Select * From and ending with = "Base" in double quotes.
strSql = "Select fhdlm wherrreee " & _
" ftfjsjk = 'fghdhdg' " & _
" skhgffbnmem 'fdgdgf'"

http://www.VBCompare.com

http://www.VBSortGen.com

 

[TimLarkin]

you also need to identify what fields you want to select from tblBidInfo.
such as:
Select tblProjectInfo.*, tblBidInfo.F_Number, tblBidInfo.F_Size, tblBidInfo.F_Amnt Inner....

(Check my syntax though..) Tim

Remember the KISS principle:
Keep It Simple, Stupid!

 

[chiph]

svm -

The people at work hate me for this, but you might want to re-format your code to make it more readable:
[tt]
sSQL = ""
sSQL = sSQL & " SELECT tblProjectInfo.Column1,"
sSQL = sSQL & " tblProjectInfo.Column2"
sSQL = sSQL & " FROM tblProjectInfo"
sSQL = sSQL & " INNER JOIN tblBidInfo ON"
sSQL = sSQL & " tblProjectInfo.F_Number = tblBidInfo.F_Number"
sSQL = sSQL & " WHERE tblProjectInfo.Probability>74"
sSQL = sSQL & " AND tblProjectInfo.Job_Status='Pending'"
sSQL = sSQL & " AND tblBidInfo.Base_Alt='Base'"
[/tt]
I've changed the select clause to bring back only the columns you need (database guys hate to see "SELECT *&quot. I've also changed the column name for Probability to remove the percent-sign, as this symbol is used as a wildcard in SQL for use with the LIKE operator. I've also changed the quotes around your string literals to the single-quotes.

Style-wise, I like to keep the single space at the front of each line (most people put it at the end) because then I can immediately see where one was left off (instead of zig-zagging down the ends of the lines). I also like to capitalize SQL keywords to make them stand out.

Chip H.

 

[chiph]

Arrggg!
Stupid Tek-Tips word-wrap screwed it up. But you get the idea. Cut and paste it into VB and you'll see.

Chip H.