Unwanted bug when using DCount in a function

[Kharlam]

I am using the following function below to generate a daily reference number,
when creating a new booking. I get the intended Reference Number for all new
bookings when the date for the booking is the current date or greater.

When I want to back-date add a new booking on any of the previous 6 days
from the current date, I am always getting intRefNumber passed on to the
getRefNumber, i.e 400,500,600,700 etc. OrdersCount is not being added to
getRefNumber.

I am using Access 2007 in Windows Vista Ultimate environment.

If anyone can shed any light on the issue, it would be greatly appreciated.

Thanks for taking the time to look at this post.

Cheers Kharlam.

Code
Public Function getRefNumber(myDay As Date) As Integer
Dim intwDay, intRefNumber, OrdersCount As Integer
intwDay = Format(myDay, "w")

OrdersCount = DCount("[OrderID]", "Orders", "[OrderPickupDate] = #" &
myDay & "#")
If OrdersCount > 100 Then
OrdersCount = OrdersCount - 99
Else
OrdersCount = OrdersCount + 1
End If

Select Case intwDay
Case 1 'Sunday
intRefNumber = 700
Case 2 'Monday
intRefNumber = 100
Case 3 'Tuesday
intRefNumber = 200
Case 4 'Wednesday
intRefNumber = 300
Case 5 'Thursday
intRefNumber = 400
Case 6 'Friday
intRefNumber = 500
Case 7 'Saturday
intRefNumber = 600
End Select
getRefNumber = OrdersCount + intRefNumber
End Function

Private Sub cmdNew_Click()
Dim newDate As Date
newDate = Form_BookingsFrm.txtViewDate
DoCmd.OpenForm "BookingsMakeFrm"
DoCmd.GoToRecord , , acNewRec
Form_BookingsMakeFrm.OrderPickUpDate = newDate
Form_BookingsMakeFrm.OrderRefNumber = getRefNumber(newDate)
DoCmd.Close acForm, "BookingsFrm"
End Sub
Code

 

[Jeff Boyce]

So, what do you want Access to do if the 'count' is ... Null? (no records
matching the criteria)

Try surrounding the DCount() function with an Nz() function to handle that
situation.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Kharlam]

Thank you very much Jeff for steering me in the right direction, I managed to
overcome the problem with the following changes to my code aswell as the
addition of a new query.
Code
OrdersCount = Nz(DCount("OrderID", "qryOrderCount"), 0)

qryOrderCount
SELECT Orders.OrderID
FROM Orders
WHERE ((OrderPickUpDate)=[Forms]![BookingsFrm]![txtViewDate]);

Working as intended.

Cheers Kharlam

 

[Jeff Boyce]

You are welcome.

Thanks for posting your final solution -- perhaps some day some other reader
will need the same solution!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you very much Jeff for steering me in the right direction, I managed
to
overcome the problem with the following changes to my code aswell as the
addition of a new query.
Code
OrdersCount = Nz(DCount("OrderID", "qryOrderCount"), 0)

qryOrderCount
SELECT Orders.OrderID
FROM Orders
WHERE ((OrderPickUpDate)=[Forms]![BookingsFrm]![txtViewDate]);

Working as intended.

Cheers Kharlam

So, what do you want Access to do if the 'count' is ... Null? (no records
matching the criteria)

Try surrounding the DCount() function with an Nz() function to handle
that
situation.

Regards

Jeff Boyce
Microsoft Office/Access MVP