Problem Adding Value To Date 追蹤

0
Avatar
Legacy Poster

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

1 意見

0
Avatar
Legacy Poster
評論操作 永久連結

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

登入寫評論。