Error in SQL

[fcarvalho]

Good afternoon. I have an sql instruction like that:

...IN 'CurrentProject.Path & " \ historicoapolice.mdb '" & _ But it gives me error

But if i put like this:

....IN ' C:\loja seguros\ historicoapolice.mdb '" & _ it does not matter anymore.

What i'm doing wrong? Can someone help me? Thank you

[ranman256]

Link the external data as a table .
the. Run the query.

[fcarvalho]

Hello. Thank you for the feedback. The "historicoapolice.mdb" is a database to store customer history. In the main MDB there is a button. Clicking executes the sql statement and sends the selected tab to history. It is not connected to the main. I would like the sql statement to run anywhere where the mdb was installed, since the folder is the same.
So, rather than ....IN ' C:\loja seguros\ historicoapolice.mdb '" & _, i would like this......IN 'CurrentProject.Path & " \ historicoapolice.mdb '" & _ But it gives me error. Mujst doing something wrong, but why?

[ssanfu]

You MUST concatenate the path, just as you have the db name.

...IN '" & CurrentProject.Path & "\historicoapolice.mdb '" & _
(Edit: just noticed you have a space before and after the slash. I removed it)


I never liked using the line continuation option. It makes it too hard to read.
So I use

    sSQL = "SELECT tblClients.Clients_PK, tblClients.LastName & ', ' & tblClients.FirstName As FullName"
    sSQL = sSQL & " FROM tblClients"
    sSQL = sSQL & " INNER JOIN tblJunction ON tblClients.Clients_PK = tblJunction.Clients_FK"
    sSQL = sSQL & " WHERE tblJunction.Visits_FK = " & Me.cboVisits
    sSQL = sSQL & " ORDER BY LastName;"
    Debug.Print sSQL
    Set r = CurrentDb.OpenRecordset(sSQL)

Notice the spaces are at the beginning of the line, not at the end. Very easy to see if there are missing spaces.


If you set a break on the line after the debug line, it is easy to see if the SQL is correctly formed.
You can also copy the SQL in the immediate window, create a new query, switch to SQL View and paste in the SQL.
Then you can try to execute the query..... Access will tell you if there are errors in the SQL.