SQL statement in VBA code to populate form text box 1

[Gfunk13]

I am trying to use the following to populate a form text box:

Dim sqlLot As String
sqlLot = "SELECT val(lot_num]) AS NewLot FROM
Raw_Matrl_Receipt_Table
GROUP BY Raw_Matrl_Receipt_Table.Lot_Num
HAVING (((Raw_Matrl_Receipt_Table.Lot_Num) Is Not Null))ORDER BY Val([lot_num]) DESC';"

If IsNull(Me!Lot_Num) Then

Me!Lot_Num = DMax("NewLot", sqlLot) + 1

End If
---

If I define sqlLot as String, I get an error stating that the input query or table cannot be found.

If I use Double or Integer, I get a type mismatch error.

 

[TheAceMan1]

How are ya Gfunk13 . . .

[blue]Domain: A string expression identifying the set of records that constitutes the domain. It can be a table name or a [purple]query name[/purple].[/blue]

[purple]query name[/purple] is a name in the query window, [purple]not a variable with assigned SQL![/purple]

Make a query instead, or use a recordset if you wanna stay with SQL . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Gfunk13]

Recordset was way to go. Thanks!