Co-workers computer doesn't recognize date in script

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marco Brilo - 29 Jan 2007 14:20 GMT

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"
.
.
.
Else
rstmm.Fields("BIOL_SEASON").Value = "Error: Date beyond range of
script"

End If
Else
rstmm.Fields("BIOL_SEASON").Value = "Date missing - enter in a valid
date"
End If

rstmm.MoveNext

Next i

rstmm.Close

End Sub

Curiously the code
when I debug the code I check the varibales and the strDay is fine and
everything looks ok but it will incorrectly recognize the date as being
within a range it's not supposed to be. After a lot of fiddling with the
code I found that the following code works fine on her computer:

If strDay > DateValue("28-Nov-1981") And strDay < DateValue("27-Apr-1982")
Then
rstmm.Fields("BIOL_SEASON").Value = "Wintering"
ElseIf strDay > DateValue("28-Nov-1982") And strDay <
DateValue("27-Apr-1983") Then
rstmm.Fields("BIOL_SEASON").Value = "Wintering"
ElseIf strDay > DateValue("28-Nov-1983") And strDay <
DateValue("27-Apr-1984") Then
rstmm.Fields("BIOL_SEASON").Value = "Wintering"

The most perplexing thing about this all is that the first set of code works
fine when I run it from my computer but not hers. We are running the code in
a database that's kept on a network drive and we both have Access2003. I
went into options and both of us have unchecked the 'Use 4 digit year'
options, but my feeling is that there's probably some other setting in Access
that is creating this issue.
Any ideas?
Thanks in advance.
Marco

Reply to this Message

missinglinq - 29 Jan 2007 14:44 GMT

Are the time/country settings for both computers the same?

Signature

There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Reply to this Message

Marco Brilo - 29 Jan 2007 15:46 GMT

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

> Are the time/country settings for both computers the same?

Reply to this Message

Douglas J. Steele - 29 Jan 2007 15:07 GMT

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?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi all,
> I have a script that assigns a biological season to each record in a
[quoted text clipped - 76 lines]
> Thanks in advance.
> Marco

Reply to this Message

Marco Brilo - 29 Jan 2007 15:53 GMT

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

> 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
[quoted text clipped - 80 lines]
> > Thanks in advance.
> > Marco

Reply to this Message

Douglas J. Steele - 29 Jan 2007 16:18 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi Doug,
> I'm not sure I'm following you here. The strDay is a date variable so you
[quoted text clipped - 103 lines]
>> > Thanks in advance.
>> > Marco

Reply to this Message