Dlookup with SQL String

[steve1111]

Hello,

I am looking to try to eliminate creating stored queries as often as possible so I have been creating them in VBA. I am having an issue with referencing them with Dlookup though so any tips would be great. I am getting a run-time error 3163 the field is too small to accept the amount of data....

Dim sSQL As String
sSQL = "SELECT dbo_Vehicles.ID, dbo_Divisions.ID AS DivisionID, dbo_Divisions.Name AS DIvisionName, dbo_Vehicles.VehicleNumber, dbo_Users.id AS TCID, Mid([dbo_Users]![login],5,99) AS TCName, dbo_Users_1.id AS TSID, Mid([dbo_Users_1]![login],5,99) AS TSName, dbo_Clinics.ID AS ClinicID, dbo_Clinics.Name AS ClinicName " & _
                "FROM ((((dbo_Vehicles INNER JOIN dbo_Zones ON dbo_Vehicles.ZoneID = dbo_Zones.ID) INNER JOIN dbo_Divisions ON dbo_Zones.DivisionID = dbo_Divisions.ID) INNER JOIN dbo_Users ON dbo_Vehicles.TCUserID = dbo_Users.id) INNER JOIN dbo_Users AS dbo_Users_1 ON dbo_Vehicles.TSUserID = dbo_Users_1.id) INNER JOIN dbo_Clinics ON dbo_Vehicles.StartLocationID = dbo_Clinics.LocationID " & _
                "WHERE (((dbo_Vehicles.ID)=[Forms]![sysfMain]![subMain1].[Form]![subMXT1].[Form]![vanID]));"


Me.DivisionID.Value = DLookup("DivisionID", sSQL)

 

[Minty]

That's not how a DLookup would work, it won't interpret the SQL string.
Why not simply open that query as a recordset and find what you need directly from the recordset?

 

[arnelgp]

contrary to the Documentation
on DLookup, and all D() functions,
the domain can only be a Table or Query
but not a String Query, like you did.

the only way you can get around this
without storing the Query is to
create a memory Query, eg:

Dim rs as Dao.Database
Dim qd As Dao.QueryDef

set db=currentdb
'insert your SELECT statement below
set qd=db.CreateQuerydef("", "SELECT ....")

Me.DivisionID.Value = qd.OpenRecordSet()("DivisionID")

set qd=Nothing
set db=Nothing

 

[steve1111]

Thanks to both of you, you got me on the correct path. I am still learning recordsets and qd in VBA!