PC Review


Reply
Thread Tools Rate Thread

DMax with IIf

 
 
Robbie Doo
Guest
Posts: n/a
 
      7th Jan 2008
I have a table that has a Date field which I use a criteria to retrieve
certain information. Also, I have a field on my report that shows the last
date Travelled. However, when I use the IIf with Dmax function I only get the
correct information for that latest month. Is it possible to enter a criteria
for 'older' periods and still get the last date travelled for that period
showing in that field?

Example:
=IIf([TravelDate]<[End Date],DMax("[TravelDate]","[Mileage]","[EmpNum]=" &
[EmpNum]))

Thank you
 
Reply With Quote
 
 
 
 
Jeff Boyce
Guest
Posts: n/a
 
      7th Jan 2008
It appears your IIF() statement only handles one condition (when
[TravelDate]<[EndDate]). Where's what you want to have happen if that ISN'T
true?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Robbie Doo" <(E-Mail Removed)> wrote in message
news:48E137D4-5448-4CAE-A0AC-(E-Mail Removed)...
>I have a table that has a Date field which I use a criteria to retrieve
> certain information. Also, I have a field on my report that shows the last
> date Travelled. However, when I use the IIf with Dmax function I only get
> the
> correct information for that latest month. Is it possible to enter a
> criteria
> for 'older' periods and still get the last date travelled for that period
> showing in that field?
>
> Example:
> =IIf([TravelDate]<[End Date],DMax("[TravelDate]","[Mileage]","[EmpNum]=" &
> [EmpNum]))
>
> Thank you



 
Reply With Quote
 
Robbie Doo
Guest
Posts: n/a
 
      8th Jan 2008
Jeff..I would prefer the "Date" to be in Between "Startdate" and "EndDate",
but I don't know how to properly write the request.

"Jeff Boyce" wrote:

> It appears your IIF() statement only handles one condition (when
> [TravelDate]<[EndDate]). Where's what you want to have happen if that ISN'T
> true?
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "Robbie Doo" <(E-Mail Removed)> wrote in message
> news:48E137D4-5448-4CAE-A0AC-(E-Mail Removed)...
> >I have a table that has a Date field which I use a criteria to retrieve
> > certain information. Also, I have a field on my report that shows the last
> > date Travelled. However, when I use the IIf with Dmax function I only get
> > the
> > correct information for that latest month. Is it possible to enter a
> > criteria
> > for 'older' periods and still get the last date travelled for that period
> > showing in that field?
> >
> > Example:
> > =IIf([TravelDate]<[End Date],DMax("[TravelDate]","[Mileage]","[EmpNum]=" &
> > [EmpNum]))
> >
> > Thank you

>
>
>

 
Reply With Quote
 
Jeff Boyce
Guest
Posts: n/a
 
      8th Jan 2008
Let me ask a different way...

Your IIF() statement says, to paraphrase: If the TravelDate is less than
the End Date, do this.

My question was "... or if the TravelDate is NOT less than the End Date, do
what?"

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Robbie Doo" <(E-Mail Removed)> wrote in message
news:6D14AF62-6E33-41A2-8BC4-(E-Mail Removed)...
> Jeff..I would prefer the "Date" to be in Between "Startdate" and
> "EndDate",
> but I don't know how to properly write the request.
>
> "Jeff Boyce" wrote:
>
>> It appears your IIF() statement only handles one condition (when
>> [TravelDate]<[EndDate]). Where's what you want to have happen if that
>> ISN'T
>> true?
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "Robbie Doo" <(E-Mail Removed)> wrote in message
>> news:48E137D4-5448-4CAE-A0AC-(E-Mail Removed)...
>> >I have a table that has a Date field which I use a criteria to retrieve
>> > certain information. Also, I have a field on my report that shows the
>> > last
>> > date Travelled. However, when I use the IIf with Dmax function I only
>> > get
>> > the
>> > correct information for that latest month. Is it possible to enter a
>> > criteria
>> > for 'older' periods and still get the last date travelled for that
>> > period
>> > showing in that field?
>> >
>> > Example:
>> > =IIf([TravelDate]<[End
>> > Date],DMax("[TravelDate]","[Mileage]","[EmpNum]=" &
>> > [EmpNum]))
>> >
>> > Thank you

>>
>>
>>



 
Reply With Quote
 
Robbie Doo
Guest
Posts: n/a
 
      8th Jan 2008
