×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Join with vb function in it

thread700-1698043

Join with vb function in it

Join with vb function in it

(OP)
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

RE: Join with vb function in it

Do you actually have a table or query named "Forms"? I would think that would lead to some issues since Forms is a collection of forms.

Duane
Hook'D on Access
MS Access MVP

RE: Join with vb function in it

(OP)
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.

RE: Join with vb function in it

(OP)
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?

RE: Join with vb function in it

I personally use vi (unix text editor) to write SQL code and then paste it in the SQL view of access.

RE: Join with vb function in it

(OP)
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?

RE: Join with vb function in it

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 ...

RE: Join with vb function in it

We all can't be PH winky smile 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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close