Dlookup syntax (1 Viewer)

[WineSnob]

I have this code

Private Sub Command12_Click()
Dim x As Integer
Dim y As Integer
x = Me.spouseage
y = Me.clientage
Me.rdt = DLookup("[40]", "TblJoint", "[OldestAge]=70")
End Sub

It works fine. However I need to replace "[40]" with x and replace 70 with y.
I can't get the syntax right.
I have tried
Me.rdt = DLookup([x], "TblJoint", "[OldestAge]=[y]") and tried
Me.rdt = DLookup(x, "TblJoint", "[OldestAge] = y")
Thanks in advance

 

[bob fitz]

Try:
Me.rdt = DLookup(x, "TblJoint", "[OldestAge] =" & y)
OR try:
Me.rdt = DLookup(x, "TblJoint", "[OldestAge] ='" & y & "'")

 

[WineSnob]

Thanks however neither works.
Me.rdt = DLookup(x, "TblJoint", "[OldestAge] =" & y)
'returns x
Me.rdt = DLookup(x, "TblJoint", "[OldestAge] ='" & y & "'")
'returns type mismatch
I can use me.spouseage and me.clientage if necessary.

 

[bob fitz]

Try:
Me.rdt = DLookup("'" & x & "'", "TblJoint", "[OldestAge] =" & y)

 

[WineSnob]

That returns value of x (me.spouseage)
This works
Me.rdt = DLookup("[55]", "TblJoint", "[OldestAge]=" & Me.clientage)
so the issue lies with x.
x or [55] is a field column name. Could that be a problem? Should I rename the columns to age55 or age56 or age57 etc?

 

[bob fitz]

You could try renaming them but before you do that,
Try:
Me.rdt = DLookup("'[" & x & "]'", "TblJoint", "[OldestAge] =" & y)

 

[WineSnob]

Thanks for your help.
I ended up changing the field column names ( I think it was confused with the column name as an integer) and using
Me.rdt = DLookup("age" & Me.spouseage, "TblJoint", "[OldestAge]=" & Me.clientage)
IT works as needed.

 

[a.sharma]

The standard syntax for DLookup is DLookup(FieldName, SourceName, Criteria)
FieldName is the name of the field which hold the value you are looking for.
SourceName is the name of the table or query that contains the field
Criteria is any condition that you wish to evaluate to get the value

Consider the following examples:
1. me.rdt = DLookup("x", "TblJoint", "[OldestAge] =" & y) if y is a number

2. me.rdt = DLookup("x", "TblJoint", "[OldestAge] = '" & y & "'") if y is a string

 

[bob fitz]

WineSnob
The problem with using the varriant called x is that you have declared as an integer but should be a string. If you change that to:
Dim x As String
then the code in my original post does work:
Me.rdt = DLookup(x, "TblJoint", "[OldestAge] =" & y)

 

[WineSnob]

Yep. I missed that. I was thinking I was looking for an integer when you are correct I was using x as a string.
Thanks.

 

[Steve@trop]

I have a similar issue with DLookup.
Table AmmoniaDetectorT has these fields:

AssetNumber
DetectorNumber

I have a form that gets the Asset number from another form and places it into the txtAssetNumber textbox. I want it to look up the DetectorNumber and enter it into the form.

I've tried the following dlookup code

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
Me.txtAssetNumber = Forms!DetectorManagementF!txtAssetNumber
Me.txtDetectorNumber = DLookup("DetectorNumber", "AmmoniaDetectorT", "AssetNumber = txtAssetNumber")
End Sub

I think the problem is the syntax on the criteria portion. I want it to use the contents of the txtAssetNumber field to look up the Detector Number.

 

[WineSnob]

Try this

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
Me.txtAssetNumber = Forms!DetectorManagementF!txtAssetNumber
Me.txtDetectorNumber = DLookup("DetectorNumber", "AmmoniaDetectorT", "AssetNumber = Me.txtAssetNumber")
End Sub


[/QUOTE]

 

[WineSnob]

Or try
Me.txtDetectorNumber = DLookup("DetectorNumber", "AmmoniaDetectorT", "AssetNumber = " & Me.txtAssetNumber)

 

[Steve@trop]

Thanks Rick,

I tried:

Me.txtDetectorNumber = DLookup("DetectorNumber", "AmmoniaDetectorT", "AssetNumber =" & Me.txtAssetNumber)

And I got a runtime error 3464:
Datatype mismatch in criteria Expression.

 

[WineSnob]

Verify that txtAssetNumber is a number.
Then try
Me.txtDetectorNumber = DLookup("DetectorNumber", "AmmoniaDetectorT", "AssetNumber =" & 1)
or Put a value you know is correct (where the 1 is) and will lookup in the syntax to verify it is working.

I have also found in the past sometime if I rewrite the entire line of code it works. Close and save the form then retry.

 

[Steve@trop]

I checked the data type in the table and found it was set to text. Changed it to number and rewrote the entire line of code and now don't get the error. However, there is nothing in the DetectorNumber field. I had the same problem when I tried to do this with a combo box instead of a Dlookup. The combo box solution worked on most of the other forms but not on this one so I'm trying the Dlookup in the text box.

 

[WineSnob]

Do you get a value if you "force" the lookup using ex.1 or 2 or some value that is in the table?
Try using Me.Refresh after the code

 

[Steve@trop]

I did try it once and put in one of the asset numbers and it didn't work either. I don't remember the error I got though.

I think I'll work on something else for a while and come back to this. If I look at this later with fresh eyes, perhaps I'll see what is wrong.

Thanks for your help.

 

[WineSnob]

If your want to post your db or just the a sample db with that table and form. I'll get it working. I'm retired and am looking for something to do.

 

[Steve@trop]

Wow, that's a generous offer! What version of Access are you on? I'm working in Access 2010 but I can probably export the components to a lower version if necessary.

Thanks Rick!