Import routine

[Dachande11]

Hi, I have a piece of code that is supposed to check a table for the last date in it and if it is not yesterdays (or fridays date if opening on a monday) then it is supposed to import a text file. The problem is it is not recognising the last date and so just keeps importing each time.
Here is the code to check dates, can anybody help?

Private Sub Form_Load()

DayNum = "w"
If DatePart(DayNum, Date) = 2 Then
Lastdate = Date - 3
Else
Lastdate = Date - 1
End If

'Confirms import data in CommDB for last commissionable date
GotData = DLast("[Transaction_Date]", "Slowstk")

'Checks to see if import routine is needed
If GotData = Lastdate Then
DoCmd.Close
DoCmd.OpenForm "Main Menu", acNormal, "", "", acReadOnly, acNormal
Else
'Runs import routine

DoCmd.Hourglass True
DoCmd.SetWarnings False
DoCmd.TransferText acImportDelim, "Slowstk Import Specification", "Slowstk", "K:\3ex\comm\Slowstk.txt", False, "", 850




DoCmd.SetWarnings True
DoCmd.Hourglass False
DoCmd.Close
DoCmd.OpenForm "Main Menu", acNormal, "", "", acReadOnly, acNormal
End If

Exit_DecisionBox:
Exit Sub
End Sub

 

[David Fletcher]

Using DatePart "w" returns the day of the week as a value from 1-7 Sunday, Monday thru Saturday. In your logic, you do not establish what today is. The first thing you need to say is

MyToday = DatePart("w",MyDate)

Then establish what yesterday is

Select Case MyToday
Case 1,2,7 'Sunday, Monday & Saturday
MyYesterday = 6 'Friday
Case Else
MyYesterday = MyToday - 1
EndSelect

You see? I haven't tested this but I think it will work - anyway you should be able to see the flaw in your logic. Hope this solves it for you. Cheers. DavidF.

BTW - avoid using variable names which are the same as Access keywords - eg Date - this will give you grief - guaranteed. :-(

[This message has been edited by David Fletcher (edited 05-11-2001).]

 

[Dachande11]

Thanks David for your help