SQL query to DLookup (1 Viewer)

[zippy483]

Good morning all

I have a database I look after that I inherited sometime ago from the original creator who is no longer available for advice

recently a user has asked for a modification to said database that necessitates a modification to an existing DLookup statement to add a second criteria and for the life of me I can't get it to work, however I can create a query and find the relevant value from the relevant table and the SQL looks like this

SELECT [myTable].[myValue]
FROM [myTable]
WHERE (([Criteria1]=[Form]![Value1]) AND ([Criteria2]=[Form]![Value2]));

The Dlookup statement is inside a macro that fires when the form control (dropdown box) Value2 is selected, it works fine with a single criteria but not with 2, single criteria Dlookup below

DLookUp("[myValue]","[myTable]","[Critera1]=Form![Value1]")

How would I go about getting the output from my SQL statement above from a Dlookup statement to use in a macro.

Thank you

 

[theDBguy]

Hi. The criteria for DLookup should look similar to your SQL. For example,

DLookup(..., "[Criteria1]=Form![Value1] AND [Criteria2]=Form![Value2]"),

Hope that helps...

Edit: Added missing closing double quote.

 

[zippy483]

Thanks DBguy

I thought that too tried a few variations on the following

DLookup("[myValue]", "[myTable]","[Criteria1]=Form![Value1]" AND "[Criteria2]=[Form]![Criteria2]")

But no joy query seems to run ok but doens't populate the box on the form with the correct data

my table is in the format below, index items 1 and 2 were already in the table index items 3 and 4 were added to expand our use to include Betty with my Value 77

myTable

myIndex	myValue	Criteria1	Criteria2	Value1	Value2
1​	99​	Fred	Barney	xxx	yyy
2​	99​	Wilma	Barney	xxx	yyy
3​	77​	Fred	Betty	xxx	yyy
4​	77​	Wilma	Betty	xxx	yyy

So if Criteria 1 = Fred and Criteria 2 = Betty I need the output 77, with the following statement, DLookUp("[myValue]","[myTable]","[Criteria1]=[Form]![Criteria1]" And "[Criteria2]=[Form]![Criteria2]") I perpetually get 99 it's almost as if the second argument in the AND statement is being ignored ??

 

[arnelgp]

try:

DLookup("[myValue]", "[myTable]","[Criteria1]= '" & Form![Value1] & "' AND [Criteria2]= '" & [Form]![Criteria2] & "'")

 

[zippy483]

unfortunately that doesn't seem to work either, how difficult can this be

 

[theDBguy]

You might consider posting a sample db demonstrating the problem.

 

[arnelgp]

should be:
DLookup("[myValue]", "[myTable]","[Criteria1]= '" & Forms![FormName]![Value1] & "' AND [Criteria2]= '" & [Forms]![FormName]![Criteria2] & "'")

 

[Gasman]

If you are going to hardcode form controls in the dlookup, then you just need " at the start and end of the criteria, I would have thought?

 

[Gasman]

unfortunately that doesn't seem to work either, how difficult can this be

Very, for some people?

 

[zippy483]

So it's proving Gasman

So avoid any confusion with my generic statements above

the SQL for the Query that works and pulls the values I wan't out of the table is

SELECT [Physics QC CTDI Refs].[Low CF], [Physics QC CTDI Refs].[High CF]
FROM [Physics QC CTDI Refs]
WHERE ((([Physics QC CTDI Refs].[Linac name])=[Form]![machine]) AND (([Physics QC CTDI Refs].CTDIChamber)=[Form]![dosemeter]));

and i need to put 2 values into separate text boxes on a form

I'm trying to use the Dlookup statements below

DLookUp("[Low CF]","[Physics QC CTDI refs]","[Forms]![Physics QC CTDI form]![Machine]=[Linac Name] and [Forms]![Physics QC CTDI form]![dosemeter]=[CTDIChamber]")

DLookUp("[High CF]","[Physics QC CTDI refs]","[Forms]![Physics QC CTDI form]![Machine]=[Linac Name] and [Forms]![Physics QC CTDI form]![dosemeter]=[CTDIChamber]")

I think some of the difficulty is that the chap that wrote the dB used spaces in field names and table names and hence i need to get the correct quote marks in the correct places, do I need "" around [Linac Name] and [Physics QC CTDI form] or indeed do they actually need to be ' ' ?

 

[arnelgp]

you can get the Combined value you need for the two textboxes:

Dim sRet As String
sRet = _
DLookUp("[Low CF] & '|' & [High CF]","[Physics QC CTDI refs]","[Linac Name] = '" & [Forms]![Physics QC CTDI form]![Machine] & "' And [CTDIChamber] = '" & [Forms]![Physics QC CTDI form]![dosemeter] & "'") & ""

If Len(sRet) Then
Me!Textbox1 = Val(Split(sRet, "|")(0))
Me!Textbox2 = Val(Split(sRet, "|")(1))
End If

 

[zippy483]

Can I embed the above inside a macro arne?

 

[arnelgp]

no, you can't (esp Split()).
are you not allowed to use VBA?