I would like the statement to return a date that is the last date but smaller
than the end date.

"Jeff Boyce" wrote:

> Let me ask a different way...
>
> Your IIF() statement says, to paraphrase: If the TravelDate is less than
> the End Date, do this.
>
> My question was "... or if the TravelDate is NOT less than the End Date, do
> what?"
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "Robbie Doo" <(E-Mail Removed)> wrote in message
> news:6D14AF62-6E33-41A2-8BC4-(E-Mail Removed)...
> > Jeff..I would prefer the "Date" to be in Between "Startdate" and
> > "EndDate",
> > but I don't know how to properly write the request.
> >
> > "Jeff Boyce" wrote:
> >
> >> It appears your IIF() statement only handles one condition (when
> >> [TravelDate]<[EndDate]). Where's what you want to have happen if that
> >> ISN'T
> >> true?
> >>
> >> Regards
> >>
> >> Jeff Boyce
> >> Microsoft Office/Access MVP
> >>
> >> "Robbie Doo" <(E-Mail Removed)> wrote in message
> >> news:48E137D4-5448-4CAE-A0AC-(E-Mail Removed)...
> >> >I have a table that has a Date field which I use a criteria to retrieve
> >> > certain information. Also, I have a field on my report that shows the
> >> > last
> >> > date Travelled. However, when I use the IIf with Dmax function I only
> >> > get
> >> > the
> >> > correct information for that latest month. Is it possible to enter a
> >> > criteria
> >> > for 'older' periods and still get the last date travelled for that
> >> > period
> >> > showing in that field?
> >> >
> >> > Example:
> >> > =IIf([TravelDate]<[End
> >> > Date],DMax("[TravelDate]","[Mileage]","[EmpNum]=" &
> >> > [EmpNum]))
> >> >
> >> > Thank you
> >>
> >>
> >>

>
>
>

 
Reply With Quote
 
Jeff Boyce
Guest
Posts: n/a
 
      8th Jan 2008
To get a "last" date, you have to have them in some kind of order. Also,
you have to define what YOU mean by "last", because chances are very good
that Access doesn't use that definition.

Once you have that done, you could create a query that returns (your) the
"last" one for each ... (?for each what?).

Then you would do your comparison with "less than".

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Robbie Doo" <(E-Mail Removed)> wrote in message
news:B35508C4-8066-49EA-B678-(E-Mail Removed)...
>I would like the statement to return a date that is the last date but
>smaller
> than the end date.
>
> "Jeff Boyce" wrote:
>
>> Let me ask a different way...
>>
>> Your IIF() statement says, to paraphrase: If the TravelDate is less than
>> the End Date, do this.
>>
>> My question was "... or if the TravelDate is NOT less than the End Date,
>> do
>> what?"
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "Robbie Doo" <(E-Mail Removed)> wrote in message
>> news:6D14AF62-6E33-41A2-8BC4-(E-Mail Removed)...
>> > Jeff..I would prefer the "Date" to be in Between "Startdate" and
>> > "EndDate",
>> > but I don't know how to properly write the request.
>> >
>> > "Jeff Boyce" wrote:
>> >
>> >> It appears your IIF() statement only handles one condition (when
>> >> [TravelDate]<[EndDate]). Where's what you want to have happen if that
>> >> ISN'T
>> >> true?
>> >>
>> >> Regards
>> >>
>> >> Jeff Boyce
>> >> Microsoft Office/Access MVP
>> >>
>> >> "Robbie Doo" <(E-Mail Removed)> wrote in message
>> >> news:48E137D4-5448-4CAE-A0AC-(E-Mail Removed)...
>> >> >I have a table that has a Date field which I use a criteria to
>> >> >retrieve
>> >> > certain information. Also, I have a field on my report that shows
>> >> > the
>> >> > last
>> >> > date Travelled. However, when I use the IIf with Dmax function I
>> >> > only
>> >> > get
>> >> > the
>> >> > correct information for that latest month. Is it possible to enter a
>> >> > criteria
>> >> > for 'older' periods and still get the last date travelled for that
>> >> > period
>> >> > showing in that field?
>> >> >
>> >> > Example:
>> >> > =IIf([TravelDate]<[End
>> >> > Date],DMax("[TravelDate]","[Mileage]","[EmpNum]=" &
>> >> > [EmpNum]))
>> >> >
>> >> > Thank you
>> >>
>> >>
>> >>

