Dlookup With Multiple Criteria

[Heatshiver]

I have two tables, table A has the Days (date) field as a primary key, and table B has the userNum field as a primary key. They have a relationship through the userID field on Table A. I have a form that has the user fill out Days and userID from Table A.

I would like to create a DLookup function that searches to see if the userID and Days fields filled out have already have a record. I can't seem to get anything to work though!

Any help would be greatly appreciated. Even a sample database with such would be of great help! Thank you.

 

[pr2-eugin]

Just use an AND in the where part of the DLookUp function..

DLookUp("[COLOR=Red]anyFieldName[/COLOR]","TableA", _
"([COLOR=Red]DateFieldName[/COLOR]=" & [COLOR=Red]Forms!FormName!DateFieldName[/COLOR] & ") AND ([COLOR=Red]UserIDFieldName[/COLOR]=" & [COLOR=Red]Forms!FormName!IDFieldName[/COLOR] & ")")
'[COLOR=Lime][COLOR=SeaGreen]Make sure that the highlighted ones match accordint to your defenitions[/COLOR].[/COLOR]
'[COLOR=SeaGreen]Also make sure that you match the DATA TYPES.[/COLOR]

Would make more sense if you use DCount.. Nothing wrong in using DLookUp, but then you have to also add functionality to handle NullString that will be returned, if there is no record that matches the criteria.

 

[Heatshiver]

Thank you so much for the help and insight! I will try this as soon as I get home! Thanks again.

 

[vbaInet]

You can use the DCount() function instead and if it returns 0 that entry doesn't exist, otherwise it does.

Let's see the DLookup() statement you wrote.

Edit: I was pretty slow there. I didn't see the last two posts before posting.