Date prompt in Pass Through Query

Database

Date prompt in Pass Through Query

select REPORTDATE,REPORTNAME_MASTER_ID, REPORTNAME_DESC, EXTOUTCALLS, EXTOUTAHT
from CMS_DAY_SITE
where REPORTDATE =  '?mydatevar'
and REPORTNAME_MASTER_ID in    ('2848', '2852', '3107', '3106', '486', '534', '535', '2166', '2048', '3188', '3190','4052', '1936', '526', '529', '524', '4051', '1838', '484',  '485', '483', '517', '2225', '1839' )
and EXTOUTCALLS > '0'
order by REPORTNAME_MASTER_ID

I have an Access 2010 database that I have created pass through queries connected via odbc to teradata views.  I need to have the query stop and prompt for the date needed and then finish running query.  THis query works in teradata as is but will not run from access database. Gives error code:

[Teradata][odbc teradata driver][teradata database]A character string failed conversion to numeric valu. (#-3535)

Any help would be apreciated 

6 REPLIES
Enthusiast

Re: Date prompt in Pass Through Query

Hi Ray,

Make sure that the format of Date column in Target is same as the one in file you are using for Import. You can add a FORMAT in the SELECT query instead of changing the format in your input file also.

Re: Date prompt in Pass Through Query

Thanks Sakthi

I am not familier with FORMAT.  How would I use it in this quer

I am taking the results and appending a table in ACCESS where the the format of the DATE is mm/dd/yyyy.

Ray

Re: Date prompt in Pass Through Query

Sakthi

I just checked the Date format in both Taget and file I am using for import and they are the same format.

Ray

Enthusiast

Re: Date prompt in Pass Through Query

Hi Ray,

Default date format is yyyy-mm-dd; Please keep your file data in this format and try once.

Re: Date prompt in Pass Through Query

Sakthi,

When I run from Access and use Where REPORTDATE = '2016-03-14' everything works as designed.

when I try to  from Access and use; Where REPORTDATE = '?mydatevar'  is when i receive the above error message.

I am trying to get away from having to go into the code and change date everytime I want to run the query.  I am trying to get Access to prompt for date as I can in Teradata.

Ray

Teradata Employee

Re: Date prompt in Pass Through Query

This is not a Teradata question. You might have better luck in a MS Access forum.

SQL Assistant recognizes the ?mydatevar as a named parameter and generates the prompt. Access does something similar for [mydatevar] syntax in queries against local or linked tables, but that functionality is not available for pass-through queries.

You could create a form that prompts for the value, concatenates the value into a SELECT statement text string, then executes the statement as a pass-through query.