Struggling with SQL in VBA (1 Viewer)

[Thicko]

Hi Folks,

I've created an update query in access and it works as expected:

UPDATE atblChemoPreCompoundOrder SET atblChemoPreCompoundOrder.Patient = [Forms].[frmChemoRecord].[Patient], atblChemoPreCompoundOrder.[Patient Number] = [Forms].[frmChemoRecord].[Patient Number]
WHERE (((atblChemoPreCompoundOrder.Preparation)=[Forms].[frmChemoRecord].[PreparationNotVisible]) AND ((atblChemoPreCompoundOrder.DoseRequired1)=[Forms].[frmChemoRecord].[Dose]) AND ((atblChemoPreCompoundOrder.StockReceived)=True) AND ((atblChemoPreCompoundOrder.StockUsed)=False) AND ((DMin("NextCycleDue","atblChemoPreCompoundOrder","[Preparation]='" & [Forms].[frmChemoRecord].[PreparationNotVisible] & "' And [DoseRequired1] = " & [Forms].[frmChemoRecord].[Dose] & " And StockReceived = -1 And StockUsed = 0"))=[NextCycleDue]));

I want to run this code through VBA and I would normally do this by running the above as an SQL string.

Because of the DMin statement in the access query I can't get it to work in an SQL format. I realise SQL replaces " with ', but it's not that simple.

I've seen the techonthenet example as:

DMax("UnitPrice", "Order Details", "OrderID = 10248")

To:

SELECT Max([Order Details].UnitPrice) AS MaxOfUnitPrice FROM [Order Details] WHERE ((([Order Details].OrderID)=10248));

I can't see how to work that into my more complex SQL statement.

All ideas welcome
Many Thanks

 

[RuralGuy]

You can force a double quote into a string by putting two together so "In a string "" will not terminate the string but place a double quote into it. It may have other ramifications depending on how you then use the string.
Hmm...it may actually require three double quotes to insert the single double quote. Play with it.

 

[Thicko]

Thanks for the advise RuralGuy, I couldn't get a DMin to work within an SQL statement so had to go with the alternative and create a second query with an inner join.

It works but it's not such a straight forward solution.

 

[RuralGuy]

Whatever works is probably a good solution. Have a great day.