CODEWITHSUNDEEP
datetimems-accessvba
msim
msim

Reputation: 373

Access VBA Converting DateTime to Short Date in DCount

I am using Access 2007.

I need to convert a the value of a field that is full date and time to just the date part, so that I can see if it is a match in a dcount for today's date. I have tried DateValue(), Int(), etc and I think I did them right (there's a possibility I didn't). It doesn't seem to work.

Here's my code WITHOUT any of the things I've tried, so as not to confuse. You can see here that the [tsUpdated] value and the CurDate value will not match because [tsUpdated] is the full date and CurDate is just the date. How do I get [tsUpdated] to be formatted as just the date in the DCount...and have it work?

GetID = Forms!frm_MainMenu!AssocIDBox
CurRecord = Forms!frm_EC_All![L#].Value
CurDate = DateValue(Now)
'
Dim tCompleted As String: tComp = DCount("[EC#]", "tbl_Data", "[AssocID] = " & GetID & " AND [tsUpdated] = " & CurDate)
'
MsgBox "You completed " & tComp & " today.", vbOKOnly, "Summary"
'
End Sub

Upvotes: 2

Views: 2388

Answers (2)

HansUp
HansUp

Reputation: 97131

DateValue(Now) returns the same Date/Time value as does Date(). So instead of CurDate = DateValue(Now), you could do CurDate = Date. However, I don't see any benefit from saving the value in that CurDate variable; just use Date() when you need it.

A greater concern is "Converting DateTime to Short Date in DCount". That means the db engine must convert the tsUpdated value from every candidate row of tbl_Data to determine which rows satisfy your WHERE clause criteria. And that means the engine can not take advantage of an index on tsUpdated to quickly identify candidate rows. So don't do that.

An approach like this could be much faster with tsUpdated indexed:

DCount("[EC#]", "tbl_Data", "[AssocID] = " & GetID & " AND [tsUpdated] >= Date AND [tsUpdated] < (Date + 1)")

Upvotes: 4

AxGryndr
AxGryndr

Reputation: 2344

My suggestion would be to use 

Format([tsUpdated],"mm/dd/yyyy")

and create a query that converts tsUpdated to a ShortDate. For example:

 SELECT 
    [Field1], 
    [Field2],
    Format([tsUpdated],"mm/dd/yyyy") AS tsUpdated_S
 FROM
    tbl_Data

Save this and adjust your DCount to use this query instead of the table.

Upvotes: 0

Related Questions