Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 828
  • Last Modified:

Oracle and Access, DLookup not returning expected results.

I'm using Access with Oracle, a DLookup I'm using is not returning expected results.

The DLookup below returns count=0 when it should return count = 1.

Me.txtUserExistsFlg = DCount("LoginName", "Users", "LoginName = '" & Me.txtUserName & " ' ")  <-- doesn't work

Note that the table "Users" points to the Oracle table.
Me.txtUserName in the debugger looks like this:  "wpeck", including the double-quote marks.

If I hard-code the DLookup to

Me.txtUserExistsFlg = DCount("LoginName", "Users", "LoginName = 'wpeck'") <-- works with literal 'wpeck'

then it works fine.

In Oracle the name is lowercase, wpeck.

Any ideas?
Alaska Cowboy
Alaska Cowboy
1 Solution
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Hi williampeck,

I believe you have an extra space before the right single-quote mark.  Try this...
Me.txtUserExistsFlg = DCount("LoginName", "Users", "LoginName = '" & Me.txtUserName & "'")  
....  Me.txtUserName {ampersand}{space}{double-quote}{single-quote}{double-quote}{right parentheses}

Hope this helps.
Alaska CowboyAuthor Commented:
Yes, you are correct, and that did the trick !

In Access, the extra space resolved itself, but Oracle took things too literally - it always does.

Thanks ! Time for bed . . .

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now