DFirst

[Amina]

Hi,
I am trying to use the dfirst function to pick the first description of
a particular date. e.g first instance where the description 'NEF Grant'
appears on the 04.06.07.

I have built this which doesn't seem to work.

=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#")

the text box appears blank although there is data to be displayed. At first
there wasn't an equals sign at the beginnig, hence showing 'name?#' but now
shows blank.

Any idea where I am going wrong?

Any help would be appreciated.

Thanks in advance.

 

[Douglas J. Steele]

What does 04.06.07 mean to you: 04 June, 2007 or 06 April, 2007? If it's
dd.mm.yy (and that's what your Regional Settings have set your Short Date
format), try:

=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=" & Format([Forms]![Weekly Child
View]![ChangeDate], "\#yyyy\-mm\-dd\#")

Access doesn't respect Regional Settings w.r.t. dates in SQL statements.

Also, are you certain that the date field contains just a date, or might it
contain date and time (because it was populated with Now(), rather than
Date())? If it does contain time, try:

" AND [Date] BETWEEN" & Format([Forms]![Weekly Child View]![ChangeDate],
"\#yyyy\-mm\-dd\#") & " AND " & Format(DateAdd("d", 1,[Forms]![Weekly Child
View]![ChangeDate]), "\#yyyy\-mm\-dd\#")

 

[Amina]

Doug,
I did try that out but no it doesnt work. the idea behind this is the
fact that i have a form with the weekly view of the day that children have
attended the nursery; from here, if i dbl- click on a price e.g. basic full
day for 23/5/07 (dd/mm/yyyy) then a sub-form appears showing the date 23/5/07
and i can automatically change that session and also see whether a funded
session has been booked.

I've also tried doing a replace. e.g. if 2 funded sessions are booked on
23/05 i want to replace ONLY ONE session with a basic pm, it deletes both the
funded ones.


Not sure if this makes any sense at all. but its basically 2 problems that i
CANT solve.

Thanks once again.



--
Amina...

What does 04.06.07 mean to you: 04 June, 2007 or 06 April, 2007? If it's
dd.mm.yy (and that's what your Regional Settings have set your Short Date
format), try:

=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=" & Format([Forms]![Weekly Child
View]![ChangeDate], "\#yyyy\-mm\-dd\#")

Access doesn't respect Regional Settings w.r.t. dates in SQL statements.

Also, are you certain that the date field contains just a date, or might it
contain date and time (because it was populated with Now(), rather than
Date())? If it does contain time, try:

" AND [Date] BETWEEN" & Format([Forms]![Weekly Child View]![ChangeDate],
"\#yyyy\-mm\-dd\#") & " AND " & Format(DateAdd("d", 1,[Forms]![Weekly Child
View]![ChangeDate]), "\#yyyy\-mm\-dd\#")



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,
I am trying to use the dfirst function to pick the first description of
a particular date. e.g first instance where the description 'NEF Grant'
appears on the 04.06.07.

I have built this which doesn't seem to work.

=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#")

the text box appears blank although there is data to be displayed. At
first
there wasn't an equals sign at the beginnig, hence showing 'name?#' but
now
shows blank.

Any idea where I am going wrong?

Any help would be appreciated.

Thanks in advance.

 

[Douglas J. Steele]

Did you try formatting the date field like I suggested? Access will
correctly interpret 23/5/07 as 23 May, 2007 since there is no 23rd month.
However, it will ALWAYS interpret 04/06/07 as 06 April, 2007.

