DCount Date Problem

D

DS

I keep getting Type Mismatch.....its gotta be the date!
Any help as always appreciated.
Thanks
DS

Me.TxtCount = Nz(DCount("[PaymentID]", "PayApplied", "PayApplied.PayDate
= Date(Now())" And "PayApplied.SalesID = " & Me.TxtSalesID & ""))
If IsNull(Me.TxtCount) Or _
 
J

Jeff Boyce

Why bother trying to "calculate" Date(Now()), if you really only need
today's date? Instead, just use Date().

You might need to hang delimiters around that date -- Access uses the pound
sign ("#") to delimit date values. If you want to find a PayDate of today,
you're working with date values. Perhaps something like:

... "PayApplied.PayDate = #" & Date() & "# And ..."

(You don't say if your PayApplied.PayDate field is of a date/time data type
or not.)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
M

Marshall Barton

DS said:
I keep getting Type Mismatch.....its gotta be the date!
Any help as always appreciated.
Thanks
DS

Me.TxtCount = Nz(DCount("[PaymentID]", "PayApplied", "PayApplied.PayDate
= Date(Now())" And "PayApplied.SalesID = " & Me.TxtSalesID & ""))
If IsNull(Me.TxtCount) Or _
Click to expand...


It's probably because the quotes are out of whack.

Assuming that PayDate is a date type field:

Me.TxtCount = Nz(DCount("[PaymentID]", "PayApplied",
"PayDate >= Date() And SalesID = " & Me.TxtSalesID), 0)
 
G

Graham Mandeno

Hi DS

There are a few problems here:

First, you use either Date() [current date] or Now() [current date and time]
but not both.

Second, your And should be inside the string, not between the two strings (I
suspect this is what is giving the error).

Third, the last [& ""] does nothing as it is simply appending an empty
string to what you already have.

Try this:
Me.TxtCount = Nz(DCount("[PaymentID]", "PayApplied", _
"PayApplied.PayDate = Date() And PayApplied.SalesID = " _
& Me.TxtSalesID), 0)

The last [, 0] is not strictly necessary but it ensures Nz returns a zero if
it processes a Null.

This means that the next line should be:
If Me.txtCount = 0 then ....
 
J

John Vinson

I keep getting Type Mismatch.....its gotta be the date!
Any help as always appreciated.
Thanks
DS

Me.TxtCount = Nz(DCount("[PaymentID]", "PayApplied", "PayApplied.PayDate
= Date(Now())" And "PayApplied.SalesID = " & Me.TxtSalesID & ""))
If IsNull(Me.TxtCount) Or _
Click to expand...

You're mixing things up with the Date function. You don't need to -
and cannot! - pass it an argument, much less passing the Now()
argument.

To get today's date, use

Date()

To get the current date and time, accurate to the second, use

Now()

In neither case does the function take any argument.

John W. Vinson[MVP]
 
D

DS

Graham said:
Hi DS

There are a few problems here:

First, you use either Date() [current date] or Now() [current date and time]
but not both.

Second, your And should be inside the string, not between the two strings (I
suspect this is what is giving the error).

Third, the last [& ""] does nothing as it is simply appending an empty
string to what you already have.

Try this:
Me.TxtCount = Nz(DCount("[PaymentID]", "PayApplied", _
"PayApplied.PayDate = Date() And PayApplied.SalesID = " _
& Me.TxtSalesID), 0)

The last [, 0] is not strictly necessary but it ensures Nz returns a zero if
it processes a Null.

This means that the next line should be:
If Me.txtCount = 0 then ....
Click to expand...
That works! Thank you everyone for your help. Graham,John,Jeff and
Marshall...Your help is always appreciated!
Thanks
DS
 
Top