Using DCount and British dates

[mlm198]

Hi,

I have two tables, the main table which contains a list of names and
total absences and the other which contains some names and absence
dates. What I want to do is automatically count the number of absences
there were for each name in the past 30 days. I have the code as
below. The problem is my dates in the absence table are in a British
format (and I want to keep British) but DCount only seems to work with
American dates. Is there anyway I can get DCount to work with british
dates or convert the absence table using format e.g. something like
format([absence], "mm/dd/yy")?

Any help will be greatful.

Matt

Dim var As Integer
Dim namest As String
Dim datedff As Date
Dim InYear As String
Dim InMonth As String
Dim InDay As String
Dim datedff2 As String

Dim Db As DAO.Database
Dim rs As DAO.Recordset
Set Db = CurrentDb
Set rs = Db.OpenRecordset("Employee")


datedff = Date - 30
InYear = Mid(datedff, 9, 2) 'split date for Dcount
InMonth = Mid(datedff, 4, 3)
InDay = Mid(datedff, 1, 3)

datedff2 = InMonth & InDay & InYear 'note date is American format
tets = datedff2
rs.MoveFirst
Do While Not rs.EOF
namest = rs![Name]
var = DCount("[AutoID]", "[absence]", _
"[Name] = '" & namest & _
"' AND [AdEndDate] > #" & datedff2 & "#")

rs.Edit
With rs
rs![Absence30] = var
.Update
End With
rs.MoveNext
Loop

rs.Close
Db.Close
Set rs = Nothing
Set Db = Nothing

Me!Absence30.Requery

 

[Terry Kreft]

The following SQL update should do the same thing as you are trying to do
below

UPDATE Employee
SET Employee.Absence30 = DCount("AutoID","absence","Name = '" &
[Employee].[Name] & "' AND AdEndDate > DateAdd(""d"", -30, Date())");

 

[John W. Vinson]

Hi,

I have two tables, the main table which contains a list of names and
total absences and the other which contains some names and absence
dates. What I want to do is automatically count the number of absences
there were for each name in the past 30 days. I have the code as
below. The problem is my dates in the absence table are in a British
format (and I want to keep British) but DCount only seems to work with
American dates. Is there anyway I can get DCount to work with british
dates or convert the absence table using format e.g. something like
format([absence], "mm/dd/yy")?

Don't confuse data STORAGE with data DISPLAY. Your dates are not
stored in British format, or in American format either - a Date/Time
value is a Double Float number, a count of days and fractions of a day
since midnight, December 30, 1899. This number can be displayed any
way you like by setting the Format property of form or report
controls.

Comments inline.

Any help will be greatful.

Matt

Dim var As Integer
Dim namest As String
Dim datedff As Date
Dim InYear As String
Dim InMonth As String
Dim InDay As String
Dim datedff2 As String

Dim Db As DAO.Database
Dim rs As DAO.Recordset
Set Db = CurrentDb
Set rs = Db.OpenRecordset("Employee")


datedff = Date - 30
InYear = Mid(datedff, 9, 2) 'split date for Dcount
InMonth = Mid(datedff, 4, 3)
InDay = Mid(datedff, 1, 3)

datedff2 = InMonth & InDay & InYear 'note date is American format

Ummm...!?

Use a Date type Variant, not a string; and use

DateAdd("m", -1, Date())

to calculate a Date/Time value one month ago. You don't need InYear,
InMonth etc. - which won't work right anyway since a date IS NOT A
STRING.

tets = datedff2
rs.MoveFirst
Do While Not rs.EOF
namest = rs![Name]
var = DCount("[AutoID]", "[absence]", _
"[Name] = '" & namest & _
"' AND [AdEndDate] > #" & datedff2 & "#")

" AND [AdEndDate] > #" & Format(datedff2, "mm/dd/yyyy") & "#"

will work fine without needing to parse strings.

rs.Edit
With rs
rs![Absence30] = var
.Update
End With
rs.MoveNext
Loop

I'd REALLY recommend *NOT* storing the count of absences, *in any
table*. Just calculate it on demand. You're making this much more
difficult than it really is!

John W. Vinson [MVP]

 

[mlm198]

The following SQL update should do the same thing as you are trying to do
below

UPDATE Employee
SET Employee.Absence30 = DCount("AutoID","absence","Name = '" &
[Employee].[Name] & "' AND AdEndDate > DateAdd(""d"", -30, Date())");

--

Terry Kreft

Hi,

I have two tables, the main table which contains a list of names and
total absences and the other which contains some names and absence
dates. What I want to do is automatically count the number of absences
there were for each name in the past 30 days. I have the code as
below. The problem is my dates in the absence table are in a British
format (and I want to keep British) but DCount only seems to work with
American dates. Is there anyway I can get DCount to work with british
dates or convert the absence table using format e.g. something like
format([absence], "mm/dd/yy")?

Any help will be greatful.

Dim var As Integer
Dim namest As String
Dim datedff As Date
Dim InYear As String
Dim InMonth As String
Dim InDay As String
Dim datedff2 As String

Dim Db As DAO.Database
Dim rs As DAO.Recordset
Set Db = CurrentDb
Set rs = Db.OpenRecordset("Employee")

datedff = Date - 30
InYear = Mid(datedff, 9, 2) 'split date for Dcount
InMonth = Mid(datedff, 4, 3)
InDay = Mid(datedff, 1, 3)

datedff2 = InMonth & InDay & InYear 'note date is American format
tets = datedff2
rs.MoveFirst
Do While Not rs.EOF
namest = rs![Name]
var = DCount("[AutoID]", "[absence]", _
"[Name] = '" & namest & _
"' AND [AdEndDate] > #" & datedff2 & "#")

rs.Edit
With rs
rs![Absence30] = var
.Update
End With
rs.MoveNext
Loop

rs.Close
Db.Close
Set rs = Nothing
Set Db = Nothing

Me!Absence30.Requery- Hide quoted text -

- Show quoted text -

That works a treat. Thanks