Problem with DLookup (1 Viewer)

[Thicko]

I'm struggling with what should be a simple problem:

This works:

PreCompoundedLookupUnused = Nz(DLookup("[DoseRequired1]", "atblChemoPreCompoundOrder", "[Preparation]='" & Me![PreparationNotVisible] & "' And StockReceived = -1 And StockUsed = 0 And NextCycleDue < Date-8"), "")

However when I add a numerical criteria:

"[DoseRequired1] = " & Me![Dose] And

Which in the full code looks like:

PreCompoundedLookupUnused = Nz(DLookup("[DoseRequired1]", "atblChemoPreCompoundOrder", "[Preparation]='" & Me![PreparationNotVisible] & "'" And [DoseRequired1] = " & Me![Dose] And StockReceived = -1 And StockUsed = 0 And NextCycleDue < Date-8"), "")

I get run time error 2465 can't find field '[1'

I really feel I'm missing something obvious, one way round is to run several seperate DLookups with single criteria but I feel that would be defeat.

Any suggestions

Many Thanks

 

[spikepl]

Dim myCriteria as String

myCriteria="[Preparation]='" & Me![PreparationNotVisible] & "'"

debug.print myCriteria

result=Nz(Dlookup("myFieldName", "myTableName", myCriteria)

Build on the above adding one criterion at a time -check your criteria in the immediate window, where debug.print puts it.

 

[CJ_London]

You have your quotation marks mixed up a bit

You supplied

PreCompoundedLookupUnused = Nz(DLookup("[DoseRequired1]", "atblChemoPreCompoundOrder", "[Preparation]='" & Me![PreparationNotVisible] & "'" And [DoseRequired1] = " & Me![Dose] And StockReceived = -1 And StockUsed = 0 And NextCycleDue < Date-8"), "")


this & "'" And
Should be & "' And


this Me![Dose] And StockReceived
Should be Me![Dose] & " And StockReceived


this < Date-8"
should be <" & Date-8
or <#" & Date-8 & "#"

You'll need to check which

final result should be


PreCompoundedLookupUnused = Nz(DLookup("[DoseRequired1]", "atblChemoPreCompoundOrder", "[Preparation]='" & Me![PreparationNotVisible] & "' And [DoseRequired1] = " & Me![Dose] & " And StockReceived = -1 And StockUsed = 0 And NextCycleDue <" & Date-8), "")

 

[Thicko]

Many Thanks for the help guys, the final solution was as CJ_London propossed.

PreCompoundedLookupUnused = Nz(DLookup("[DoseRequired1]", "atblChemoPreCompoundOrder", "[Preparation]='" & Me![PreparationNotVisible] & "' And [DoseRequired1] = " & Me![Dose] & " And StockReceived = -1 And StockUsed = 0 And NextCycleDue <#" & Date - 8 & "#"), "") ' Internet edited

Going onto a follow up just for interest / completeness

I ran the steps as spikepl suggested and the results were as expected:

Dim myCriteria1 As String
Dim myCriteria2 As String
Dim myCriteria3 As String
Dim myCriteria4 As String
Dim myCriteria5 As String
myCriteria1 = "[Preparation]='" & Me![PreparationNotVisible] & "'"
myCriteria2 = "[DoseRequired1] = " & Me![Dose]
myCriteria3 = "StockReceived = -1"
myCriteria4 = "StockUsed = 0"
myCriteria5 = "NextCycleDue <" & Date - 8
Debug.Print myCriteria1
Debug.Print myCriteria2
Debug.Print myCriteria3
Debug.Print myCriteria4
Debug.Print myCriteria5

However when trying to put them together:

myResult1 = Nz(DLookup("DoseRequired1", "atblChemoPreCompoundOrder", myCriteria1 And myCriteria2), "")

It was a Type Mismatch, Where did I go wrong?

Again Many thanks to CJ_London and spikepl for the guidance