Date Calculation, Source Excel S’abonner

0
Avatar
Legacy Poster

Good afternoon I´m trying to configure a Label which is connected to some fields located within an excel, inside of this excel file one of the fields is the Expiry days (Named Caducidad in my attached file), I want to add this value to the current date (when label is been printed) in order to let bartender to calculate the sum of Now(date) + this Expiry days, I have been trying to seek for information and for examples of scripts to perform this task but all information I have found doesn´t work, I have tried to create a new field named F.Cad on the Label in order to use VB to perform the sum but it doesn´t works, I tried to use DateAdd funtion and although it is mentioned on the Help it always show me error messages saying that is not correct, could any of you help me?

21 commentaires

0
Avatar
Fernando Ramos Miracle
Modérateur
Actions pour les commentaires Permalien

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.

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

We are using 9.4 SR3 2781 Release, I attached an example of the label format and of the excel file in my previous comment in order to let you help me. Please let me know i f you need more information or examples.
0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

After using next sintaxis: Value = DateAdd("d", CInt(Field("Etiquetas Muestras PT-RAW para Bartender.Caducidad")), Date) I obtain an error message #6900.
0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

Overflow on 'CInt'
0
Avatar
Fernando Ramos Miracle
Modérateur
Actions pour les commentaires Permalien

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

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

I have used a file longest that I have posted (8000 lines), I created it (huge one I haven´t posted)in csv format because when I tried to use it with 8000 lines it says me that there are too many fields in bartender connection to database screen, but as you can see in the attached example there are only 9 columns and they are my fields, the difference is the lenght of the number of lines. I´m going to try the excel file with your label.
0
Avatar
Fernando Ramos Miracle
Modérateur
Actions pour les commentaires Permalien

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?

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

I have tried and it shows me the same error message that I obtain when I have tried with the bigger file (8000lines) error when connecting to database: Unknown error 0x800A0E7A.
0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

I have blank cells could make them to cause the error?
0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

The gratest value for the field you are using at the DateAdd sentence is 32. I could send you the complete excel file by email, if you want.
0
Avatar
Fernando Ramos Miracle
Modérateur
Actions pour les commentaires Permalien

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.

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

After trying to connect to the excel or the same file in csv format Bartender returns an Error Message saying Argumento o Llamada a l procedimiento no válidos: DateAdd, he usado la misma sentencia que me habíais proporcionado cambiando el valor del nombre del campo respecto de la hoja que os he enviado y sigue sin funcionar, de hecho me proporciona el mesaje de error #6900.
0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

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'

0
Avatar
Fernando Ramos Miracle
Modérateur
Actions pour les commentaires Permalien

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.

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

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

0
Avatar
Fernando Ramos Miracle
Modérateur
Actions pour les commentaires Permalien

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.

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

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

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

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

0
Avatar
Fernando Ramos Miracle
Modérateur
Actions pour les commentaires Permalien

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

0
Avatar
Legacy Poster
Actions pour les commentaires Permalien

Hi,

 

I am getting error message:

 

BarTender: Error Message #6900

<Line 1::Overflow:'CInt'>
 
on the code:
If IsNumeric(CInt(Field("'Master List$'.Expiry Days"))) Then
     Value = DateAdd("d", CInt(Field("'Master List$'.Expiry Days")), Date)   
Else
     Value = DateAdd("d", 0, Date)   
End If
 
Can you advice why is it so?
0
Avatar
Legacy Poster
Actions pour les commentaires Permalien


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

Vous devez vous connecter pour laisser un commentaire.