Another Subform Question

[Ted]

Sometimes there are some tricky issues with checking for
null values. You may want to try checking to see if the
value is greater than 0, and if so use the value,
otherwise use the alternate value (basically just reverse
the if statement logic). I'm not sure if this will solve
the problem, but it may be worth trying.

 

[DavidW]

I did try that and I got the error that there was no value.

How do you mean reverse the logic?.

What I have done for now was to insert 286 records for each vehicle, inturn
that populated the query, and the code works with null values in the query.
I think the problem is that there are no records in the query, and in turn
it sees nothing(null or 0 values), how do you handle something that does
exist?
Thanks for responding!

 

[Ted]

Oh, OK I think I have a little better understanding now.
Maybe you could try adjusting your original query
calculation where you find caclute the queryfield
named "emile" (the maximum of some usage records if I am
following correctly) to return 0 if no records are
found. That way, you will always have a match in the
query recordsource.

Or, possibly better yet, maybe you could just change the
source for the e-mile field on the form to use the DMax()
function to lookup the maximum usage from the other table
instead of using a query. The DMax () function will
return Null if no records are found, so that would
hopefully eliminate your problem.

 

[Ted]

PS, sorry for the bad grammar in the first paragraph of
my last message, I rewrote one of the sentences and
missed deleting the word "find", and I don't know how I
came up with that spelling for calculate. Anyway,
hopefully you can make sense of it.

 

[DavidW]

Oh, OK I think I have a little better understanding now.
Maybe you could try adjusting your original query
calculation where you find caclute the queryfield
named "emile" (the maximum of some usage records if I am
following correctly) to return 0 if no records are
found. That way, you will always have a match in the
query recordsource.

how do you get the query to return 0 if no records are found. If this will
work it seems like this is the better candidate.

Or, possibly better yet, maybe you could just change the
source for the e-mile field on the form to use the DMax()
function to lookup the maximum usage from the other table
instead of using a query. The DMax () function will
return Null if no records are found, so that would
hopefully eliminate your problem.

I tried that ,the only problem is the form is in continious form mode, when
I used the DMax() function it did just that, except it wasnt for each
individual record, it looked for the max entry in the recordset and copied
that 1 number to every record.

 

[Ted]

I'm not sure how your query is currently calculating the
max, so I'm not sure what the best way would be, but
here's my best guess.

My guess is that you are grouping the records of a table,
and you have a field that is taking the max of one of the
table's fields. Then, the problem is that there aren't
any records for some of the records in your form (which
is grouped by vehicle ID or something similar).

If that is the case, you could modify the query so that
the vehicles table (or whatever table would provide the
same group of records that your form is using) is also
included (linked by vehicle ID or something like that).
For the relationship, you would set the join so that the
vehicles table is driving the relationship (Include all
records from tblvehicles and only those from tblusage
that match). Then you could group by the vehicle ID (or
again, whatever would match your form records 1:1) and
calc the max of the usage field. If there is not a match
in the usage table, the field would be blank (null) if
not, the max would be listed. But, you would have a
record for all form records.

This would probably solve your problem as it is. But, if
you wanted to change the nulls to 0's you could use an
iif statement in another field in the query to something
like "iif(isnull([maxofusage])=true,0,[maxofusage])"

Of course, I have had to make some assumptions about your
query and database structure so I'm not sure if this
would work. Maybe it will give you some ideas though.

 

[DavidW]

Good Job!
Hopefully One Of These Days, I will learn everything about this program,
STILL LEARNING THE ROPES.

I already had a relationship specified, but I didnt have the setting set
right.

Setting the show records to show all from one worked like a charm!

Forever Grateful
David