Put SQL-query with Dlookup into VBA syntax problem

[killerflappy]

Hi guys,
I’m struggling to put a access-query into VBA with the right syntax.

The query is working and gives the SQL:
UPDATE tblMutations, tblDeliveryDate SET tblMutations.DeliveryDate = DLookUp("DeliveryDate","tblDeliveryDate","Search=" & "'1'") WHERE (([tblMutations].[Customer] Is Null))

I played around with “, ‘, (), [] etc.

It’s setting the DeliveryDate in the tblMutations to de DeliveryDate that is set in the row where Search is 1. In the tblDeliveryDate I store the DeliveryDate that is set on a form to use all around my database. Search is a short text. I point to the search and get the DeliveryDate.

In another VBA sql-statement I can refer to the DeliveryDate with: [tblDeliveryDate].[DeliveryDate] WHERE (([tblDeliveryDate].[Search]='1'))

Maybe this isn’t the right way to store this important date to use all around the database. I came up with this solution, because I can’t store just the deliverydate in a table and refer to it in SQL-statements or Dlookup’s. Or is there another way?

 

[Minty]

Your query has referenced two tables but is only updating one. the easy route would be to simply build the string something like

Dim dDelDate as String
Dim sSql as String

dDelDate ="#" & Format(DLookUp("DeliveryDate","tblDeliveryDate","Search='1'"),"mm/dd/yyyy") & "#"

sSql = "Update tblMutations SET DeliveryDate = " & dDelDate & " WHERE Customer Is Null ;"

Debug.Print sSql

Currentdb.Execute sSql , dbSeeChanges

 

[arnelgp]

you missed 1 doubke quote on the search part.

 

[killerflappy]

Hi guys,

It works perfect! Briljant!

I was struggling with the syntax, because I use another way for the SQL to run.

What are the rows below and how to use them?

Debug.Print sSql

Currentdb.Execute sSql , dbSeeChanges

 

[Minty]

The debug.print sql will echo the sSql string to the immediate window in the vba editor.
It is a simple handy way to see what your code is doing.

Currentdb.Execute is a good way to run an action query in VBA.

dbSeeChanges is a switch that allows the backend database to update any underlying tables, if you aren't using something like SQL server you don't need it. I normally include it by default as I use SQL Server tables.

 

[jleach]

Also, just as an aside: if you ever find yourself thinking about putting a DLookup (or any other D* functions) inside a query, take a step back and ask yourself how this can be done better. Domain aggregate functions themselves aren't bad, but inside a query they are very bad.

(every D* function needs to open it's own recordset to get a value, and the performance impact of this is highly compounded when used in a query: this could be the difference between a 2-second query and a 2-hour query!)

Cheers