UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dlookup With 2 Criteria, Access 2016    
 
   
River59
post Jan 14 2019, 03:40 PM
Post#1



Posts: 1,734
Joined: 7-April 10
From: Detroit, MI


I need to set a variable by using a record ID to look on a table and see if there is something in the COERelLtr field.

relTemp = DLookup("COERelatedLtr", "tbl_Template", "TemplateID = " & Me.txtTempID) - This works fine

I now know the relTemp

Now I need to see if that relTemp's status is 'Active' on tbl_Template... this is where I am failing. I can find it on the table but I cannot figure out how to add the Status = 'Active' to the DCount

If DCount("TemplateID", "tbl_Template", "TemplateID = " & relTemp) ..... I need to add tbl_Template.Status = 'Active' here ..... > 0 Then
MsgBox "Blah, blah, blah".

If DCount("TemplateID", "tbl_Template", "TemplateID = " & relTemp And tbl_template.Status = 'Active' ) > 0 Then

Any help?

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
ForestByte
post Jan 14 2019, 03:48 PM
Post#2



Posts: 38
Joined: 15-January 18



You mean something like this:

If DCount("TemplateID", "tbl_Template", "TemplateID = " & relTemp & " AND Status = '" & "Active" & "'")>0
Go to the top of the page
 
River59
post Jan 14 2019, 03:58 PM
Post#3



Posts: 1,734
Joined: 7-April 10
From: Detroit, MI


Yes, ForestByte, that is exactly what I mean ... lol

Thank you so much for responding. You have my gratitude! thumbup.gif
This post has been edited by River59: Jan 14 2019, 03:58 PM

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
tina t
post Jan 14 2019, 05:04 PM
Post#4



Posts: 5,957
Joined: 11-November 10
From: SoCal, USA


hello River, i'm a bit confused here. why are you performing two separate lookups on the same table? and why is field COERelatedLtr the same value as field TemplateID? or am i mis-reading the two DLookup() functions you posted?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
June7
post Jan 14 2019, 05:25 PM
Post#5



Posts: 602
Joined: 25-January 16



Equally confused. Perhaps you can provide sample dataset.





--------------------
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
River59
post Jan 15 2019, 09:11 AM
Post#6



Posts: 1,734
Joined: 7-April 10
From: Detroit, MI


TinaT, June7

Can't provide a dataset as I am working for a banking institution but here is the concept:

Template 24 is related to Template 25. If we are working with Template 25, we need to look up the related template (COERelLtr) which is 24, then we need to see if 24 is Active. (The field should be COERelTemp but was named COERelLtr before we made decisions so I left it alone.)
The first lookup finds Template 24, the second checks to see if it is Active or not.

The use here is that if we are Retiring Template 25, we need to let the user know that it is related to Template 24 which is active.

Hope this clears up what I am doing.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
June7
post Jan 15 2019, 03:30 PM
Post#7



Posts: 602
Joined: 25-January 16



Why does working for banking institution prevent providing example dataset? We need the real database structure but don't need real data. See instructions at bottom of my post.





--------------------
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
River59
post Jan 15 2019, 03:37 PM
Post#8



Posts: 1,734
Joined: 7-April 10
From: Detroit, MI


Thank you June7 but I know what I'm doing with this form. I only needed assistance with the syntax of the lookups.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
June7
post Jan 15 2019, 07:28 PM
Post#9



Posts: 602
Joined: 25-January 16



Well, didn't ask for form, asked for table structure.

However, read your OP again and think proper answer is in post 2 which you seem satisfied with.





This post has been edited by June7: Jan 15 2019, 07:30 PM

--------------------
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th June 2019 - 12:06 AM