>>
>>
>>



 
Reply With Quote
 
Robbie Doo
Guest
Posts: n/a
 
      9th Jan 2008
What I ment by each was the period. Let say the person had driven in Nov and
in Dec. When I'm trying to get the mileage in Nov it gives me the last date
driven in Dec. I would like to get the last date driven in Nov instead
becuase that's the report I'm requesting.

"Jeff Boyce" wrote:

> To get a "last" date, you have to have them in some kind of order. Also,
> you have to define what YOU mean by "last", because chances are very good
> that Access doesn't use that definition.
>
> Once you have that done, you could create a query that returns (your) the
> "last" one for each ... (?for each what?).
>
> Then you would do your comparison with "less than".
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "Robbie Doo" <(E-Mail Removed)> wrote in message
> news:B35508C4-8066-49EA-B678-(E-Mail Removed)...
> >I would like the statement to return a date that is the last date but
> >smaller
> > than the end date.
> >
> > "Jeff Boyce" wrote:
> >
> >> Let me ask a different way...
> >>
> >> Your IIF() statement says, to paraphrase: If the TravelDate is less than
> >> the End Date, do this.
> >>
> >> My question was "... or if the TravelDate is NOT less than the End Date,
> >> do
> >> what?"
> >>
> >> Regards
> >>
> >> Jeff Boyce
> >> Microsoft Office/Access MVP
> >>
> >> "Robbie Doo" <(E-Mail Removed)> wrote in message
> >> news:6D14AF62-6E33-41A2-8BC4-(E-Mail Removed)...
> >> > Jeff..I would prefer the "Date" to be in Between "Startdate" and
> >> > "EndDate",
> >> > but I don't know how to properly write the request.
> >> >
> >> > "Jeff Boyce" wrote:
> >> >
> >> >> It appears your IIF() statement only handles one condition (when
> >> >> [TravelDate]<[EndDate]). Where's what you want to have happen if that
> >> >> ISN'T
> >> >> true?
> >> >>
> >> >> Regards
> >> >>
> >> >> Jeff Boyce
> >> >> Microsoft Office/Access MVP
> >> >>
> >> >> "Robbie Doo" <(E-Mail Removed)> wrote in message
> >> >> news:48E137D4-5448-4CAE-A0AC-(E-Mail Removed)...
> >> >> >I have a table that has a Date field which I use a criteria to
> >> >> >retrieve
> >> >> > certain information. Also, I have a field on my report that shows
> >> >> > the
> >> >> > last
> >> >> > date Travelled. However, when I use the IIf with Dmax function I
> >> >> > only
> >> >> > get
> >> >> > the
> >> >> > correct information for that latest month. Is it possible to enter a
> >> >> > criteria
> >> >> > for 'older' periods and still get the last date travelled for that
> >> >> > period
> >> >> > showing in that field?
> >> >> >
> >> >> > Example:
> >> >> > =IIf([TravelDate]<[End
> >> >> > Date],DMax("[TravelDate]","[Mileage]","[EmpNum]=" &
> >> >> > [EmpNum]))
> >> >> >
> >> >> > Thank you
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>

>
>
>

 
Reply With Quote
 
Jeff Boyce
Guest
Posts: n/a
 
      9th Jan 2008
You have to tell Access what you are looking for.

In the example you gave, you'd have to tell Access that the month for which
you want records is "11" (November). You could use:

