dlookup

[laveshhp via AccessMonster.com]

any idea why i get #name error

=DLookUp("[OpenBal]","Table11","[Date] =" & Format([Forms]![Date3]!
[BeginningDate],"\#mm/dd/yyyy\#"))


Thanks

 

[Klatuu]

Not exactly, but there are a couple of things that need attention.
First, using Date as a field name or any other name is a bad idea. Because
it is a reserved word, it can confuse Access. You do have it in brackets
which should resolve the problem, but it is still a bad idea.

Get rid of the Format. You don't really need it

=DLookUp("[OpenBal]","Table11","[Date] = #" & [Forms]![Date3]!
[BeginningDate] & "#")

 

[Douglas J. Steele]

Why do you say that the Format isn't necessary? If the machine has its Short
Date format set to dd/mm/yyyy, then that's what's going to be presented by
referring to [Forms]![Date3]![BeginningDate]. However, Access is going to
treat it as mm/dd/yyyy in the DLookup statement.

In other words, two days ago was 12 Sep, 2006. If that was the date in
[Forms]![Date3]![BeginningDate] and the Short Date format has been set to
dd/mm/yyyy, then your suggested DLookup would have been
=DLookUp("[OpenBal]","Table11","[Date] = #12/09/2006#"). Regardless of the
fact that Regional Settings have been set to dd/mm/yyyy, Access is going to
treat that as 09 Dec, 2006.

That having been said, I have no idea why the originally presented statement
isn't working. It should!


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Not exactly, but there are a couple of things that need attention.
First, using Date as a field name or any other name is a bad idea.
Because
it is a reserved word, it can confuse Access. You do have it in brackets
which should resolve the problem, but it is still a bad idea.

Get rid of the Format. You don't really need it

=DLookUp("[OpenBal]","Table11","[Date] = #" & [Forms]![Date3]!
[BeginningDate] & "#")

any idea why i get #name error

=DLookUp("[OpenBal]","Table11","[Date] =" & Format([Forms]![Date3]!
[BeginningDate],"\#mm/dd/yyyy\#"))


Thanks

 

[Klatuu]

I see your point. Since I am in the US, I seldom think about other local
date settings. Perhaps a bad idea if I expect anything I do to go
international.

I have done some experimenting, and can't reproduce the error. I do notice,
however, we haven't asked where this formula is being used. If it is in a
Default Value or Control Source property of a control, it might return that
error. (Notice the = at the beginning of the line)

I don't know the why of it (perhaps you could enlighten me <again.), but I
have noticed that qualifying references in those two properties will return
that error. My solution has been to use:

=DLookUp("[OpenBal]","Table11","[Date] ="
Format([BeginningDate],"\#mm/dd/yyyy\#"))

Why do you say that the Format isn't necessary? If the machine has its Short
Date format set to dd/mm/yyyy, then that's what's going to be presented by
referring to [Forms]![Date3]![BeginningDate]. However, Access is going to
treat it as mm/dd/yyyy in the DLookup statement.

In other words, two days ago was 12 Sep, 2006. If that was the date in
[Forms]![Date3]![BeginningDate] and the Short Date format has been set to
dd/mm/yyyy, then your suggested DLookup would have been
=DLookUp("[OpenBal]","Table11","[Date] = #12/09/2006#"). Regardless of the
fact that Regional Settings have been set to dd/mm/yyyy, Access is going to
treat that as 09 Dec, 2006.

That having been said, I have no idea why the originally presented statement
isn't working. It should!


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Not exactly, but there are a couple of things that need attention.
First, using Date as a field name or any other name is a bad idea.
Because
it is a reserved word, it can confuse Access. You do have it in brackets
which should resolve the problem, but it is still a bad idea.

Get rid of the Format. You don't really need it

=DLookUp("[OpenBal]","Table11","[Date] = #" & [Forms]![Date3]!
[BeginningDate] & "#")

any idea why i get #name error

=DLookUp("[OpenBal]","Table11","[Date] =" & Format([Forms]![Date3]!
[BeginningDate],"\#mm/dd/yyyy\#"))


Thanks

 

[Douglas J. Steele]

I'm missing something. I don't see any difference between what you're saying
your solution is in the most recent post, and what the original poster had.

I'm also afraid I don't know what you mean by "qualifying references in
those two properties will return that error".

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I see your point. Since I am in the US, I seldom think about other local
date settings. Perhaps a bad idea if I expect anything I do to go
international.

I have done some experimenting, and can't reproduce the error. I do
notice,
however, we haven't asked where this formula is being used. If it is in a
Default Value or Control Source property of a control, it might return
that
error. (Notice the = at the beginning of the line)

I don't know the why of it (perhaps you could enlighten me <again.), but I
have noticed that qualifying references in those two properties will
return
that error. My solution has been to use:

=DLookUp("[OpenBal]","Table11","[Date] ="
Format([BeginningDate],"\#mm/dd/yyyy\#"))

Why do you say that the Format isn't necessary? If the machine has its
Short
Date format set to dd/mm/yyyy, then that's what's going to be presented
by
referring to [Forms]![Date3]![BeginningDate]. However, Access is going to
treat it as mm/dd/yyyy in the DLookup statement.

In other words, two days ago was 12 Sep, 2006. If that was the date in
[Forms]![Date3]![BeginningDate] and the Short Date format has been set to
dd/mm/yyyy, then your suggested DLookup would have been
=DLookUp("[OpenBal]","Table11","[Date] = #12/09/2006#"). Regardless of
the
fact that Regional Settings have been set to dd/mm/yyyy, Access is going
to
treat that as 09 Dec, 2006.

That having been said, I have no idea why the originally presented
statement
isn't working. It should!


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Not exactly, but there are a couple of things that need attention.
First, using Date as a field name or any other name is a bad idea.
Because
it is a reserved word, it can confuse Access. You do have it in
brackets
which should resolve the problem, but it is still a bad idea.

Get rid of the Format. You don't really need it

=DLookUp("[OpenBal]","Table11","[Date] = #" & [Forms]![Date3]!
[BeginningDate] & "#")


:

any idea why i get #name error

=DLookUp("[OpenBal]","Table11","[Date] =" & Format([Forms]![Date3]!
[BeginningDate],"\#mm/dd/yyyy\#"))


Thanks