DLOOKUP of table from itself 1

[TBOB]

I'm using a table as its own lookup table in a form. That works fine, but when I'm trying to use the DLOOKUP to get the text instead of ID number I keep getting an error. Here is the function I'm using:

=DLookUp("[facid]","[FACILITIES]"," [ID]=[facadj1] &quot

Everything here is from the same table FACILITIES. ID is the PRIkey using Autonumber. facid is the identifier I want to show on my report. facadj1 is the field where where the ID number is stored. Any help will be appreciated.

Thanks,

Tony

 

[Steve101]

Too many square brackets. Try just using:

DLookUp("[facid]","FACILITIES"," "[ID]='" & [facadj1] & "'&quot

You only need square brackets to surround field names with special characters in them. In this example, facid, id[/] and facid1 dont have any of these special characters so you can optionally get rid of these as well.

The brackets are wrong around the domain (ie. Facilities) where I think they will cause the DLookup to fail. Also
note the use of the double and single quotes to build the DLookups criteria expression. Remove the single quotes if facadj1 is numeric, as opposed to a string.

Hope this helps,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[TBOB]

Steve,

I tried your line and several iterations of it. I keep getting a syntax error. With the syntax in my original form, minus the brackets around FACILITIES, it accepts the code, but still gives a #Error when viewed on the report. I have used this the other Lookups in my report and they work. Is it different somehow because everything is from the same table?

Thanks,

Tony

 

[Blorf]

If it is because it is from the same table (I honestly don't know), try something like this:


=DLookUp("[facid]","select FACILITIES.* from FACILITIES;"," [ID]=[facadj1] &quot


Also, since you want to look up a value from the same record, why not just include that value in the reports recordset?

ChaZ

Ascii dumb question, get a dumb Ansi

 

[Steve101]

Tony, if you publish the Table name and associated fields, it will be easier to help you resolve the syntax problems you seem to be having.

Another thought: why are you not using a join query to lookup the associated value on the form; you should'nt need the DLOOKUP function in the form at all.


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[tzephon]

Are you making your comparison against the same data type?
If ID is long int field and facadj1 is text then that will cause an error.
=DLookUp("[facid]", "FACILITIES", "[ID] = " & [facadj1])
=DLookUp("[facid]", "Facilities", "[ID] = " & cint(facadj1))

 

[TBOB]

I've been out for a while, sorry to not get back sooner. Let me clarify this situation. I think my original dialog may have made it sound like there is a problem in my form. There is not. I'm using the table 'FACILITIES' as its own lookup table for the form. No problem there. When I try to print out the report based on that table, this is where I am using the DLookUp function and having the problem. I trying to use the DLookUp to give me the text from the 'facid' field in the 'FACILITIES' table where 'facilityID'(the primary key for 'FACILITIES') = 'facadj1'(the stored value of the adjacent site). When I preview the report, the facadj1 box shows the value, but the text box, txtADJFAC1, I'm using for the DLookUp gives me this indication, # Error. I put the DLookUp in the control source for txtADJFAC1 as follows:

=DLookUp("[facid]","FACILITIES","[facilityID]=[facadj1]")

Any help is appreciated.

Thanks,

 

[Steve101]

Try:
This could be fixed by:

DLookUp("[facid]","FACILITIES","[facilityID]=[red][b]'[/b][/red]Facadj1[red][b]'[/b][/red]")

The red quotes above are required as your DLookup criteria is comparing a text field to a string value.

The example below shows how to "soft code" the DLookup criteria expression:

FacilityIdValue="Facadj1"
DLookUp("[facid]","FACILITIES","[facilityID]='" & FacilityIdValue & "'")

If required, you could do the FacilityIdValue assignment to a form based field.

Hope this helps,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[TBOB]

Steve,

Still can't make it happen. Could I send you a sample of the database and have you look at it?

Tony

 

[Steve101]

k; but will only be able to look at it later; at work now on a customer site, so it would be this evening.


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[TBOB]

That's fine. It's on its way.

Thanks,

Tony

 

[Steve101]

Tony,

I've had a look at the mdb. On the Facilities report, replace the controlsource property of the text46 control with the following:
[tt]
=DLookUp("[alt]","ALT","[altID]= " & [facalt])
[/tt]
On the txtADJFAC1 control, replace the controlsource property with the following:
[tt]
=DLookUp("[facid]","FACILITIES","[facilityID]=" & [facadj1])
[/tt]
In both cases above, the closing quote of the last argument (ie. the DLookup criateria parameter) needs to be placed so that it does NOT incorporate the respective facalt and facadj1 controls. This ensures that the VALUEs of these controls and not the name is actually used as the lookup criteria. The value is first evaluated, then it is concatenated (using the & operator) to the expression to the left, then the entire expression is used as the basis for doing the lookup.

Another couple of points: The mdb file you sent me was 5.5MB - pretty large attachment given that I only had to look at two lines of code. After I compacted it, it dropped to 470KB - less than 10% of the original size. When I then zipped it, it dropped to 76KB; about 1.5% of the size of the file you sent ... just food for thought should you ever need to ship around mdb files.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[TBOB]

Steve,

That works! Thanks for the help and sorry about the file size. I will pay more attention in the future.

Tony

 

[TBOB]

Steve's code above works great. The new problem is when there is nothing in the [facadj1] field I get an #Error on the report. Played around unsucessfully with solutions. Any ideas?

Thanks,

Tony

 

[Steve101]

Quick and dirty, but try

=DLookUp("[facid]","FACILITIES","[facilityID]=" & [red]nz([/red][facadj1][red],0)[/red])

This will convert null values to zero, thus allowing the criteria argument above to be a legitimate expression. I'm assuming that you dont have a facilityId of zero, otherwise replace the 0 with a number that you know is not used for the facilityid; eg. 99999 or -999.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)