Dcount of date field (1 Viewer)

carll

New member
Local time
Today, 19:11
Joined
Dec 11, 2017
Messages
4
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

Registered User.
Local time
Today, 15:11
Joined
Oct 19, 2012
Messages
828
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:

Code:
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 ?

Code:
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

New member
Local time
Today, 19:11
Joined
Dec 11, 2017
Messages
4
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

bit cruncher
Local time
Today, 12:11
Joined
Mar 17, 2004
Messages
8,178
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...
Code:
=DCount("*","WorksOrders","OperationsConfirmedDate = Forms!WorkOrdersNew.OperationsConfirmedDate")
hth
Mark
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:11
Joined
May 7, 2009
Messages
19,169
how about:

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

Mark_

Longboard on the internet
Local time
Today, 12:11
Joined
Sep 12, 2017
Messages
2,111
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

AWF VIP
Local time
Today, 15:11
Joined
Jan 11, 2013
Messages
3,852
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

New member
Local time
Today, 19:11
Joined
Dec 11, 2017
Messages
4
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:11
Joined
May 7, 2009
Messages
19,169
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

New member
Local time
Today, 19:11
Joined
Dec 11, 2017
Messages
4
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.
 

Users who are viewing this thread

Top Bottom