You should probably read Allen Browne's "International Dates in Access"
(http://www.allenbrowne.com/ser-36.html) , or what I had in my September,
2003 "Access Answers" column in Pinnacle Publication's "Smart Access". (You
can download the column, and sample database, for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,
I did try that out but no it doesnt work. the idea behind this is the
fact that i have a form with the weekly view of the day that children have
attended the nursery; from here, if i dbl- click on a price e.g. basic
full
day for 23/5/07 (dd/mm/yyyy) then a sub-form appears showing the date
23/5/07
and i can automatically change that session and also see whether a funded
session has been booked.

I've also tried doing a replace. e.g. if 2 funded sessions are booked on
23/05 i want to replace ONLY ONE session with a basic pm, it deletes both
the
funded ones.


Not sure if this makes any sense at all. but its basically 2 problems that
i
CANT solve.

Thanks once again.



--
Amina...

What does 04.06.07 mean to you: 04 June, 2007 or 06 April, 2007? If it's
dd.mm.yy (and that's what your Regional Settings have set your Short Date
format), try:

=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly
Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=" & Format([Forms]![Weekly Child
View]![ChangeDate], "\#yyyy\-mm\-dd\#")

Access doesn't respect Regional Settings w.r.t. dates in SQL statements.

Also, are you certain that the date field contains just a date, or might
it
contain date and time (because it was populated with Now(), rather than
Date())? If it does contain time, try:

" AND [Date] BETWEEN" & Format([Forms]![Weekly Child View]![ChangeDate],
"\#yyyy\-mm\-dd\#") & " AND " & Format(DateAdd("d", 1,[Forms]![Weekly
Child
View]![ChangeDate]), "\#yyyy\-mm\-dd\#")



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,
I am trying to use the dfirst function to pick the first description
of
a particular date. e.g first instance where the description 'NEF
Grant'
appears on the 04.06.07.

I have built this which doesn't seem to work.

=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly
Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#")

the text box appears blank although there is data to be displayed. At
first
there wasn't an equals sign at the beginnig, hence showing 'name?#' but
now
shows blank.

Any idea where I am going wrong?

Any help would be appreciated.

Thanks in advance.

 

[Amina]

The formatting doesn't work. Although I dont think this is the problem. The
reason being it works in one place but doesnt work in another.

This one works:

DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#")

This one doesn't:


=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#")

Doesn't this seem a bit odd as there isn't much of a difference between the
two.

Thanks

--
Amina...

Did you try formatting the date field like I suggested? Access will
correctly interpret 23/5/07 as 23 May, 2007 since there is no 23rd month.
However, it will ALWAYS interpret 04/06/07 as 06 April, 2007.

You should probably read Allen Browne's "International Dates in Access"
(http://www.allenbrowne.com/ser-36.html) , or what I had in my September,
2003 "Access Answers" column in Pinnacle Publication's "Smart Access". (You
can download the column, and sample database, for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,
I did try that out but no it doesnt work. the idea behind this is the
fact that i have a form with the weekly view of the day that children have
attended the nursery; from here, if i dbl- click on a price e.g. basic
full
day for 23/5/07 (dd/mm/yyyy) then a sub-form appears showing the date
23/5/07
and i can automatically change that session and also see whether a funded
session has been booked.

I've also tried doing a replace. e.g. if 2 funded sessions are booked on
23/05 i want to replace ONLY ONE session with a basic pm, it deletes both
the
funded ones.


Not sure if this makes any sense at all. but its basically 2 problems that
i
CANT solve.

Thanks once again.



--
Amina...

What does 04.06.07 mean to you: 04 June, 2007 or 06 April, 2007? If it's
dd.mm.yy (and that's what your Regional Settings have set your Short Date
format), try:

=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly
Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=" & Format([Forms]![Weekly Child
View]![ChangeDate], "\#yyyy\-mm\-dd\#")

Access doesn't respect Regional Settings w.r.t. dates in SQL statements.

Also, are you certain that the date field contains just a date, or might
it
contain date and time (because it was populated with Now(), rather than
Date())? If it does contain time, try:

" AND [Date] BETWEEN" & Format([Forms]![Weekly Child View]![ChangeDate],
"\#yyyy\-mm\-dd\#") & " AND " & Format(DateAdd("d", 1,[Forms]![Weekly
Child
View]![ChangeDate]), "\#yyyy\-mm\-dd\#")



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,
I am trying to use the dfirst function to pick the first description
of
a particular date. e.g first instance where the description 'NEF
Grant'
appears on the 04.06.07.

