If date is in range then

What is probably incredibly easy to do, I am having problems with. All I want to do is to pick up whether or not a date is in a specific range, and if it is then output the end of the month date.

here is the code I started:

if {ar_invoices.arih_invoice_date} >= Date(01/01/2005) and {ar_invoices.arih_invoice_date} <= date(01/31/2005)
then
"01/31/2005"

else
if {ar_invoices.arih_invoice_date} >= date(02/01/2005) and {ar_invoices.arih_invoice_date} <= date(01/28/2005)
then
"02/28/2005"

else
if {ar_invoices.arih_invoice_date} >= date(03/01/2005) and {ar_invoices.arih_invoice_date} <= date(03/31/2005)
then
"03/30/2005"

else
if {ar_invoices.arih_invoice_date} >= date(04/01/2005)
then
"Has not been invoiced yet"

I will eventually continue the code, but want to make sure it works first

Let me know if you need more clarification!

Thank you so much!Looks like the date format was incorrect.

for those who may be stuck on the same thing here's the code:

if {ar_invoices.arih_invoice_date} >= Date(2005,1,1) and {ar_invoices.arih_invoice_date} <= date(2005,1,31)
then
"01/31/2005"

else
if {ar_invoices.arih_invoice_date} >= date(2005,2,1) and {ar_invoices.arih_invoice_date} <= date(2005,2,28)
then
"02/28/2005"

else
if {ar_invoices.arih_invoice_date} >= date(2005,3,1) and {ar_invoices.arih_invoice_date} <= date(2005,3,30)
then
"03/30/2005"

else
"Has not been invoiced yet"

IF construct in DTS

Hi All,

I am new to DTS. I have to pick data from a excel sheet and put it in SQL Server table. If the column value is N/A in the excel then I should load NULL in the target table. Else the value. This is being the case I wrote this construct and it seems to load only NULLs into the target and the values are not getting loaded.

If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then DTSDestination("ABC_transport") = NULL Else DTSDestination("ABC_transport") = DTSSource("ABC transport")
Main = DTSTransformStat_OK

Thanks in advance.

Regards,
Sathishtry the code below, use the line breaks I have...

If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then
DTSDestination("ABC_transport") = NULL
Else
DTSDestination("ABC_transport") = DTSSource("ABC transport")
End If
Main = DTSTransformStat_OK|||Hi,

Thanks for the reply.

I tried it, still it loads only the NULL and not the values.

Regards,
Sathish|||Well the syntax is correct.

I'd check the source data. Is excel displaying N/A when there is something else in the cell? I know it will sometimes show rounded figures when the cell hold the full data.

Can you post a sample of the excel spreadsheet?|||Originally posted by rokslide
Well the syntax is correct.

I'd check the source data. Is excel displaying N/A when there is something else in the cell? I know it will sometimes show rounded figures when the cell hold the full data.

Can you post a sample of the excel spreadsheet?|||Okie,... well there are 1000 non N/A records, 5159 N/A records and 1 null,... how many non N/A records are you getting loaded in your db table?? 6159 or 5159?|||Hi,

That was pretty fast. I am getting loaded with 6159 NULLs.

Regards,
Sathish|||using the dts wizard to import the spreadsheet to a temp ABC transport table and using the following code...

'************************************************* *********************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************* ***********************

Function Main()
DTSDestination("Renewal Month") = DTSSource("Renewal Month")
If Trim(UCase(DTSSource("ABC transport"))) = "N/A" Then
DTSDestination("ABC transport") = NULL
Else
DTSDestination("ABC transport") = DTSSource("ABC transport")
End If
Main = DTSTransformStat_OK
End Function

I get it with values where they are meant to be...

Wanna try this as a test to try and locate your problem?|||Not much luck. Still its not working for me. Let me try for some time to see whether I can get it.

Thank you very much for the help.

Regards,
Sathish|||No worries. Let me know if you need more help, sorry I couldn't solve your problem.|||The problem is with the excel driver - the excel driver attempts to guess at the datatype based on the first X number of entries. Save the excel file as a csv file and do the same dts script using the csv file and it will work.|||By if he is using VBScript to do the transformation then it would be using it's typing (eg. everything is a string) to do the comparison...

Also, wouldn't that also mean that my test transformation wouldn't work where it did work?