MS Access 2010 - Using Mid in a query

KrazyKasper

Board Regular
Joined
Aug 28, 2008
Messages
87
I have two tables, both of which have the field SalesDocumentId. In one of the tables the field has a prefix SO- and I am trying to eliminate that so I can compare the two fields.
When trying to run the following query, I get the error "Invalid Bracketing of Name Shipments.SalesDocumentId". It does not seem to like my using the table name "Shipments" but if I eliminate that, it gives an error of "the specified field could refer to more than one table listed in the From clause".

I tried various bracketing but nothing seems to work. Do I need to dothis in two separate queries?

SELECT Mid([Shipments.SalesDocumentId],4) AS TruncSalesDocTyp
FROM dbo_Shipments INNER JOIN dbo_InvoiceDetail ON dbo_Shipments.SalesDocumentId = dbo_InvoiceDetail.SalesDocumentId;
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
To begin with, you have the wrong syntax for the Mid expression and you should not need to bracket this part of the mid statement Shipments.SalesDocumentId as there are no spaces in either the table name nor the field name. If you do wish to bracket them, then it would be like this: [Shipments].[SalesDocumentId]

The syntax for the Microsoft Access Mid function is:

Mid ( text, start_position, number_of_characters )
</pre>
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top