select REPORTDATE,REPORTNAME_MASTER_ID, REPORTNAME_DESC, EXTOUTCALLS, EXTOUTAHT
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
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.
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.
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.
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.