Eliminate DLookup / Keep query editable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to eliminate some DLookups from a query to enhance the speed of
the query. But, when I link to the table it makes it not editable. Is there
a way around this or something simple that I am missing?

I am using the first two characters in a field to find a value in another
table. When I perform a DLookup as
Expr1: DLookUp("[PType]","TProcesses","[TPShort] = '" &
Left([CControlID2],2) & " ' ")

it works fine, but if I eliminate the Dlookup and try to link to the
Left([CControlID2],2) it works but the data is now not editable.

Any suggestions? I need the info for an editable form.

Thanks,
Dave
 
You might be able to add the TProcesses table to the query.
Join TPShort to CCControlID2 by dragging from table to table.
Then switch to SQL View (Vie menu), and edit the JOIN clause so that instead
of:
xxx.TPShort = yyy.CCControlID2
it reads:
xxx.TPShort = Left(yyy.CCControlID2,2)

You won't be able to switch back to query design view after that change, but
if it's a simple query it should still work. (There could be other issues if
you already have multiple tables and join types here.)

When designing your tables, it is worth keeping the atomic rule in mind,
i.e. only store one thing in a field. Designing the structure that way means
that you generally don't have this issue with pulling out part of a field.
 
That join works great, the only problem is that it is still uneditable. I
use an indirect method to assign CControlID2 to TProcess and thus no direct
link. Because of the structure it was not possible to assign ahead of time.
I can get it to work with DLookup, but it is slow. Thanks for the try.

Dave


Allen Browne said:
You might be able to add the TProcesses table to the query.
Join TPShort to CCControlID2 by dragging from table to table.
Then switch to SQL View (Vie menu), and edit the JOIN clause so that instead
of:
xxx.TPShort = yyy.CCControlID2
it reads:
xxx.TPShort = Left(yyy.CCControlID2,2)

You won't be able to switch back to query design view after that change, but
if it's a simple query it should still work. (There could be other issues if
you already have multiple tables and join types here.)

When designing your tables, it is worth keeping the atomic rule in mind,
i.e. only store one thing in a field. Designing the structure that way means
that you generally don't have this issue with pulling out part of a field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

dave k said:
I am trying to eliminate some DLookups from a query to enhance the speed of
the query. But, when I link to the table it makes it not editable. Is
there
a way around this or something simple that I am missing?

I am using the first two characters in a field to find a value in another
table. When I perform a DLookup as
Expr1: DLookUp("[PType]","TProcesses","[TPShort] = '" &
Left([CControlID2],2) & " ' ")

it works fine, but if I eliminate the Dlookup and try to link to the
Left([CControlID2],2) it works but the data is now not editable.

Any suggestions? I need the info for an editable form.

Thanks,
Dave
Click to expand...
Click to expand...
 
Back
Top