Is this asking too much of Access

[mboe]

I have a somewhat complex query that locks up and utilizes 60% of the processing power of a dual processor server. I have to kill the process to stop it. I got it down to the following causing the problem.

Working Query Portion:
EffectiveDate: DLookUp("[txn_date]","lns2e","[loan_no] = [loan_no] and [txn_date] = [txn_date] and [txn_code] = 837")

this only returns the first record from what I can tell so I changed the syntax to what should be correct.

EffectiveDate: DLookUp("[txn_date]","lns2e","[loan_no] = '" & [loan_no] & "' and [txn_date] = [txn_date] and [txn_code] = 837")

I also have a criteria on this portion of the following

DateSerial(Year(DMax("[dt_lst_txn]","dda2aeom")),Month(DMax("[dt_lst_txn]","dda2aeom")),1)

Is this just to complex for access or am I doing something wrong?

 

[llkhoutx]

Shouldn't

EffectiveDate: Dlookup(“[txn_data]”,”Ins2e”,”[loan_no]=[loan_no} and [txn_data]=[txn_date] and [txn_code]=837”)

be

EffectiveDate: Dlookup(“[txn_data]”,”Ins2e”,”[loan_no]=" & [loan_no] & " and [txn_data]=" & [txn_date] & " and [txn_code]=" & 837)

 

[Mile-O]

Shouldn't

EffectiveDate: Dlookup(“[txn_data]”,”Ins2e”,”[loan_no]=[loan_no} and [txn_data]=[txn_date] and [txn_code]=837”)

be

EffectiveDate: Dlookup(“[txn_data]”,”Ins2e”,”[loan_no]=" & [loan_no] & " and [txn_data]=" & [txn_date] & " and [txn_code]=" & 837)

Not quite.

EffectiveDate: DLookup("[txn_data]","Ins2e","[loan_no] = " & [loan_no] & " And [txn_data] = #" & [txn_date] & "# And [txn_code] = 837")


Still, though. If ou use domain aggreate functions in queries then speed, most certainly, will be compromised.