can someone have a look at this and tell me why I get a syntax error on line 26
On Error Resume Next
MsgBox "start"
Set WshShell = Wscript.CreateObject("Wscript.Shell")
UserName = WshShell.ExpandEnvironmentStrings("%USERNAME%")
Dim CN_SyOps, Signdate, rscategory, Username1
Set CN_SyOps = CreateObject("ADODB.Connection")
CN_SyOps.ConnectionString = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=Z:\SyOps.mdb"
CN_SyOps.Open
Set rsCAtegory = CN_SyOps.Execute("SELECT [UserName] FROM [SyOps] WHERE [UserName] = '" & UserName & "'")
UserName1 = rsCategory.GetString
If UserName1 = "" Then
MsgBox "Your name is not in the SyOps Database" & vbcrlf & "you need to phisicaly sign SyOps to be enterd into the database" & vbcrlf & "Please contact the C4I Helpdesk x7498" & vbcrlf & vbcrlf & "A record has been made of this Error.",16, "ERROR"
CN_SyOps.Execute("INSERT INTO SyOps (UserName, SignDate) VALUES ('" & UserName & "','13/07/1972')")
Else
Set rsCAtegory = CN_SyOps.Execute("SELECT [SignDate] FROM [SyOps] WHERE [UserName] = '" & UserName & "'")
Signdate = rsCategory.GetString
Select Case cdate(Signdate)
Case "13/07/1972"
MsgBox "Your name is still not in the SyOps Database" & vbcrlf & "you need to phisicaly sign SyOps to be enterd into the database" & vbcrlf & "Please contact the C4I Helpdesk x7498" & vbcrlf & vbcrlf & "A record has been made of this Error.",16, "**Error**"
Case IS < (Date-365)
msgbox "Your Sy-Ops Digital signiture is out of Date." & vbcrlf & "You last signed on " & SignDate & "You need to sign it now."
commandline = "CONNet_SyOPs.hta"
wshshell.run commandline , 0 , True
CN_SyOps.Execute("UPDATE [SyOps] SET SignDate = '" & Date & "' WHERE [UserName] = '" & UserName & "'")
End select
End If
CN_SyOps.Close
MsgBox "end"
Driod, the date in 1972 is just a semi random date, it’s a figure that will only appear in that instance.
PMM, I’ve tried variants on that but come accross the same problem again and again
DT, date is a function within vbs and gives todays date, -365 works to delete 365 days from it as I tested it seperatly, I’ve tried forcing it to a date datatype using ‘CDate(date)’ but it’s exactly the same. the ‘case is’ statement works fine in vba, but not vbs, It needs to be in vbs as this is going to be in our logon script which is vbs.
I wonder if I can get it to do the math in the database query?..
Just had a quick look and it looks like select case isn’t as useful in VBS and can’t do less than/more than type comparisons. You’ll have to use if…elseif instead.
On Error Resume Next
Dim Signdate
Signdate = "13/07/1973"
Select Case cdate(Signdate)
Case "13/07/1972":
MsgBox "Your name is still not in the SyOps Database" & vbcrlf & "you need to phisicaly sign SyOps to be enterd into the database" & vbcrlf & "Please contact the C4I Helpdesk x7498" & vbcrlf & vbcrlf & "A record has been made of this Error.",16, "**Error**"
Case cdate(Signdate) < (Date(now())-365):
msgbox "Your Sy-Ops Digital signiture is out of Date." & vbcrlf & "You last signed on " & SignDate & "You need to sign it now."
case else : msgbox "idunno"
End select
If rsCAtegory.Recordset.Count < 1 Then
MsgBox “Your name is still not in the SyOps Database” & vbcrlf & “you need to phisicaly sign SyOps to be enterd into the database” & vbcrlf & “Please contact the C4I Helpdesk x7498” & vbcrlf & vbcrlf & “A record has been made of this Error.”,16, “Error”
elseif
rsCategory(0) < (Date(Now())-365) then
msgbox “Your Sy-Ops Digital signiture is out of Date.” & vbcrlf & "You last signed on " & SignDate & “You need to sign it now.”
commandline = “CONNet_SyOPs.hta”
wshshell.run commandline , 0 , True
CN_SyOps.Execute(“UPDATE [SyOps] SET SignDate = '” & Date & “’ WHERE [UserName] = '” & UserName & “’”)
End if
Not sure but the Record Count sure would make it faster then stepping thru the Case. Seems if the count is 0 then just jump to message.
Cheers all, I got it all sorted, but ended up adding a check column to the table and using that to see what was set, I couldn’t get it all to work, it was either not happy as it was a number or not happy as it was a date, either which way it’s sorted now, it’s go’s live as part of our login script on Monday