Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Join with vb function in it

Status
Not open for further replies.
guitarzan

guitarzan

Programmer
Apr 22, 2003
2,236
US
I am trying to run a query in Access (2003) that requires a join of one field to the first 4 characters of another field. I guess I never tried to do this before, because when I do the query is successful but when I go back to design view i get "Microsoft Access can't represent the join expression ... in Design View"

The only way to get back to design view is to remove the VB command (Left() in this case). Is there any way around this that doesn't disable "design" view?
Code: 
SELECT Invoices.InvoiceNum, Forms.FormNumber, Forms.FormType
FROM Invoices LEFT JOIN Forms ON Left(Invoices.InvoiceNum, 4) = Forms.FormNumber;

In the above sample, InvoiceNum and FormNumber are both text fields, and FormNumber is always 4 characters long
 
Sort by date Sort by votes
Why bother with the buggy design view ?
If you replace the JOIN .. ON clause with a WHERE criteria you defeat the purpose of the outer join.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
BTW, you may try this:
FROM Invoices LEFT JOIN Forms ON Invoices.InvoiceNum Like Forms.FormNumber & '*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
PHV: I wondering why what I was trying to do was being considered invalid by design view... if it's just bugginess about design view, I will work around it. (the query seems to work fine otherwise). I can live without design view.

dhookom: This was just a sample db I threw together to demonstrate the problem in its simplest form, and it did its job. Apparently in this case a table named "Forms" works.
 
Upvote 0 Downvote
When working with access, would you recommend writing the SQL manually as opposed to using design view? Access always seemed very picky about parenthesis, so I usually use its Design View for the joins, and then manually tweaked the SELECT / WHERE / GROUP BY clauses etc. Is there a better approach?
 
Upvote 0 Downvote
I personally use vi (unix text editor) to write SQL code and then paste it in the SQL view of access.
 
Upvote 0 Downvote
PHV: Thanks... I remember access being much more picky about parenthesis in join statements compared to sql server / mysql etc... thats why i try to use design view in access when i have to... am i wrong? are Access join statements as easy to write in a text editor as sql for SQL Server or other languages? Or is Access more of a pain?
 
Upvote 0 Downvote
I remember access being much more picky about parenthesis in join statements
When I use N tables in a FROM clause I know I should have (N-2) balanced pairs of parenthesis,eg (N=4):
SELECT ...
FROM ((T1
INNER JOIN T2 ON ...)
INNER JOIN T3 ON ...)
INNER JOIN T4 ON ...
 
Upvote 0 Downvote
We all can't be PH ;-) so just use the GUI design view for Access queries. I only modify these if I need to use the SQL statements in code, need to post the SQL to a forum, or write SQL that isn't supported by the GUI design view such as union queries.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JayRo71
  • Locked
  • Question
Saved Imoorts problem with file name
Replies
10
Views
801
Andrzejek
Andrzejek
Anthony343242
  • Locked
  • Question
MS ACCESS UNION ALL with Attachment data type 1
2
Replies
29
Views
7K
Anthony343242
Anthony343242
Lidias13
  • Locked
  • Question
Odata contains fields from multiple tables in single table with different field names - for same
Replies
1
Views
537
dhookom
dhookom
jjMedrano
  • Locked
  • Question
UNION ALL with Attachment data type 1
Replies
8
Views
626
Andrzejek
Andrzejek
BugZap13
  • Locked
  • Question
How to extract Access table schema with decimal places on numeric fields?
Replies
2
Views
4K
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top