Thank you Jeff. After a lot of trial & error I was able to figure out the
code. However, I had posted another question regarding "Combo to Checkbox"
and never got a response on. Maybe you can help me with that?
I have a listbox which I'm trying to show on a report as checkboxes. When
I
put the listbox reference on the report I get only the last record to show
as
a checkbox, the rest of them are ignored/not counted. There maybe more
than 1
records picked from the drop-down, why do I get only the last one?
Each "unbound" box's control source is something like this:
box 1: =IIf([PrimaryId]=1,True,False)
box 2: =IIf([PrimaryId]=2,True,False)
....and so on (up to 9)
These boxes are on the report and only the last item shows a checkmark.
There maybe more but they're ignored.
Any help?
Jeff Boyce said:
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
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?
:
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
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.
:
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
I would like the statement to return a date that is the last date
but
smaller
than the end date.
:
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
message
Jeff..I would prefer the "Date" to be in Between "Startdate"
and
"EndDate",
but I don't know how to properly write the request.
:
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
message
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
