Date Calculation, Source Excel
-
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 -
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 -
After using next sintaxis: Value = DateAdd("d", CInt(Field("Etiquetas Muestras PT-RAW para Bartender.Caducidad")), Date) I obtain an error message #6900. 0 -
Overflow on 'CInt' 0 -
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 -
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 -
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 -
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 -
I have blank cells could make them to cause the error? 0 -
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 -
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 -
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 -
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 -
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"))
ThenValue = 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.
ThenValue = 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 -
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 -
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 -
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 -
is there no other way for using offset date value from excel?
do we have to use a CSV? (version 10)0 -
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 -
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"))) ThenValue = DateAdd("d", CInt(Field("'Master List$'.Expiry Days")), Date)ElseValue = DateAdd("d", 0, Date)End IfCan you advice why is it so?0 -
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
0
Iniciar sesión para dejar un comentario.
Comentarios
21 comentarios