Problem Adding Value To Date

Legacy Poster

• September 03, 2015 14:14

I am trying to create a label where there is an Input Prompt for the user to enter a number (of days) to alter a date by.  I need to calculate the date as:

 

Looked Up Date + Shelf Life + Alter Days (could be positive or negative)

 

As you can see from the below script (which works), I am pulling in a qty from the field called LotShelfLife and then using that in the DateAdd formula to alter the date.  I just need to be able to alter options, by doing something like this:

 

options = options + AlterDays

 

dataSource = "EPICORSQL01"
initialCatalog = "Epicor10" 
tableName = "dbo.Part" 
columnName = "LotShelfLife" 

Set objCon = CreateObject("ADODB.Connection")

objCon.ConnectionString = "Provider=SQLOLEDB;Data Source=" & dataSource & ";Initial Catalog=" & initialCatalog & ";Integrated Security=SSPI;Persist Security Info=False;"
objCon.Open

strCon = "SELECT " & columnName & " FROM " & Tablename & " WHERE PartNum = '" & Format.NamedSubStrings("EpiPartNum").Value & "'"

Set rs = CreateObject("ADODB.Recordset")
Set rs = objCon.Execute(strCon)

options=0
Do Until rs.EOF
	If Len(options)>0 Then options = options
	options = rs(columnName).value
	rs.MoveNext
Loop

objCon.Close
Set objCon = Nothing

'options = options + Format.Objects("AlterDays").Value

dteUseBy = DateAdd("d", options, Date)
strUseBy = Right("0" & Day(dteUseBy), 2) & " " & MonthName(Month(dteUseBy),true) & " " & Right(Year(dteUseBy), 2)

Value=strUseBy

 

The script fails with error:  Type mismatch when the 'options line is uncommented.

 

Please help!

Mark

0

• 

  Legacy Poster

  ★ BarTender Hero ★

  • September 03, 2015 15:07

  For the benefit of anybody reading this, I found the problem.  The script runs more than once, and the mismatch was because it was catching a blank or "" entry.

   

  My final script looked like this:

   

  dataSource = "EPICORSQL01"
  initialCatalog = "Epicor10" 
  tableName = "dbo.Part" 
  columnName = "LotShelfLife" 
  
  Set objCon = CreateObject("ADODB.Connection")
  
  objCon.ConnectionString = "Provider=SQLOLEDB;Data Source=" & dataSource & ";Initial Catalog=" & initialCatalog & ";Integrated Security=SSPI;Persist Security Info=False;"
  objCon.Open
  
  strCon = "SELECT " & columnName & " FROM " & Tablename & " WHERE PartNum = '" & Format.NamedSubStrings("EpiPartNum").Value & "'"
  
  Set rs = CreateObject("ADODB.Recordset")
  Set rs = objCon.Execute(strCon)
  
  options=0
  Do Until rs.EOF
  	If Len(options)>0 Then options = options
  	options = rs(columnName).value
  	rs.MoveNext
  Loop
  
  objCon.Close
  Set objCon = Nothing
  
  If Format.NamedSubStrings("AlterDays").Value <> "" and Format.NamedSubStrings("AlterDays").Value <> "-"  Then
  	AlteredDays = CInt(Format.NamedSubStrings("AlterDays").Value) + options
  Else
  	AlteredDays = options
  End If
  
  dteUseBy = DateAdd("d", AlteredDays, Date)
  strUseBy = Right("0" & Day(dteUseBy), 2) & " " & MonthName(Month(dteUseBy),true) & " " & Right(Year(dteUseBy), 2)
  
  Value=strUseBy
  

  0

Please sign in to leave a comment.