DFirst

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Amina - 04 Jul 2007 18:10 GMT

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.

Signature

Amina...

Reply to this Message

Douglas J. Steele - 04 Jul 2007 18:18 GMT

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\#")

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi,
> I am trying to use the dfirst function to pick the first description of
[quoted text clipped - 19 lines]
>
> Thanks in advance.

Reply to this Message

Amina - 04 Jul 2007 18:38 GMT

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.

Signature

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
[quoted text clipped - 38 lines]
> >
> > Thanks in advance.

Reply to this Message

Douglas J. Steele - 04 Jul 2007 18:48 GMT

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)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Doug,
> I did try that out but no it doesnt work. the idea behind this is the
[quoted text clipped - 65 lines]
>> >
>> > Thanks in advance.

Reply to this Message

Amina - 04 Jul 2007 20:08 GMT

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

Signature

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.
[quoted text clipped - 75 lines]
> >> >
> >> > Thanks in advance.

Reply to this Message

Douglas J. Steele - 04 Jul 2007 20:20 GMT

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!)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> The formatting doesn't work. Although I dont think this is the problem.
> The
[quoted text clipped - 116 lines]
>> >> >
>> >> > Thanks in advance.

Reply to this Message

Amina - 04 Jul 2007 22:34 GMT

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.

Signature

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.
[quoted text clipped - 129 lines]
> >> >> >
> >> >> > Thanks in advance.

Reply to this Message

Amina - 04 Jul 2007 23:16 GMT

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.

Signature

Amina...

Reply to this Message

Bob Quintal - 04 Jul 2007 21:15 GMT

> Hi,
> I am trying to use the dfirst function to pick the first
[quoted text clipped - 18 lines]
>
> 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.

Signature

Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Reply to this Message