Using DCount and British dates

  • Thread starter Thread starter mlm198
  • Start date Start date
M

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
 
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())");
 
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")?
Click to expand...

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
Click to expand...

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 & "#")
Click to expand...

" 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
Click to expand...

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]
 
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,
Click to expand...
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")?
Click to expand...
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
Click to expand...
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Set Db = CurrentDb
Set rs = Db.OpenRecordset("Employee")
Click to expand...
datedff = Date - 30
InYear = Mid(datedff, 9, 2) 'split date for Dcount
InMonth = Mid(datedff, 4, 3)
InDay = Mid(datedff, 1, 3)
Click to expand...
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 & "#")
Click to expand...
rs.Edit
With rs
rs![Absence30] = var
.Update
End With
rs.MoveNext
Loop
Click to expand...
rs.Close
Db.Close
Set rs = Nothing
Set Db = Nothing
Click to expand...
Me!Absence30.Requery- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

That works a treat. Thanks
 
Back
Top