I have built this which doesn't seem to work.

=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly
Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#")

the text box appears blank although there is data to be displayed. At
first
there wasn't an equals sign at the beginnig, hence showing 'name?#' but
now
shows blank.

Any idea where I am going wrong?

Any help would be appreciated.

Thanks in advance.

 

[Douglas J. Steele]

Don't know whether it's relevant, but DFirst is a pretty meaningless
concept, since you can't control the underlying order of records.

Just for kicks, what happens if you use

=Nz(DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly
Child View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#"), "No record returned")

(Please believe me, though, that since your Short Date format is set to
dd/mm/yyyy, you MUST format the dates!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The formatting doesn't work. Although I dont think this is the problem.
The
reason being it works in one place but doesnt work in another.

This one works:

DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#")

This one doesn't:


=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#")

Doesn't this seem a bit odd as there isn't much of a difference between
the
two.

Thanks

--
Amina...

Did you try formatting the date field like I suggested? Access will
correctly interpret 23/5/07 as 23 May, 2007 since there is no 23rd month.
However, it will ALWAYS interpret 04/06/07 as 06 April, 2007.

You should probably read Allen Browne's "International Dates in Access"
(http://www.allenbrowne.com/ser-36.html) , or what I had in my September,
2003 "Access Answers" column in Pinnacle Publication's "Smart Access".
(You
can download the column, and sample database, for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,
I did try that out but no it doesnt work. the idea behind this is
the
fact that i have a form with the weekly view of the day that children
have
attended the nursery; from here, if i dbl- click on a price e.g. basic
full
day for 23/5/07 (dd/mm/yyyy) then a sub-form appears showing the date
23/5/07
and i can automatically change that session and also see whether a
funded
session has been booked.

I've also tried doing a replace. e.g. if 2 funded sessions are booked
on
23/05 i want to replace ONLY ONE session with a basic pm, it deletes
both
the
funded ones.


Not sure if this makes any sense at all. but its basically 2 problems
that
i
CANT solve.

Thanks once again.



--
Amina...


:

What does 04.06.07 mean to you: 04 June, 2007 or 06 April, 2007? If
it's
dd.mm.yy (and that's what your Regional Settings have set your Short
Date
format), try:

=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly
Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=" & Format([Forms]![Weekly
Child
View]![ChangeDate], "\#yyyy\-mm\-dd\#")

Access doesn't respect Regional Settings w.r.t. dates in SQL
statements.

Also, are you certain that the date field contains just a date, or
might
it
contain date and time (because it was populated with Now(), rather
than
Date())? If it does contain time, try:

" AND [Date] BETWEEN" & Format([Forms]![Weekly Child
View]![ChangeDate],
"\#yyyy\-mm\-dd\#") & " AND " & Format(DateAdd("d", 1,[Forms]![Weekly
Child
View]![ChangeDate]), "\#yyyy\-mm\-dd\#")



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,
I am trying to use the dfirst function to pick the first
description
of
a particular date. e.g first instance where the description 'NEF
Grant'
appears on the 04.06.07.

I have built this which doesn't seem to work.

=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly
Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#")

the text box appears blank although there is data to be displayed.
At
first
there wasn't an equals sign at the beginnig, hence showing 'name?#'
but
now
shows blank.

Any idea where I am going wrong?

Any help would be appreciated.

Thanks in advance.

 

[Bob Quintal]

Hi,
I am trying to use the dfirst function to pick the first
description of
a particular date. e.g first instance where the description
'NEF Grant' appears on the 04.06.07.

I have built this which doesn't seem to work.

=DFirst("[Id]","[Invoice Items]","[Description]='" &
[Forms]![Weekly Child View]![Description] & "' AND [Child
ID]=" & [Forms]![Children List].[Form]![Child ID] & " AND
[Date]=#" & [Forms]![Weekly Child View]![ChangeDate] & "#")

the text box appears blank although there is data to be
displayed. At first there wasn't an equals sign at the
beginnig, hence showing 'name?#' but now shows blank.

Any idea where I am going wrong?

Any help would be appreciated.

Thanks in advance.

Is [Forms]![Children List].[Form]![Child ID] a reference to a
control on a subform?

If I'm picturing your layout correctly you have a mainform
[Forms]![Weekly Child View] that has a subform in it.

Reference to that subform should come from the
Forms!Parent!Control.form!controlOnSubform


Note that the Control.form should be what appears in the subform's
name property and not in the sourceObject property- They may be
different.

Assuming the subform control has the same name as the form it
contains, then I think if you try
[Forms]![Weekly Child View]![Children List].[Form]![Child ID] your
query will work.

 

[Amina]

Ok, I tried the NZ function, it gives me 'no record returned'. I now seem to
understand your theory of regional settings, which is set.

Still havn't got it working, but I think i'm just about to give up

Thanks for your help.
--
Amina...

Don't know whether it's relevant, but DFirst is a pretty meaningless
concept, since you can't control the underlying order of records.

Just for kicks, what happens if you use

=Nz(DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly
Child View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#"), "No record returned")

(Please believe me, though, that since your Short Date format is set to
dd/mm/yyyy, you MUST format the dates!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The formatting doesn't work. Although I dont think this is the problem.
The
reason being it works in one place but doesnt work in another.

This one works:

DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#")

This one doesn't:


=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#")

Doesn't this seem a bit odd as there isn't much of a difference between
the
two.

Thanks

--
Amina...

Did you try formatting the date field like I suggested? Access will
correctly interpret 23/5/07 as 23 May, 2007 since there is no 23rd month.
However, it will ALWAYS interpret 04/06/07 as 06 April, 2007.

You should probably read Allen Browne's "International Dates in Access"
(http://www.allenbrowne.com/ser-36.html) , or what I had in my September,
2003 "Access Answers" column in Pinnacle Publication's "Smart Access".
(You
can download the column, and sample database, for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,
I did try that out but no it doesnt work. the idea behind this is
the
fact that i have a form with the weekly view of the day that children
have
attended the nursery; from here, if i dbl- click on a price e.g. basic
full
day for 23/5/07 (dd/mm/yyyy) then a sub-form appears showing the date
23/5/07
and i can automatically change that session and also see whether a
funded
session has been booked.

I've also tried doing a replace. e.g. if 2 funded sessions are booked
on
23/05 i want to replace ONLY ONE session with a basic pm, it deletes
both
the
funded ones.


Not sure if this makes any sense at all. but its basically 2 problems
that
i
CANT solve.

Thanks once again.



--
Amina...


:

What does 04.06.07 mean to you: 04 June, 2007 or 06 April, 2007? If
it's
dd.mm.yy (and that's what your Regional Settings have set your Short
Date
format), try:

=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly
Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=" & Format([Forms]![Weekly
Child
View]![ChangeDate], "\#yyyy\-mm\-dd\#")

Access doesn't respect Regional Settings w.r.t. dates in SQL
statements.

Also, are you certain that the date field contains just a date, or
might
it
contain date and time (because it was populated with Now(), rather
than
Date())? If it does contain time, try:

" AND [Date] BETWEEN" & Format([Forms]![Weekly Child
View]![ChangeDate],
"\#yyyy\-mm\-dd\#") & " AND " & Format(DateAdd("d", 1,[Forms]![Weekly
Child
View]![ChangeDate]), "\#yyyy\-mm\-dd\#")



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,
I am trying to use the dfirst function to pick the first
description
of
a particular date. e.g first instance where the description 'NEF
Grant'
appears on the 04.06.07.

I have built this which doesn't seem to work.

=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly
Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#")

the text box appears blank although there is data to be displayed.
At
first
there wasn't an equals sign at the beginnig, hence showing 'name?#'
but
now
shows blank.

Any idea where I am going wrong?

Any help would be appreciated.

Thanks in advance.

 

[Amina]

YES!!! Thanks to your help, the Nz function worked.

I've put in what I used below, just so you know,

=Nz(DFirst("[Id]","[Invoice Items]","[Description] = 'NEF Grant' AND [Child
ID]=" & Forms![Children List].Form![Child ID] & " AND [Date]=" &
Format([SessionDate],"\#yyyy-mm-dd\#")),"No record returned")

Thanks once again, wouldn't have done it without those tips.



--
Amina...

Don't know whether it's relevant, but DFirst is a pretty meaningless
concept, since you can't control the underlying order of records.

Just for kicks, what happens if you use

=Nz(DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly
Child View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#"), "No record returned")

(Please believe me, though, that since your Short Date format is set to
dd/mm/yyyy, you MUST format the dates!)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The formatting doesn't work. Although I dont think this is the problem.
The
reason being it works in one place but doesnt work in another.

This one works:

DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#")

This one doesn't:


=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#")

Doesn't this seem a bit odd as there isn't much of a difference between
the
two.

Thanks

--
Amina...

Did you try formatting the date field like I suggested? Access will
correctly interpret 23/5/07 as 23 May, 2007 since there is no 23rd month.
However, it will ALWAYS interpret 04/06/07 as 06 April, 2007.

You should probably read Allen Browne's "International Dates in Access"
(http://www.allenbrowne.com/ser-36.html) , or what I had in my September,
2003 "Access Answers" column in Pinnacle Publication's "Smart Access".
(You
can download the column, and sample database, for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug,
I did try that out but no it doesnt work. the idea behind this is
the
fact that i have a form with the weekly view of the day that children
have
attended the nursery; from here, if i dbl- click on a price e.g. basic
full
day for 23/5/07 (dd/mm/yyyy) then a sub-form appears showing the date
23/5/07
and i can automatically change that session and also see whether a
funded
session has been booked.

I've also tried doing a replace. e.g. if 2 funded sessions are booked
on
23/05 i want to replace ONLY ONE session with a basic pm, it deletes
both
the
funded ones.


Not sure if this makes any sense at all. but its basically 2 problems
that
i
CANT solve.

Thanks once again.



--
Amina...


:

What does 04.06.07 mean to you: 04 June, 2007 or 06 April, 2007? If
it's
dd.mm.yy (and that's what your Regional Settings have set your Short
Date
format), try:

=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly
Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=" & Format([Forms]![Weekly
Child
View]![ChangeDate], "\#yyyy\-mm\-dd\#")

Access doesn't respect Regional Settings w.r.t. dates in SQL
statements.

Also, are you certain that the date field contains just a date, or
might
it
contain date and time (because it was populated with Now(), rather
than
Date())? If it does contain time, try:

" AND [Date] BETWEEN" & Format([Forms]![Weekly Child
View]![ChangeDate],
"\#yyyy\-mm\-dd\#") & " AND " & Format(DateAdd("d", 1,[Forms]![Weekly
Child
View]![ChangeDate]), "\#yyyy\-mm\-dd\#")



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,
I am trying to use the dfirst function to pick the first
description
of
a particular date. e.g first instance where the description 'NEF
Grant'
appears on the 04.06.07.

I have built this which doesn't seem to work.

=DFirst("[Id]","[Invoice Items]","[Description]='" & [Forms]![Weekly
Child
View]![Description] & "' AND [Child ID]=" & [Forms]![Children
List].[Form]![Child ID] & " AND [Date]=#" & [Forms]![Weekly Child
View]![ChangeDate] & "#")

the text box appears blank although there is data to be displayed.
At
first
there wasn't an equals sign at the beginnig, hence showing 'name?#'
but
now
shows blank.

Any idea where I am going wrong?

Any help would be appreciated.

Thanks in advance.