VBS Syntax error

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"

Is the date in 1972 on line 24 relevant?

Me wrong person to comment… but can you define a defined case ?
should that not be a predefined variable of a list.

Looks like your trying to get it to determin its own value

Where-as I’d would have thought it would worked out and value assigned before the case is made.

Unsure if i’m making sense here… but like a if statement before your select to say if date-360 < ? than assign value or case name

than case that value name then underlying logic ?

I would have thought you would need to use dateserial to do such a calculation, essentially date - 365 does not know what the 365 corresponds to.

I’m a little rusty now, but I think it should look something like

Case IS < DateSerial(year(now)-1,month(now),day(now))

hth on the road to right :slight_smile: it may just work using

CASE IS < (date(now) -365)

date needs passing a value.

DT.

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?..

Thanks for your help btw

have you tried just Case Is < (Date() - 365) ?

damn weird not knowing this off top of head, flipping new job means I’ve not touched vb or vbs for nearly 6 months now.

DT.

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.

http://www.tizag.com/vbscriptTutorial/vbscriptselectcase.php

clunky but…

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 :slight_smile: