DLookup Help

[FreddyFord]

Hi,

If anyone can help me with this, it would be much appreciated.

I have 2 tables in a db that costs Steelwork erection during constructions.

I want a new field in TB-WAL-2300-Steelwork (cant attach an image) to search the first 3 letters of the Type field and return the Component Name in TB-Abbreviation-Table where the Abreviations match.

Ive tried DLookup but cant get it working and Im pulling my hair out!!!

Thankyou

TB-WAL-2800-Steelwork

Type RHS 400x200x16
New Field Rectangular Hollow Section


TB-Abbreviation-Table

Abbreviation RHS
Component Name Rectangular Hollow Section

 

[pbaldy]

See if this helps, since you didn't post your effort to fix:

http://access.mvps.org/access/general/gen0018.htm

 

[arnelgp]

if you are going to do it in form:

=DLookup("[Component Name]", "[TB-Abbreviation-Table ]", "Left([Abbreviation]) = '" & Left(Me.[New Field], 3) & "'")

 

[FreddyFord]

Thankyou. I'm trying it now. Is there a general rule of thumb on when to use expressions in queries or reports?

 

[FreddyFord]

Hi,

I ideally want to do it from a query as I want to use the results in a group in a form.

This is what I have so far, but cant get the last part correct. It works up until the last Abb section

Expr4: DLookUp("[Component Name]","TB-Abbreviation","[Abbreviation]"="[Abb]")

I want to return the [component name] from (Table)-TB-Abbreviation where [Abbreviation] from (Table) TB-Abbreviation = [Abb] from (Query) QR-WAL-2800-Steelwork

 

[bob fitz]

Maybe:
Expr4: DLookUp("[Component Name]","TB-Abbreviation","[Abbreviation]='" & [Abb] & "'")

 

[FreddyFord]

Hi Bob,

Thankyou, I think that must be working as when I run it, it freezes access and then crashes. Is there a maximum number of records access can handle? I have 15,000 maximum.

Thanks,

Chris

 

[bob fitz]

Hi Bob,

Thankyou, I think that must be working as when I run it, it freezes access and then crashes. Is there a maximum number of records access can handle? I have 15,000 maximum.

Thanks,

Chris

It doesn't sound like it is working very well if it makes access crash

Can you post a copy of your db

 

[jdraw]

Chris,
Please tell us more about your business and the database you have --something like " a day in the life of our company". It will help put the database and requirements into context.
Is this an operational database or one that is being developed?

 

[plog]

I want to return the [component name] from (Table)-TB-Abbreviation where [Abbreviation] from (Table) TB-Abbreviation = [Abb] from (Query) QR-WAL-2800-Steelwork

DLookups don't belong in queries. Especially in this instance. A query is for joining tables that relate.

That means, you bring in QR-WAL-2800-Steelwork, link it to TB-Abbreviation appropriately, and then bring down the component name field (or any other fields you want.