Dcount of date field

[carll]

Hi All
I am getting a little frustrated at my own inability, so please bear with me!

Upon entering a date on a form I wish to check to see if there are more than, say 8 occurrences of this date, as I cannot ship more than 8 times a day.

I first of all created a TOTALS query that referenced the form date: [Forms]![Work Orders New]![Operations Confirmed Date] and used the count on the ID field, this return the correct count of records.

I believe in researching this that a better way to get an answer into VBA is with the DCount function.

I created the following in a text box just to see if I could make this work:=DCount("[ID]","Works Orders","Operations Confirmed Date=[Forms]![Work Orders New]![Operations Confirmed Date]") however this returns a count of the total number of records??

All advice warmly welcomed!

Once I can get this working, I need to ultimately put the correct code into the on dirty function of "Operations Confirmed Date" that if the count is greater than 8, then a message box pops up saying too many shipments on selected day.

Carl.

 

[Solo712]

Hi All
I am getting a little frustrated at my own inability, so please bear with me!

Upon entering a date on a form I wish to check to see if there are more than, say 8 occurrences of this date, as I cannot ship more than 8 times a day.

I first of all created a TOTALS query that referenced the form date: [Forms]![Work Orders New]![Operations Confirmed Date] and used the count on the ID field, this return the correct count of records.

I believe in researching this that a better way to get an answer into VBA is with the DCount function.

I created the following in a text box just to see if I could make this work:=DCount("[ID]","Works Orders","Operations Confirmed Date=[Forms]![Work Orders New]![Operations Confirmed Date]") however this returns a count of the total number of records??

All advice warmly welcomed!

Once I can get this working, I need to ultimately put the correct code into the on dirty function of "Operations Confirmed Date" that if the count is greater than 8, then a message box pops up saying too many shipments on selected day.

Carl.

Hi Carl, for starters I doubt that the DCount statement as you presented it would execute at all:

DCount("[ID]","Works Orders","Operations Confirmed Date=[Forms]![Work Orders New]![Operations Confirmed Date]")

You need to use square brackets for fields that have space in their names. The Criteria argument needs to be formatted for dates. Also, is it "Work Orders" or "Works Orders" ? Like this ?

DCount("[ID]","[Work Orders]","[Operations Confirmed Date]=#" &
 [Forms]![Work Orders New]![Operations Confirmed Date] & "#")

If you don't get the result you are expecting after this change, you need to look at what you have in the "Operations Confirmed Date". Is the table or query restricting the date to the current day?

Best,
Jiri

 

[carll]

Hi Jiri
Thanks for the fast response

The table is called Works Orders, so I changed this in your code to:
DCount("[ID]","[Works Orders]","[Operations Confirmed Date]=#" & [Forms]![Work Orders New]![Operations Confirmed Date] & "#")

I am getting an error: #Name?

Thanks
Carl.

 

[MarkK]

You need the equals sign, the Forms reference will work inside the string and without delimiters, and common practice is to not use spaces in identifiers. Making those changes yields...

=DCount("*","WorksOrders","OperationsConfirmedDate = Forms!WorkOrdersNew.OperationsConfirmedDate")

hth
Mark

 

[arnelgp]

how about:

=DCount("*","[Work Orders]","[Operations Confirmed Date]=#" & Format([Forms]![Work Orders New]![Operations Confimed Date], "mm/dd/yyyy") & "#")

 

[Mark_]

Question regarding scope,

is [Forms]![Work Orders New]![Operations Confirmed Date] on the form you are displaying the results in? If so you can use Me.[Operations Confirmed Date] instead. For myself, I do this so I remember where I am doing this and that I EXPECT said field to be in the same form. It tends to help when debugging as I know I will need to make sure another form is also loaded any time I see [Forms]! at the beginning.

 

[moke123]

Question regarding scope,

is [Forms]![Work Orders New]![Operations Confirmed Date] on the form you are displaying the results in? If so you can use Me.[Operations Confirmed Date] instead. For myself, I do this so I remember where I am doing this and that I EXPECT said field to be in the same form. It tends to help when debugging as I know I will need to make sure another form is also loaded any time I see [Forms]! at the beginning.

The other bonus to using the Me. keyword is intellisense providing the correct spelling for the control.

 

[carll]

how about:

=DCount("*","[Work Orders]","[Operations Confirmed Date]=#" & Format([Forms]![Work Orders New]![Operations Confimed Date], "mm/dd/yyyy") & "#")

Hi
This produced a different error: #Type!

Carl.

 

[arnelgp]

1. On design view, set the Format
of [Operations Confirmed Date] to Short Date.

2. Remove the Expression (Control Source) from your Textbox,
The one you need to display the count.
remove this =DCount(....)

3. Add an Unbound TextBox.
Name it txtDate.

4. Set its Visible Property to No.

5. Add the following code to your
[Operations Confimed Date] Events:

Private Sub Operations_Confirmed_Date_AfterUpdate()
Me.txtDate = Me.Operations_Confirmed_Date
Call thisMsg
End Sub

Private Sub Operations_Confirmed_Date_Change()
Me.txtDate = Me.Operations_Confirmed_Date.Text
If IsDate(Me.txtDate) Then _
Call thisMsg
End Sub

Private Sub thisMsg()
' Replace Me.yourTextBoxNameToDisplayCount, with correct textboxname.
Me.yourTextBoxNameToDisplayCount = DCount("*", "[Work Orders]", IIf(IsNull([txtDate]), "(1=1)", "[Operations Confirmed Date]=#" & Format([txtDate], "mm/dd/yyyy") & "#"))
If Me.yourTextBoxNameToDisplayCount > 8 Then _
MsgBox "Tooo many shipments on this date"

End Sub

 

[carll]

1. On design view, set the Format
of [Operations Confirmed Date] to Short Date.

2. Remove the Expression (Control Source) from your Textbox,
The one you need to display the count.
remove this =DCount(....)

3. Add an Unbound TextBox.
Name it txtDate.

4. Set its Visible Property to No.

5. Add the following code to your
[Operations Confimed Date] Events:

Private Sub Operations_Confirmed_Date_AfterUpdate()
Me.txtDate = Me.Operations_Confirmed_Date
Call thisMsg
End Sub

Private Sub Operations_Confirmed_Date_Change()
Me.txtDate = Me.Operations_Confirmed_Date.Text
If IsDate(Me.txtDate) Then _
Call thisMsg
End Sub

Private Sub thisMsg()
' Replace Me.yourTextBoxNameToDisplayCount, with correct textboxname.
Me.yourTextBoxNameToDisplayCount = DCount("*", "[Work Orders]", IIf(IsNull([txtDate]), "(1=1)", "[Operations Confirmed Date]=#" & Format([txtDate], "mm/dd/yyyy") & "#"))
If Me.yourTextBoxNameToDisplayCount > 8 Then _
MsgBox "Tooo many shipments on this date"

End Sub

You are an absolute hero, thank you, that worked.
I did get an error, but had called my new text box "count", not a good idea. Renamed it and all fine.
Again thank you.

Carl.