If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > DLookup with multiple conditions

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: May 2011
Posts: 47
DLookup with multiple conditions

I have a DLookup with multiple conditions that appears to be evaluating values from different records in the db (i.e. evaluating True for condition 1 on record 5, and evaluating True on condition 2 on record 10). Is the the proper way for DLookup to work? If that's so, is it true that DLookup does not work like a SQL WHERE clause, where each record is evaluated as true or false?

thanks for any help
Reply With Quote
  #2 (permalink)  
Old
(Making Your Life Easy)
 
Join Date: Feb 2004
Location: New Zealand
Posts: 1,275
I have had no problems with Dlookup
did you know
you can do

aaa = DLookup("this","Fromthistable","bla='bla' and thisis=false and that='this'")


are you looking at the samething data as dlookup is looking at
I know I have spent a number of hours telling the dlookup it was wrong when it was me
__________________
hope this help

See clear as mud


StePhan McKillen
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment:
Access based on my own environment: DAO3.6/A97/A2000/A2003
VB based on my own environment: vb6 sp5
ASP based on my own environment: 5.6
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
MYLE
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: May 2011
Posts: 47
DLookup

I've been through it many times and I understand what you're saying. Here it is:

If Not IsNull(DLookup("[field_1]", "tbl_detail_all", "field_1 = '" & Me.cmbo_scenario & "'")) And _
Not IsNull(DLookup("[field_2]", "tbl_detail_all", "field_2 = 'Accounts Receivable'")) Then

Update...

Else

Insert...

db looks like this:

field_1 XXX YYY ZZZ
AAA BBB CCC field_2

I believe my logic (condition on DLookuo) would INSERT my new data. Instead it skips over the insert (when I debug). Again, it appears that it is evaluating on two seperate rows in the db.

I know I'm wrong but I just can't figure this one out.

Thanks for any help.
Reply With Quote
  #4 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,190
DLookUp returns the value of the specified column for one row matching the criteria (if any). Nothing garantees that the same row will be selected if you call the function twice successively with a different criteria, except if the criteria concerns the primary key column or a column with a UNIQUE constraint.

In your case, you should combine both conditions into a single criteria and call DLookUp only once (provided that the combination of both conditions constitutes a unique condition for the table):
Code:
    Dim strCriteria As String
    
    strCriteria = "field_1 = '" & Me.cmbo_scenario & "' AND field_2 = 'Accounts Receivable'"
    If Not IsNull(DLookup("[field_1]", "tbl_detail_all", strCriteria)) Then
__________________
Have a nice day!
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: May 2011
Posts: 47
DLookup

Thanks so much. I now see why I had the problem (i.e. using the DLookup function twice).
Again, Thank you.
Reply With Quote
  #6 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,190
You're welcome!
__________________
Have a nice day!
Reply With Quote
  #7 (permalink)  
Old
(Making Your Life Easy)
 
Join Date: Feb 2004
Location: New Zealand
Posts: 1,275
I would use a Dcount as it would return a 0 if not there
__________________
hope this help

See clear as mud


StePhan McKillen
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment:
Access based on my own environment: DAO3.6/A97/A2000/A2003
VB based on my own environment: vb6 sp5
ASP based on my own environment: 5.6
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
MYLE
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: May 2011
Posts: 47
DLookup

Thanks much.
Reply With Quote
  #9 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,190
Good idea
__________________
Have a nice day!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On