Date Calculation, Source Excel S’abonner

Hello Juan Pedro,

1. Could you let me know what BarTender version are you working with? Note that on version 10 we've integrated this behaviour directly on BarTender (set a date offset depending on the value of a data base field).

2. In any case the DateAdd() function should certainly work. If that is not the case it probably is because the data type coming from your database is not the correct one. Try applying the DateAdd() function in the following way:

DateAdd("d",  CInt(Field("<FieldName>")), Date)

- The ["d"] value marking that we are adding "days" needs to be between quotes as the function is expecting a string.

- I'm using the CInt() to force whatever you've got on your database to be converted to an Integer value. Note that the DateAdd() function is expecting a numeric value on the second function field.

Finally, please note that in order to apply the above code for each of your database records you need to create an event based VB script and enter the function in the "OnNewRecord" event.

3. If you are still not able to make it work, please attach your document so I can take a look at what might be going on.

Please note that Excel is not a database, but a spreadsheet and tends to display this kind of issues (among others) when being used as a database. If you export the Excel file to a .CSV file instead and re-establish the database connection (as a text file this time) do you still get the same error?

*I've tested your issue with a very simple document using your attached Excel file and the script didn't offer any sort of error. Then again it's probably the case that this test Excel file is smaller than your production one. In any case, and for your reference,  I've attache my example document; to make it work save your Excel file to the "C:\Seagull" folder.

Another thing you could try is to copy all the valid information from your Excel spreadsheet and paste it into a brand new one. This usually helps purge any unwanted behavior from Excel (at least unwanted when used as a database).

If when connecting your original Excel sheet (the big one with 8000 records) to BarTender it displays an error is probably the case that there are extra empty fields or records that are generating an error (8000 records shouldn't cause BarTender to tell you there are too many).

Copy/pasting all these valid records and fields (not the entire sheet) on a brand new sheet should help "purge" any extra fields and records that Excel has created.

Also, could you let me know what's the biggest value that the "CInt()" command needs to deal with?

Yes, please send us your Excel file and BarTender document to our Tech support email:

http://www.bartenderbarcodesoftware.com/label-software/technical-support.aspx

Make sure to reference this forum question and to add your support number (form the "Help>About..." dialog) to your email.

Cheers.

Please note that Excel is not a database, but a spreadsheet and tends to display this kind of issues (among others) when being used as a database. If you export the Excel file to a .CSV file instead and re-establish the database connection (as a text file this time) do you still get the same error?

 

*I've tested your issue with a very simple document using your attached Excel file and the script didn't offer any sort of error. Then again it's probably the case that this test Excel file is smaller than your production one. In any case, and for your reference,  I've attache my example document; to make it work save your Excel file to the "C:\Seagull" folder.

 

Another thing you could try is to copy all the valid information from your Excel spreadsheet and paste it into a brand new one. This usually helps purge any unwanted behavior from Excel (at least unwanted when used as a database).

I tried this sample file and I am getting an error #3904 Invalid procedure call or argument: 'DataAdd'

1. If you are getting that error message then it's probably the case that the data on your database field used in the VB script doesn't have the correct data type.. Note that in order for the example to work you need to make sure that data coming from your database is a "numeric" value. You might want to force this by using a conversion method such as CInt()

DateAdd("d", CInt(Field("DatabaseField")), Date)

2. Another possibility to consider is if your database has empty values in some records (or even characters instead of numbers), if that is the case, you will need to add a conditional structure (If... Then... Else) to avoid the error caused by this empty record.

Something like the below should do: 

If IsNumeric(Field("DatabaseField"))
Then

     Value = DateAdd("d", Field("DatabaseField"), Date)

Else

     Value = DateAdd("d", 0, Date)

End If

3. If you combine my two suggestions the code would result in the following:

If IsNumeric(CInt(Field("DatabaseField")))   'evaluates if the database field has a valid numeric value for the date offset.
Then

     Value = DateAdd("d", CInt(Field("DatabaseField")), Date)   'numeric value in database field, offset over the system date is calculated.

Else

     Value = DateAdd("d", 0, Date)   'wrong data, no offset occurs.

End If

Please test it and let me know how you get on.

On #1 Ienterd

Value = DateAdd("d", CInt(Field("test.Sell by")), Date)

And I am getting Error # 6900 OnNewRecord(Line 3): : Overflow: 'CInt'

On #2 I enterd

If IsNumeric(Field("test.Sell by"))

Then
Value = DateAdd("d", Field("test.Sell by"), Date)

Else

Value = DateAdd("d", 0, Date)

End If

And I am getting Error # 6900 OnNewRecord(Line 3): If IsNumeric(Field("test.Sell by")): Expected 'Then'

On #3 I enterd

If IsNumeric(CInt(Field("test.Sell by")))

Then

Value = DateAdd("d", CInt(Field("test.Sell by")), Date)

Else

Value = DateAdd("d", 0, Date)

End If

I am getting Error # 6900 OnNewRecord(Line 3): If IsNumeric(CInt(Field("test.Sell by"))): Expected 'Then'

I attached the database and btw file 

Let me know

Thanks

Hello Ely,

1. The reason you are getting this error at design time is because you are entering the code in the "OnNewRecord" event, but at the time you click OK or "Test" no record is available to test with, so the CInt() function doesn't have any data to work with.

Please ignore the error message and test doing a print preview. Do you still get an error? Does the offset work?

2. Also, I would like to correct the code in my last post, the "Then" reserved word should be in the same line as the "If" word. Below you'll find the corrected script:

If IsNumeric(CInt(Field("DatabaseField"))) Then  'evaluates if the database field has a valid numeric value for the date offset.

     Value = DateAdd("d", CInt(Field("DatabaseField")), Date)   'numeric value in database field, offset over the system date is calculated.

Else

     Value = DateAdd("d", 0, Date)   'wrong data, no offset occurs.

End If

*Note that you will also get the CInt() error when clicking on the "OK" button after entering the script. Please test doing a print preview to see if you still get the error.

No it is not working the first one from the sample file is working but I am getting the error I disable the warning so it is not popping up 

Thanks anyway

is there no other way for using offset date value from excel?
do we have to use a CSV? (version 10)

Hello,

No it is not working the first one from the sample file is working but I am getting the error I disable the warning so it is not popping up 

 

Thanks anyway

I don't fully understand what you are saying here, as I explained before the error you get at design time is expected, as the database reference at the time is empty. To make sure if it works or not you should ignore the error message and try doing a print preview.

*If you wish to avoid the error message you could specify a "default" value for the field reference:

Field("DatabaseField". "1")

In the above example the field value will take "1" by default, until the actual data from the database is sourced.

is there no other way for using offset date value from excel?
do we have to use a CSV? (version 10)

The database type in use is actually irrelevant, you only need to make sure that the field you are working with has the appropriate data type. For example, if a field containing a character such as "b" is used with a CInt() function you will surely get a type mismatch error (a "b" cannot be converted to an Integer value).

Regards

Hi,

I am getting error message:

BarTender: Error Message #6900

Hello,

I don't fully understand what you are saying here, as I explained before the error you get at design time is expected, as the database reference at the time is empty. To make sure if it works or not you should ignore the error message and try doing a print preview.

*If you wish to avoid the error message you could specify a "default" value for the field reference:

Field("DatabaseField". "1")

In the above example the field value will take "1" by default, until the actual data from the database is sourced.

The database type in use is actually irrelevant, you only need to make sure that the field you are working with has the appropriate data type. For example, if a field containing a character such as "b" is used with a CInt() function you will surely get a type mismatch error (a "b" cannot be converted to an Integer value).

Regards