YourMonth: Month([YourDateTimeField)

as a new field in a query, then use a parameter prompt in the Selection
Criterion, something like:

[Enter the number of the month]

Please note, however, that this will return ALL Novembers, not just the one
you wanted (remember, Access can't read your mind!).

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Robbie Doo" <(E-Mail Removed)> wrote in message
news:F39EF789-0215-4745-BE60-(E-Mail Removed)...
> What I ment by each was the period. Let say the person had driven in Nov
> and
> in Dec. When I'm trying to get the mileage in Nov it gives me the last
> date
> driven in Dec. I would like to get the last date driven in Nov instead
> becuase that's the report I'm requesting.
>
> "Jeff Boyce" wrote:
>
>> To get a "last" date, you have to have them in some kind of order. Also,
>> you have to define what YOU mean by "last", because chances are very good
>> that Access doesn't use that definition.
>>
>> Once you have that done, you could create a query that returns (your) the
>> "last" one for each ... (?for each what?).
>>
>> Then you would do your comparison with "less than".
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "Robbie Doo" <(E-Mail Removed)> wrote in message
>> news:B35508C4-8066-49EA-B678-(E-Mail Removed)...
>> >I would like the statement to return a date that is the last date but
>> >smaller
>> > than the end date.
>> >
>> > "Jeff Boyce" wrote:
>> >
>> >> Let me ask a different way...
>> >>
>> >> Your IIF() statement says, to paraphrase: If the TravelDate is less
>> >> than
>> >> the End Date, do this.
>> >>
>> >> My question was "... or if the TravelDate is NOT less than the End
>> >> Date,
>> >> do
>> >> what?"
>> >>
>> >> Regards
>> >>
>> >> Jeff Boyce
>> >> Microsoft Office/Access MVP
>> >>
>> >> "Robbie Doo" <(E-Mail Removed)> wrote in message
>> >> news:6D14AF62-6E33-41A2-8BC4-(E-Mail Removed)...
>> >> > Jeff..I would prefer the "Date" to be in Between "Startdate" and
>> >> > "EndDate",
>> >> > but I don't know how to properly write the request.
>> >> >
>> >> > "Jeff Boyce" wrote:
>> >> >
>> >> >> It appears your IIF() statement only handles one condition (when
>> >> >> [TravelDate]<[EndDate]). Where's what you want to have happen if
>> >> >> that
>> >> >> ISN'T
>> >> >> true?
>> >> >>
>> >> >> Regards
>> >> >>
>> >> >> Jeff Boyce
>> >> >> Microsoft Office/Access MVP
>> >> >>
>> >> >> "Robbie Doo" <(E-Mail Removed)> wrote in message
>> >> >> news:48E137D4-5448-4CAE-A0AC-(E-Mail Removed)...
>> >> >> >I have a table that has a Date field which I use a criteria to
>> >> >> >retrieve
>> >> >> > certain information. Also, I have a field on my report that shows
>> >> >> > the
>> >> >> > last
>> >> >> > date Travelled. However, when I use the IIf with Dmax function I
>> >> >> > only
>> >> >> > get
>> >> >> > the
>> >> >> > correct information for that latest month. Is it possible to
>> >> >> > enter a
>> >> >> > criteria
>> >> >> > for 'older' periods and still get the last date travelled for
>> >> >> > that
>> >> >> > period
>> >> >> > showing in that field?
>> >> >> >
>> >> >> > Example:
>> >> >> > =IIf([TravelDate]<[End
>> >> >> > Date],DMax("[TravelDate]","[Mileage]","[EmpNum]=" &
>> >> >> > [EmpNum]))
>> >> >> >
>> >> >> > Thank you
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>

>>
>>
>>



 
Reply With Quote
 
Robbie Doo
Guest
Posts: n/a
 
      9th Jan 2008
If I create a new field "MyMonth" then I would have 2 parameters in the query
because one already asks for Between...And in the [TravelDate] field. Would
that be ok?

"Jeff Boyce" wrote:

> You have to tell Access what you are looking for.
>
> In the example you gave, you'd have to tell Access that the month for which
> you want records is "11" (November). You could use:
>
> YourMonth: Month([YourDateTimeField)
>
> as a new field in a query, then use a parameter prompt in the Selection
> Criterion, something like:
>
> [Enter the number of the month]
>
> Please note, however, that this will return ALL Novembers, not just the one
> you wanted (remember, Access can't read your mind!).
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "Robbie Doo" <(E-Mail Removed)> wrote in message
> news:F39EF789-0215-4745-BE60-(E-Mail Removed)...
> > What I ment by each was the period. Let say the person had driven in Nov
> > and
> > in Dec. When I'm trying to get the mileage in Nov it gives me the last
> > date
> > driven in Dec. I would like to get the last date driven in Nov instead
> > becuase that's the report I'm requesting.
> >
> > "Jeff Boyce" wrote:
> >
> >> To get a "last" date, you have to have them in some kind of order. Also,
> >> you have to define what YOU mean by "last", because chances are very good
> >> that Access doesn't use that definition.
> >>
> >> Once you have that done, you could create a query that returns (your) the
> >> "last" one for each ... (?for each what?).
> >>
> >> Then you would do your comparison with "less than".
> >>
> >> Regards
> >>
> >> Jeff Boyce
> >> Microsoft Office/Access MVP
> >>
> >> "Robbie Doo" <(E-Mail Removed)> wrote in message
> >> news:B35508C4-8066-49EA-B678-(E-Mail Removed)...
> >> >I would like the statement to return a date that is the last date but
> >> >smaller
> >> > than the end date.
> >> >
> >> > "Jeff Boyce" wrote:
> >> >
> >> >> Let me ask a different way...
> >> >>
> >> >> Your IIF() statement says, to paraphrase: If the TravelDate is less
> >> >> than
> >> >> the End Date, do this.
> >> >>
> >> >> My question was "... or if the TravelDate is NOT less than the End
> >> >> Date,
> >> >> do
> >> >> what?"
> >> >>
> >> >> Regards
> >> >>
> >> >> Jeff Boyce
> >> >> Microsoft Office/Access MVP
> >> >>
> >> >> "Robbie Doo" <(E-Mail Removed)> wrote in message
> >> >> news:6D14AF62-6E33-41A2-8BC4-(E-Mail Removed)...
> >> >> > Jeff..I would prefer the "Date" to be in Between "Startdate" and
> >> >> > "EndDate",
> >> >> > but I don't know how to properly write the request.
> >> >> >
> >> >> > "Jeff Boyce" wrote:
> >> >> >
> >> >> >> It appears your IIF() statement only handles one condition (when
> >> >> >> [TravelDate]<[EndDate]). Where's what you want to have happen if
> >> >> >> that
> >> >> >> ISN'T
> >> >> >> true?
> >> >> >>
> >> >> >> Regards
> >> >> >>
> >> >> >> Jeff Boyce
> >> >> >> Microsoft Office/Access MVP
> >> >> >>
> >> >> >> "Robbie Doo" <(E-Mail Removed)> wrote in message
> >> >> >> news:48E137D4-5448-4CAE-A0AC-(E-Mail Removed)...
> >> >> >> >I have a table that has a Date field which I use a criteria to
> >> >> >> >retrieve
> >> >> >> > certain information. Also, I have a field on my report that shows
> >> >> >> > the
> >> >> >> > last
> >> >> >> > date Travelled. However, when I use the IIf with Dmax function I
> >> >> >> > only
> >> >> >> > get
> >> >> >> > the
> >> >> >> > correct information for that latest month. Is it possible to
> >> >> >> > enter a
> >> >> >> > criteria
> >> >> >> > for 'older' periods and still get the last date travelled for
> >> >> >> > that
> >> >> >> > period
> >> >> >> > showing in that field?
> >> >> >> >
> >> >> >> > Example:
> >> >> >> > =IIf([TravelDate]<[End
> >> >> >> > Date],DMax("[TravelDate]","[Mileage]","[EmpNum]=" &
> >> >> >> > [EmpNum]))
> >> >> >> >
> >> >> >> > Thank you
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>

>
>
>

 
Reply With Quote
 
Jeff Boyce
Guest
Posts: n/a
 
      10th Jan 2008
I'm having trouble imagining your query.

Post the SQL statement for the query.

By the way, what happens when you try it?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Robbie Doo" <(E-Mail Removed)> wrote in message
news:9327E7C0-629B-4386-B17E-(E-Mail Removed)...
> If I create a new field "MyMonth" then I would have 2 parameters in the
> query
> because one already asks for Between...And in the [TravelDate] field.
> Would
> that be ok?
>
> "Jeff Boyce" wrote:
>
>> You have to tell Access what you are looking for.
>>
>> In the example you gave, you'd have to tell Access that the month for
>> which
>> you want records is "11" (November). You could use:
>>
>> YourMonth: Month([YourDateTimeField)
>>
>> as a new field in a query, then use a parameter prompt in the Selection
>> Criterion, something like:
>>
>> [Enter the number of the month]
>>
>> Please note, however, that this will return ALL Novembers, not just the
>> one
>> you wanted (remember, Access can't read your mind!).
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "Robbie Doo" <(E-Mail Removed)> wrote in message
>> news:F39EF789-0215-4745-BE60-(E-Mail Removed)...
>> > What I ment by each was the period. Let say the person had driven in
>> > Nov
>> > and
>> > in Dec. When I'm trying to get the mileage in Nov it gives me the last
>> > date
>> > driven in Dec. I would like to get the last date driven in Nov instead
>> > becuase that's the report I'm requesting.
>> >
>> > "Jeff Boyce" wrote:
>> >
>> >> To get a "last" date, you have to have them in some kind of order.
>> >> Also,
>> >> you have to define what YOU mean by "last", because chances are very
>> >> good
>> >> that Access doesn't use that definition.
>> >>
>> >> Once you have that done, you could create a query that returns (your)
>> >> the
>> >> "last" one for each ... (?for each what?).
>> >>
>> >> Then you would do your comparison with "less than".
>> >>
>> >> Regards
>> >>
>> >> Jeff Boyce
>> >> Microsoft Office/Access MVP
>> >>
>> >> "Robbie Doo" <(E-Mail Removed)> wrote in message
>> >> news:B35508C4-8066-49EA-B678-(E-Mail Removed)...
>> >> >I would like the statement to return a date that is the last date but
>> >> >smaller
>> >> > than the end date.
>> >> >
>> >> > "Jeff Boyce" wrote:
>> >> >
>> >> >> Let me ask a different way...
>> >> >>
>> >> >> Your IIF() statement says, to paraphrase: If the TravelDate is
>> >> >> less
>> >> >> than
>> >> >> the End Date, do this.
>> >> >>
>> >> >> My question was "... or if the TravelDate is NOT less than the End
>> >> >> Date,
>> >> >> do
>> >> >> what?"
>> >> >>
>> >> >> Regards
>> >> >>
>> >> >> Jeff Boyce
>> >> >> Microsoft Office/Access MVP
>> >> >>
>> >> >> "Robbie Doo" <(E-Mail Removed)> wrote in message
>> >> >> news:6D14AF62-6E33-41A2-8BC4-(E-Mail Removed)...
>> >> >> > Jeff..I would prefer the "Date" to be in Between "Startdate" and
>> >> >> > "EndDate",
>> >> >> > but I don't know how to properly write the request.
>> >> >> >
>> >> >> > "Jeff Boyce" wrote:
>> >> >> >
>> >> >> >> It appears your IIF() statement only handles one condition (when
>> >> >> >> [TravelDate]<[EndDate]). Where's what you want to have happen
>> >> >> >> if
>> >> >> >> that
>> >> >> >> ISN'T
>> >> >> >> true?
>> >> >> >>
>> >> >> >> Regards
>> >> >> >>
>> >> >> >> Jeff Boyce
>> >> >> >> Microsoft Office/Access MVP
>> >> >> >>
>> >> >> >> "Robbie Doo" <(E-Mail Removed)> wrote in
>> >> >> >> message
>> >> >> >> news:48E137D4-5448-4CAE-A0AC-(E-Mail Removed)...
>> >> >> >> >I have a table that has a Date field which I use a criteria to
>> >> >> >> >retrieve
>> >> >> >> > certain information. Also, I have a field on my report that
>> >> >> >> > shows
>> >> >> >> > the
>> >> >> >> > last
>> >> >> >> > date Travelled. However, when I use the IIf with Dmax function
>> >> >> >> > I
>> >> >> >> > only
>> >> >> >> > get
>> >> >> >> > the
>> >> >> >> > correct information for that latest month. Is it possible to
>> >> >> >> > enter a
>> >> >> >> > criteria
>> >> >> >> > for 'older' periods and still get the last date travelled for
>> >> >> >> > that
>> >> >> >> > period
>> >> >> >> > showing in that field?
>> >> >> >> >
>> >> >> >> > Example:
>> >> >> >> > =IIf([TravelDate]<[End
>> >> >> >> > Date],DMax("[TravelDate]","[Mileage]","[EmpNum]=" &
>> >> >> >> > [EmpNum]))
>> >> >> >> >
>> >> >> >> > Thank you
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>

>>
>>
>>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
RE: DMAX Beetle Microsoft Access Forms 0 13th Nov 2008 06:25 PM
DMax sturner333 Microsoft Access Queries 2 10th Apr 2008 08:10 PM
DMax and more!! =?Utf-8?B?RGF2aWQj?= Microsoft Access Queries 1 7th Sep 2005 04:26 AM
DMax for dates in rpt group header returns DMax for entire records =?Utf-8?B?aGVsaW9z?= Microsoft Access Getting Started 1 19th Jul 2005 09:32 PM
A little bit more help on DMax +1 Tina Microsoft Access Form Coding 4 19th Feb 2004 01:36 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:47 PM.