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?
In the above sample, InvoiceNum and FormNumber are both text fields, and FormNumber is always 4 characters long
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
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Join with vb function in it
FROM Invoices LEFT JOIN Forms ON Invoices.InvoiceNum Like Forms.FormNumber & '*'
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Join with vb function in it
Duane
Hook'D on Access
MS Access MVP
RE: Join with vb function in it
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
RE: Join with vb function in it
RE: Join with vb function in it
RE: Join with vb function in it
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
Duane
Hook'D on Access
MS Access MVP