ACCESS FEED: Re: [MS_AccessPros] Lookup code

Minggu, 09 Maret 2014

Re: [MS_AccessPros] Lookup code

Toukey-

There should be no space between the # and the quote on either side.

Also, regarding your second email (you didn't copy the thread in that one), if you're on a non-US machine, you should use Format to convert the date to US format.

If Not IsNull(DLookup("DOB", "Members", "DOB = #" & Format(Me.DOB, "mm/dd/yyyy") & "#")) Then

All dates in literals in SQL must be in US format to work properly. (DLookup builds SQL using your parameters behind the scenes.)

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Mar 9, 2014, at 2:38 PM, toukey1 <no_reply@yahoogroups.com> wrote:

Hi John,
Sorry - yes I meant DLookup.
I entered the code you suggested and got the following message on the highlighted code below
    "You entered an invalid argument in a domain aggregate function"
This is the code:
    If Not IsNull(DLookup("DOB", "Members", "[Members].[DOB] = # " & Me!DOB & " #")) Then
     mes = MsgBox("possible duplicate date", vbOKOnly)
I am still going wrong somewhere.
I would also like if the DOB already exist in the table to show in the mes the first and last names of the member with the duplicated DOB. Possible?
Oh! I forgot to mention Access 2010 .mde database with linked member table if that makes a difference.

Thanks
Toukey