DATE HELP

[Alan]

Hello All

Sorry but really struggling with this Date Query

I have a table that holds Dates and times (General Date Format) of various
daily server logs that I import to an Access Database. Each log is date
stamped and this table provides the last date of import to allow the next
file to be selected.

I also have a table that after importing the logs then takes a number of
counts from the logs which it updates in this table against a date value
(Primary Key of table)
My probelm is with the lookup and access to the date.
I have used a recordset to access the table as below, but the issue appears
to be with how the date is being formatted after it leaves the vba statement.

As can be seen below I have tried reformating the date value at the
beginningbut to no avail. I have also tried removing the format in the Set
Recordset SQL

Public Sub getcount()

Dim rstDateUpdate As Recordset
Dim rstTestCount As Recordset
Dim dtSrvrDate As Date

Set rstDateUpdate = CurrentDb.OpenRecordset("SELECT * FROM tblServerImports
WHERE (tblServerImports.ServerName)='TestServer'")
With rstDateUpdate
' dtSrvrDate = Format(Left(.Fields("LatestFiledate"), 10), "mm\/dd\/yyyy")
dtSrvrDate = Left(.Fields("LatestFiledate"), 10)
' dtSrvrDate = Int(.Fields("LatestFiledate"))
End With

Set rstTestCount = CurrentDb.OpenRecordset("SELECT tblServerCounts.* FROM
tblServerCounts WHERE (((tblServerCounts.Date)=#" & Format(dtSrvrDate,
"dd\/mm\/yyyy") & "#))", dbOpenDynaset)
With rstTestCount
MsgBox rstTestCount.RecordCount
End With
End Sub

There is a record for the date being searched therfore count should be 1 but
returns 0 (this is also the trigger in the main code to create a new entry if
no date exists)

Any Help would be gratefully appreciated

Kind Regards

 

[John Spencer]

In queries you must use dates in the form mm/dd/yyyy or yyyy/mm/dd.
For an explanation see
International Dates in Access at:
http://allenbrowne.com/ser-36.html

You might try using the DCount function instead.

If DCount("*","tblServerCounts","[Date]=" &
Format(dtSrvrDate,"\#yyyy-mm-dd\#")) = 0 Then

'Do stuff

End IF

By the way, Date is a bad name for a field. Date() is a function that returns
the current system date. The two can get confused. Fpr instance, if I had
written
DCount("*","tblServerCounts","Date=" & Format(dtSrvrDate,"\#yyyy-mm-dd\#"))

The comparison might well have used the FUNCTION Date instead of the value of
the FIELD named Date.

A better name would be ActionDate or fldDate or something that specifies what
kind of date this is such as HireDate, TerminationDate, AdmissionDate, etc.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Clifford Bass via AccessMonster.com]

Hi Alan,

Try:

dtSrvrDate = DateValue(.Fields("LatestFiledate"))

And:

...WHERE [Date] = #" & Format(dtSrvrDate, "yyyy-mm-dd") & "#".....

Please note that the use of "Date" for a field name is a poor choice as
it can easily get confused with the Date() function. Do a search for
"reserved words" in your on-line help in Access to get a list of words that
should not be used for table, field and variable names.

The DateValue() function will provide you with just the date portion of
the LatestFiledate field. When constructing date conditions in a SQL string,
it is best to use the universal yyyy-mm-dd format that will not be affected
by a user's computer's regional settings.

Hope that helps,

Clifford Bass