Help with dlookup required

[Norman Fritag]

Hi there,

I am wonder why this dlookups return null, despite having a record in the
table with a matching date?
Am I missing something here???
Am i running in some limitations, that I am not aware of??

Any hint is much appreciated.

regards

Norman

DLookup("[Datevisited]", strTable, "[DOHID] = " & Me.DOHID _
& " and Grade = " & Me.Grade
& " and [Datevisited] =#" & format(txtDatev isited_text,"DD/MM/YYYY")& "#") =
null ??

DLookup("[Datevisited]", strTable, "[DOHID] = " & Me.DOHID _
& " and Grade = " & Me.Grade
& " and [Datevisited] =#" & cDate(txtDatevi sited_text) & "#") = null

DLookup("[Datevisited]", strTable, "[DOHID] = " & Me.DOHID _
& " and Grade = " & Me.Grade
& " and [Datevisited] =#" & (txtDatevisited _text) ) = null

txtDatevisited_ text reads in the debugger as "09/02/2004"
Me.Grade = 10
Me.DOHID = 1

DCount("Grade", strTable, "[DOHID] = " & Me.DOHID & " and Grade = " &
Me.Grade ) = 2
In the Record: Dohid = 1;Grade=10,date visited="25/09/2003"
Dohid = 1;Grade=10,date visited="09/02/2004"

[Peter Doering]

Re: Help with dlookup required

On Wed, 11 Feb 2004 21:05:25 +1100, Norman Fritag wrote:
[color=blue]
> I am wonder why this dlookups return null, despite having a record in the
> table with a matching date?
> Am I missing something here???
> Am i running in some limitations, that I am not aware of??
> ...
> DLookup("[Datevisited]", strTable, "[DOHID] = " & Me.DOHID _
> & " and Grade = " & Me.Grade
> & " and [Datevisited] =#" & format(txtDatev isited_text,"DD/MM/YYYY")& "#") =
> null ??[/color]

Is txtDatevisitet_ text declared as a string as the name indicates or does
it contain a valid date? In case it's a date, try:

.... =#" & Format(txtDatev isited_text, "MM\/DD\/YYYY") & "#")

Otherwise check the content of txtDatevisited_ text.

HTH - Peter

--
No mails please.

[Norman Fritag]

Re: Help with dlookup required

Peter,
Thanks for your reply,
To your question: txtDatevisitet_ text is a textbox control declared as a
string and containing "09/02/2004"!
That why I am so puzzled

regards
Norman

"Peter Doering" <nospam@doering .org> wrote in message
news:c0d0u6$163 07b$1@ID-204768.news.uni-berlin.de...[color=blue]
> On Wed, 11 Feb 2004 21:05:25 +1100, Norman Fritag wrote:
>[color=green]
> > I am wonder why this dlookups return null, despite having a record in[/color][/color]
the[color=blue][color=green]
> > table with a matching date?
> > Am I missing something here???
> > Am i running in some limitations, that I am not aware of??
> > ...
> > DLookup("[Datevisited]", strTable, "[DOHID] = " & Me.DOHID _
> > & " and Grade = " & Me.Grade
> > & " and [Datevisited] =#" & format(txtDatev isited_text,"DD/MM/YYYY")&[/color][/color]
"#") =[color=blue][color=green]
> > null ??[/color]
>
> Is txtDatevisitet_ text declared as a string as the name indicates or does
> it contain a valid date? In case it's a date, try:
>
> ... =#" & Format(txtDatev isited_text, "MM\/DD\/YYYY") & "#")
>
> Otherwise check the content of txtDatevisited_ text.
>
> HTH - Peter
>
> --
> No mails please.[/color]

[Rod Scoullar]

Re: Help with dlookup required

Norman,

Dates are assumed to be in the format mm/dd/yyyy, so your date of 09/02/04
is interpreted as 2nd Sept 2004.

I use a format statement similar to

datestring = Format(datevari able, "\#dd-mmm-yyyy\#")

which returns a value of #02-Feb-2004#

This cannot be interpreted incorrectly.

Rod Scoullar

[Peter Doering]

Re: Help with dlookup required

On Wed, 11 Feb 2004 23:04:39 +1100, Norman Fritag wrote:
[color=blue]
> To your question: txtDatevisitet_ text is a textbox control declared as a
> string and containing "09/02/2004"![/color]

Then try ...

DLookup("[Datevisited]", strTable, "[DOHID] = " & Me.DOHID _
& " and Grade = " & Me.Grade & " and [Datevisited] =#" & _
Mid(txtDatevisi ted_text,4,3) & _
Left(txtDatevis ited_text,2) & _
Right(txtDatevi sited_text,5) & "#")

HTH - Peter

--
No mails please.

[Norman Fritag]

Re: Help with dlookup required

Rod,
thanks for your input.

the system date setting is set to "dd/mm/yyyy" format.
I use the same stringtext value in a vba sub, whereas I evaluate if this
date already exists and it works fine.
You make have noticed that I used (and [Datevisited] =#" &
format(txtDatev isited_text,"DD/MM/YYYY") & "#"). It evaluates null.
I will try your format setting and see if that works.

thanks again

regards Norman

"Rod Scoullar" <pcdummy@hotmai l.com> wrote in message
news:uxTnXuJ8DH A.3880@tk2msftn gp13.phx.gbl...[color=blue]
> Norman,
>
> Dates are assumed to be in the format mm/dd/yyyy, so your date of 09/02/04
> is interpreted as 2nd Sept 2004.
>
> I use a format statement similar to
>
> datestring = Format(datevari able, "\#dd-mmm-yyyy\#")
>
> which returns a value of #02-Feb-2004#
>
> This cannot be interpreted incorrectly.
>
> Rod Scoullar
>
>[/color]

[Norman Fritag]

Re: Help with dlookup required

thanks Peter'

Will check it out.

Norman

"Peter Doering" <nospam@doering .org> wrote in message
news:c0d95s$15s 5rj$1@ID-204768.news.uni-berlin.de...[color=blue]
> On Wed, 11 Feb 2004 23:04:39 +1100, Norman Fritag wrote:
>[color=green]
> > To your question: txtDatevisitet_ text is a textbox control declared as a
> > string and containing "09/02/2004"![/color]
>
> Then try ...
>
> DLookup("[Datevisited]", strTable, "[DOHID] = " & Me.DOHID _
> & " and Grade = " & Me.Grade & " and [Datevisited] =#" & _
> Mid(txtDatevisi ted_text,4,3) & _
> Left(txtDatevis ited_text,2) & _
> Right(txtDatevi sited_text,5) & "#")
>
> HTH - Peter
>
> --
> No mails please.[/color]