MS Access DLookUp malfunction

Question

I have a very weird problem occurring in MS Access which I can't seem to figure out.

Summary: I have a table from Sharepoint that is connected to my MS Access database and a Person table in my Ms Access db. I pull the information row by row from the Sharepoint table and add it to Person Table.

However, before adding the new data I must check if that specific Person already exists in my table. I check for 'Lastname', 'Firstname' and 'Date created' using DLookup function.

Here where everything goes side ways. DLookup returns me a NULL for almost half of the records that already exist in Person Table.

After playing a lot with the condition in DLookup statement my conclusion is that there is a problem with the 'Date created' parameter, yet I have tried using "#" and CDate and even Format, nothing works.

I can't share the data, since it's sensitive, however the syntax for DLookup I'm using is the following:

    sqlStr = "LastName=" & Chr(34) & rs![Last Name] & Chr(34) 
    & " AND FirstName=" & Chr(34) & rs![First Name] & Chr(34) 
    & " AND DateLastModified=" & Format(dateVar, "dd/mm/yyyy") 

    DLookup("LastName", "table_Person", sqlStr)

P.S: I have tried DCount, same thing happens. DCount returns 0 yet I know for a fact the record is there.

can you please share an example of your data and your code? It's impossible for anyone to help without having some idea of what you're working with — ashleedawg, Mar 09 '18 at 15:30
**Welcome to [so]!** Please take a few minutes to check out the [tour] as well as [ask], and there are important tips about **providing examples** at [mcve]. We like to see that some effort has been made in finding a solution before asking for help (on a _specific_ problem), so please [edit] your question to include details about what you've tried so far. More info [here](https://stackoverflow.com/help/on-topic). — ashleedawg, Mar 09 '18 at 15:39
Can you also show your DLOOKUP syntax, try like `"01/02/2018"` — Nathan_Sav, Mar 09 '18 at 15:48
Yes sorry i have edited the question with the DLookUp syntax I`m using. — linkrok, Mar 09 '18 at 15:58
Use Gustav's [`CSql()` function](https://stackoverflow.com/a/36494189/3820271) when building dynamic SQL. It handles date, string and other variables. — Andre, Mar 09 '18 at 16:03
Also: [How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) — Andre, Mar 09 '18 at 16:05
Follow the advice from @Andre, or at least use the correct format for the date expression: `& " AND DateLastModified=#" & Format(dateVar, "yyyy\/mm\/dd") & "#"`. — Gustav, Mar 09 '18 at 17:12
@Gustav I did try it however the problem is still there, I begin to suspect there is something wrong with the table values themselves. — linkrok, Mar 09 '18 at 17:59
You received [an excellent answer here](https://stackoverflow.com/questions/49579470/scheme-remove-the-maximum-from-a-bst), but seem to have deleted it, causing the helpful person posting a substantial answer to have entirely wasted their time. May I ask why you did this? — halfer, Mar 30 '18 at 20:32

ComputerVersteher · Accepted Answer · 2018-03-09T18:22:58.247

To build criterias BuildCriteria is your Friend.

Sub TestBuildCriteria()
Dim strCriteria As String 
strCriteria = BuildCriteria("OrderDate", dbDate, [Date created])
MsgBox strCriteria
End Sub

Sub YourCode()
sqlStr = BuildCriteria("LastName", dbText, "=" & rs![Last Name]) & _
   " AND " & BuildCriteria("FirstName", dbText, "=" & rs![First Name]) & _
   " AND " & BuildCriteria("DateLastModified", dbDate, "=" & dateVar)
End Sub

This echoes the proper formated date. Also useful for other data-type. E.g. it escapes Quotation Marks in Strings. Read Custom Filters using BuildCriteria() too.

But there is a far easier alternative.

Create a unique composite index on LastName, FirstName and DateLastModified in the the table. Now you can't insert a duplicate as it has to be unique. If you try you will receive an error msg. Be aware of transaction rollbacks (e.g. Multiple inserts, one fails by key violation -> all actions will be reverted due transaction rollback if you use db.Execute SQL, dbFailOnError).

Ma man if I could kiss you I would! Thank you dear sir you! It works like a charm! — linkrok, Mar 09 '18 at 18:11

score 1 · Answer 2 · answered Mar 09 '18 at 16:07

To check for dates use:

"DateLastModified=#" & FormatDateTime(dateVar, vbShortDate) & "#"

if dateVar can be null you need something like this:

FormatDateTime(Nz(dateVar,CDate("1/1/2000")), vbShortDate)

And of course that just checks the date part. If your dateVar can also have a time part then you have to use

DateValue(dateVar)

score 0 · Answer 3 · answered Mar 09 '18 at 16:55

Your syntax is not correct. You should put square brackets around field names as is pointed out in documented examples at MSDN

sqlStr = "[LastName]=" & Chr(34) & rs![Last Name] & Chr(34) 
& " AND [FirstName]=" & Chr(34) & rs![First Name] & Chr(34) 
& " AND [DateLastModified]=#" & Format(dateVar, "dd/mm/yyyy") & "#" 

DLookup("[LastName]", "table_Person", sqlStr)

score 0 · Answer 4 · answered Mar 09 '18 at 19:09

In this situation, my advice would be to simplify the criteria part of the DLOOKUP/DCOUNT until you get something that works, and only then start to make the criteria more complex. I call this 'sanity checking'.

Date/Time criteria often cause problems, so first check that you can make it work without the Date part of the criteria.

For example, in your case, check that this works. Use the Debug Window (Ctril+G) to test this:

? DCount("*", "table_Person", "LastName=" & Chr(34) & rs![Last Name] & Chr(34))

Then try:

? DCount("*", "table_Person", "LastName=" & Chr(34) & rs![Last Name] & Chr(34) & " AND FirstName=" & Chr(34) & rs![First Name] & Chr(34))

Once you have that working, add in the Date criteria. Building the criteria up in stages like this, allows you to confirm which part is actually causing the problem.

I'm in the UK, and so I have my dates displayed in UK format - 'DD/MM/YYYY'. However, when specifying a date criteria for a DLOOKUP/DCOUNT, I always have to format the date to US format. I've often used a simple function to swap the digits into the correct order for the criteria:

Function HashDate(dD As Date) As String
  HashDate = "#" & Format$(dD, "MM/DD/YYYY") & "#"
End Function

In the the Debug Window:

? Date
09/03/2018 

? HashDate(Date)
#03/09/2018#

MS Access DLookUp malfunction

4 Answers4

Linked