Type mismatch for date

Scott Mason

• March 08, 2019 21:19

Would anyone know why I'm getting type mismatch error when using a date field.

my_shelf = Field("ShelfLife_c")
mfg_date = Field("MfgDt") '<-- this is a date if i put it on the label it's in the format YYYY-MM-DD

if ( my_shelf = "-1" or my_shelf = "0" ) then 
  Value = "000000"

else 
  exp_date = DateAdd( "m", my_shelf, mfg_date )  '<-- this line is throwing the type mismatch error

  Value = exp_date.ToString("MMYYYY")

end if

I've tried doing cDate(Field("MfgDt") but that does nothing. It's like Bartender doesn't understand that Field("MfgDt") is a date. When I print the database field MfgDt on the label it shows 2018-05-14 so I know it's at least a date string.

Any idea how to fix this error?

 

 

0

• 

  Peter Thane

  ★ BarTender Hero ★

  • March 09, 2019 12:31

  Are you performing the VB as an Event Controlled Script? It looks like you are bringing the data in from a database field and so you need to make sure the VB routine runs after this (such as OnNewReicord, OnPostPrompt or OnIdenticalCopies.

  It looks like you will need to make the sub-type of the VB field Date and adjust the format afterwards as without this, in my testing the VB date came out as DD/MM/YYYY (my PC's standard date order)

  0
• 

  Scott Mason

  ★ BarTender Hero ★

  • March 11, 2019 13:46

  Here's what I finally did to get it working:

  sl = Field("ShelfLife_c") 
  d = Field("MfgDt")
  
  ' make sure d is a date and there is a valid shelf life
  if ( IsDate(d) and sl > 0 ) then
  
  'convert to date obj
  dob = CDate(d)
  
  ' create the expiration date by adding shelf life to the mfg date 
  d = DateAdd("m", sl , dob)
  
  'output needs to be MMDDDD, DatePart function will not zero pad so use Right to zero pad month then concatenate the year
  Value = Right("00" & DatePart("m", d), 2) & DatePart("yyyy", d)
  
  else 
  
  ' otherwise just return a string of zeros
  Value = "000000"
  
  end if

  0

Please sign in to leave a comment.