Join with vb function in it

[guitarzan]

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?

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

 

[PHV]

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

 

[PHV]

BTW, you may try this:
FROM Invoices LEFT JOIN Forms ON Invoices.InvoiceNum Like Forms.FormNumber & '*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

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

 

[guitarzan]

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.

 

[guitarzan]

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?

 

[PHV]

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

 

[guitarzan]

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?

 

[PHV]

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

 

[dhookom]

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