DMAX Problem driving me nuts.. HELP PLS

[hardy1976]

Hi,

I have the following statement...

lastwaste = DMax("[wastebatch]", "[Job Sheet]", "[sheetid] = " & SheetID)

which works fine.... however I need to exlude instances where the is a "-"
I tried

lastwaste = DMax("[wastebatch]", "[Job Sheet]", "[wastebatch]" <> "-" And "[sheetid] = " & SheetID)

but it gives me a type mismatch error.. any ideas? (the wastebatch field is text)

Regards
H

 

[LinData]

Select text within single quotes

Try

lastwaste = DMax("[wastebatch]", "[Job Sheet]", "[wastebatch]<>'-' And [sheetid] = " & SheetID)

When a condition is based upon text, the text should be enclosed within single quotes

Regards
LinData

 

[hardy1976]

LinData thanks, so simple!!!

Nearly there it seems to give me blank now?! any ideas?

 

[LinData]

A Blank result could be a number of different things.

The first thing to understand is that you are trying to do a DMax on a text field and this can return unpredictable results, like the following...

Field Contents
null
-
1
2
3
4
5
10

DMax will return 5 as the highest ! However....

Because you have 2 conditionals [wastebatch] and [sheetid], my guess would be that at least one record has a null (empty) field, this will spook DMax (or any of the other domain aggregate function) and it will return blank.
Check all the records and make sure that they contain data (both [wastebatch] and [sheetid])

Regards
LinData

 

[hardy1976]

thanks, I've now used a query - seems safer and the max works correctly to.

Thanks for the effort.
h