Using a query as a Table

[kidrobot]

I'm trying to use a query as a table lets call it "table query". The "table query" is based off of a Teradata table, that is why I am using a query to skim down the data. For some reason the queries I run off of the "table query" give me a "ODBC--call failed" message. Anyone know why this occurs?

If the way I'm doing this is not possible I was thinking of making a make-table query to create the table and run all of the needed functions then delete the made-table in code. Anyone have any other suggestions on this?

 

[kidrobot]

For some reason the query doesn't like the criteria

"Between CDate([Forms]![Form]![txtDatestart]) And CDate([Forms]![Form]![txtDateEnd])"

When I put forms!form!txtdatestart it works just fine, does anyone know why the code above doesn't work?

 

[kidrobot]

In other words is there another way to do this ....

Between CDate([Forms]![Form]![txtDatestart]) And CDate([Forms]![Form]![txtDateEnd])

 

[RuralGuy]

I don't have a good answer for you but did you name your form "Form"? Are you sure the Teradata table fields are date fields?

 

[kidrobot]

I don't have a good answer for you but did you name your form "Form"? Are you sure the Teradata table fields are date fields?

Yes I am 100% sure they are date fields. If I manually type in the between dates the query will work, but when I use my criteria above it give me that odbc error. Kind of weird, if anyone else knows what I can do please let me know!!

 

[RuralGuy]

And what happens when you use the following syntax?
Between [Forms]![Form]![txtDatestart] And [Forms]![Form]![txtDateEnd]
...Did you really name your form "Form"?

 

[The_Doc_Man]

The only issue that I see offhand is that if the date fields are really dates and the field names are named according to a fairly standard naming convention, you might want to consider

... BETWEEN CDate([Forms]![Form]![txtDateStart]) AND ....

In other words, since your Teradata box is running the query, is it trying to tell you that you have an illegal mode mixing (date field vs. text criteria) but is doing a "Lassie" on us? I.e. barking and wagging its tail until we follow it?

If the Teradata box you are using happens to be a relabeled ShareBase box, I recall that those were great boxes with dumb error handling. If it is one of the original Teradata boxes from before the Teradata-ShareBase merger, then the same possibility still exists, I'm just less familiar with the older boxes and can't give you quite as much of a guess as to the probabilities of that type of error.