Co-workers computer doesn't recognize date in script

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I have a script that assigns a biological season to each record in a Caribou
database based on the date of it's sattelite transmission. It works just
fine on my computer but for some reason when my co-worker run's it from her
computer some of the records consistently get assigned the wrong season. I
have pasted the nuts and bolts of the code below:

Private Sub ADDBIOL_SEASON()
Dim cnnMM As ADODB.Connection, rstmm As ADODB.Recordset
Dim strDay As Date
Set cnnMM = Application.CurrentProject.Connection
Set rstmm = New ADODB.Recordset
rstmm.Open Source:="Sheet1", ActiveConnection:=cnnMM,
CursorType:=adOpenStatic, LockType:=adLockPessimistic
numrec = rstmm.RecordCount
rstmm.MoveFirst

For i = 0 To (numrec - 1)

If Not rstmm.Fields("Date").Value Then
strDay = rstmm.Fields("Date").Value

If strDay > "28-Nov-1981" And strDay < "27-Apr-1982" Then
rstmm.Fields("BIOL_SEASON").Value = "Wintering"
ElseIf strDay > "28-Nov-1982" And strDay < "27-Apr-1983" Then
rstmm.Fields("BIOL_SEASON").Value = "Wintering"
 
Rather than relying on the date value be formatted in a specific way, why
not store the value in a Date variable, and then compare it to
#28-Nov-1981#, #27-Apr-1982# and so on?
 
Do you mean under 'Date and Time' in Control Panel? I don't know where else?
She's left for a meeting now but will check when she gets back.
thanks
M
 
Hi Doug,
I'm not sure I'm following you here. The strDay is a date variable so you
must mean the comparison dates. i.e. 28-Nov-1981 and 27-Apr-1982 in which
case I would need a lot of variables since I have many many date ranges to
compare with (I've only shown a small portion of the code)
Another way I suppose I could write the code is to covert the date into Day
of Year, i.e. Jan 1 of any year is 1, and Dec 31 is 364, except leap years it
would be 365. Do you know of any function in Access that will accomplish
that? Once converted to day of year I could simply compare it as a number.

Thanks
M
 
Sorry: I missed the declaration line. (the str prefix for a variable
usually means it's been declared as String: dtm is the normal prefix for
Date variables).

However, you're currently comparing the date variable to strings:

If strDay > "28-Nov-1981" And strDay < "27-Apr-1982" Then

Change that to use Date constants, not String constants:

If strDay > #28-Nov-1981# And strDay < #27-Apr-1982# Then
 
Back
Top