recordset date criteria

[Elsie]

I've a query created from "Find Unmatched Query Wizard".
In the Click event of VBA code, I want to count the number of receiving no.
in this Query with 2 criterion. One of the criterion involves Receiving date
as shown below :

Me![NoOfReceivingNo] = DCount("[ReceivingNo", "400 : tblIQCInventory Without
Matching OnHold Parts", "[PartNo] = '" & rst2!PartNo & "' AND [ReceivingDate]
= #" & rst2![ReceivingDate] & "#")

But, it always give a "0" result.
If I change the "=" sign after the [ReceivingDate] to >, the result will be
the total number of receiving no. after the Receiving date (and also
inclusive of the receiving no. in the Receiving Date). Why?

Appreciate if someone can help me to resolve this problem

 

[Alex Dybenko]

Hi,
try to format date as mm/dd/yyyy:

Me![NoOfReceivingNo] = DCount("[ReceivingNo", "400 : tblIQCInventory Without
Matching OnHold Parts", "[PartNo] = '" & rst2!PartNo & "' AND
[ReceivingDate]
= #" & format(rst2![ReceivingDate],"mm\/dd\/yyyy") & "#")

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Elsie via AccessMonster.com]

Hi, Alex

This works great !!!
Thanks a lot.

I can't find this date format "mm\/dd\/yyyy" in Access Help where the date
separator is \/.
Will there be a problem if there are 2 different dates like 12/10/2008 and
10/12/2008 ?

Hi,
try to format date as mm/dd/yyyy:

Me![NoOfReceivingNo] = DCount("[ReceivingNo", "400 : tblIQCInventory Without
Matching OnHold Parts", "[PartNo] = '" & rst2!PartNo & "' AND
[ReceivingDate]
= #" & format(rst2![ReceivingDate],"mm\/dd\/yyyy") & "#")

I've a query created from "Find Unmatched Query Wizard".
In the Click event of VBA code, I want to count the number of receiving

[quoted text clipped - 16 lines]

Appreciate if someone can help me to resolve this problem

 

[Douglas J. Steele]

That's a back slash followed by a forward slash: \ /. Backslash is an escape
character, so whatever follows it is show literally. If you don't use it,
the forward slash in mm/dd/yyyy will actually be replaced by whatever
character's been defined as the date separator character to the operating
system through Regional Settings.

12/10/2008 will ALWAYS be interpretted as 10 Dec, 2008, 10/12/2008 will
ALWAYS be interpretted as 12 Oct, 2008.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi, Alex

This works great !!!
Thanks a lot.

I can't find this date format "mm\/dd\/yyyy" in Access Help where the date
separator is \/.
Will there be a problem if there are 2 different dates like 12/10/2008 and
10/12/2008 ?

Hi,
try to format date as mm/dd/yyyy:

Me![NoOfReceivingNo] = DCount("[ReceivingNo", "400 : tblIQCInventory
Without
Matching OnHold Parts", "[PartNo] = '" & rst2!PartNo & "' AND
[ReceivingDate]
= #" & format(rst2![ReceivingDate],"mm\/dd\/yyyy") & "#")

I've a query created from "Find Unmatched Query Wizard".
In the Click event of VBA code, I want to count the number of receiving

[quoted text clipped - 16 lines]

Appreciate if someone can help me to resolve this problem