Strange results with DCount

[AccessAddict]

Hi

I'm using a DCount function to check if a selected date in a combo box and ID number in a text field (both controls on the same subform) combination already exists in the underlying table, code of which is

Private Sub cboDateCom_BeforeUpdate(Cancel As Integer)

'make sure date has not already been used
If DCount("*", "tblCost", "[AccName] = " & Forms![frmAccCost].Form![frmsubCost]![AccName] & " AND [DateCom] = #" & Forms![frmAccCost].Form![frmsubCost]![cboDateCom] & "#&quot > 0 Then

MsgBox "You have already Entered that Date!!", 48, "Date Already Used"
Cancel = True
End If

End Sub

(field AccName is an ID number ie integer and not string).

My problem is that the function works only some of the time. (The combo box Row Source is simple select query and Control Source a field of tblCost) For example, if the user selects 28/6/03 (which already exists in the underlying table) from the combo box, the code runs fine (message box displays etc), but if 5/7/03 is selected, even though that date also exists nothing happens. There seems to be no logic to which dates the function picks up and which it doesn't, examples

28/6/03, 19/7/03, 26/7/03, 16/8/03 21/6/03 are all picked up OK (in other words the DCount function evaluates as 1) but 5/7/03, 12/7/03, 2/8/03, 9/8/03 7/6/03 don't trigger the message (DCount evaluates to 0)

It has me totally stumped, so any ideas are gratefully accepted

Cheers

Alan

 

[BillPower]

Hi AccessAddict,

In queries Dates have to often queried in US Format, maybe the same for DCount. Have atry anyway.

If DCount("*", "tblCost", "[AccName] = " & Forms![frmAccCost].Form![frmsubCost]![AccName] & " AND [DateCom] = #" & Format(Forms![frmAccCost].Form![frmsubCost]![cboDateCom],"mm/dd/yy&quot & "#&quot > 0 Then

Bill

 

[scriverb]

I think Bill is correct. I have experienced difficulty when evaluating and dates when the dd/mm/yy format was used. Sometimes it recognizes it and sometimes not.

You will also note that the dates that work correctly are dates that can only be interpreted as dd/mm/yy while the dates that didn't evaluate correctly could be evaluated as valid dates in both formats (dd/mm/yy and mm/dd/yy)

28/6/03, 19/7/03, 26/7/03, 16/8/03 21/6/03 are all picked up OK (in other words the DCount function evaluates as 1) but 5/7/03, 12/7/03, 2/8/03, 9/8/03 7/6/03 don't trigger the message (DCount evaluates to 0)

Just something to take notice of.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[AccessAddict]

Hi

That solved the problem. Many thanks - I'd have never thought of that in a million years but will be very aware of it in future. So much to learn and so little time...........

Thanks again

Alan