I created 2 pass through variables to accept date parameters in a SQL connector.  I set the date variable types as DBDate.  I receive an error message that the automated data type is not recognized by Visual Basic.  I also tried setting it as variable type 'date', with the same error.  I tried running a direct query to extract the data and receive the error in the word doc attached.  

If anyone has resolved this issue and would like to share. 

Views: 44


Replies to This Discussion

Hi Donna,

The error in your attachment is becuase you were using direct query to run a set of code containing variables instead of the actual value. If in direct query, you have to replace your values with, in this case, the actual dates.
Going back to your initial problem with the data type, when yu run the report, are you selecting the date from the calendar or are you typing it in? If the variables are of DBDate type then make sure you have allow lookups turned on and select the date from a calendar to avoid error. 
hope this helps.


Those are the settings that I am using with the Pass Through Variables' Data Types set at DBDate, and Field Variable Data Types of DBDate, VarChar and Numeric.

I found the Date Format that I mentioned earlier, it was in the Container.  I changed that from YYYY-MM-DD to MM-DD-YYYY and received an error on execution.

See word doc attached for images. 


Donna try this,
Set your date format back to normal in the container
On your pass through variable expression, remove the lookup type and the lookup SQL Select statement. (they can be blank) This will force the date calendar to display on the parameter lookup. 
Try running the report and select the date from the calendar.  That error is usually a data type unless there is some problem in a record somewhere.

If you dont come right, send me your container code.

I have tried that, it doesn't work.  You actually have to have the lookup type to display the calendar.

No, you actually do not need to, I often use it like this:

See attached, it will not allow a blank value.

Try changing the data type, then set it back to DBDATE again, if that doesn’t reset the lookup, remove the expression then recreate it.

I have tried that.  Is your connector a SQL Query?  I can't figure out why other reports run, but this does not.  The only variable with another report is that this connector is a SQL Query.  I went ahead and retried to run without, received the same error.  The only place in the connector code where I reference the PTVariable is in the Where clause.

(JrnlHdr.TransactionDate Between @FromDate@ And @ToDate@
and JrnlHdr.CustomerSONo<> '')

Try casting the date in the where clause:
(JrnlHdr.TransactionDate Between CAST(@FromDate@ As DATE)  And CAST(@ToDate@ as DATE)
and JrnlHdr.CustomerSONo<>'')
otherwise please send your full query

SELECT Employee.EmployeeName 'Rep Name',
JrnlHdr.TransactionDate 'SO Date', JrnlHdr.Reference 'SON', JrnlHdr.MainAmount 'SO Amt', JrnlHdr.MainAmount 'PO Amt',
JrnlHdr.TransactionDate 'Inv Date', JrnlHdr.Reference 'Inv Number',
JrnlHdr.JournalEx, JrnlHdr.TransactionDate
From JrnlHdr Left Join Employee
(JrnlHdr.TransactionDate Between Cast(@FromDate@ as Date) And Cast(@ToDate@ as Date)
and JrnlHdr.CustomerSONo<> '')
or (JrnlHdr.TransactionDate Between Cast(@FromDate@ as Date) And Cast(@ToDate@ as Date)
and JrnlHdr.Reference like '10____-%')
or (JrnlHdr.TransactionDate Between Cast(@FromDate@ as Date) And Cast(@ToDate@ as Date)
and JrnlHdr.JournalEx=19)

This is the Container Query.  Still receiving the error as is above.

ok, above code seems correct. Discovered that the field JournalEx is the cuase of the problem due to its data type. Cast it to an INT in the SQL Query in the Select statement, not necessary to do it in the where clause. That worked my side.


The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2019   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service