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