Access pass-through query can't recognize form date...pls help

[ScorpioMCN]

I've searched throughout this site and googled also and have not found any posts that resolves my issue.

I have this sql in a pass-through query in Access 2010...I need the sql to use the date the user enters in the Access form...I get this error however:
ODBC--call failed.
[Teradata][ODBC Teradata Driver][Teradata Database] A character string failed conversion to a numeric value. (#-3535)

Can someone please help? I'm at my wit's end. Thanks!

SELECT
*

FROM
invoice_detail

WHERE receipt_date = '" & Format$([Forms]![TEST_Export Excel files]![txtEnterDate], "yyyy-mm-dd") & "'

--These all work:
--WHERE receipt_date = current_date
--WHERE receipt_date = '2012-04-16'
--WHERE receipt_date = 1120416
--WHERE receipt_date between 1120416 and 1120417
--WHERE receipt_date >= '2012-04-16' AND receipt_Date <= '2012-04-17'

--These do not work:
--WHERE receipt_date = [Forms]![TEST_Export Excel files]![txtEnterDate]
;

ps: I also need it to calculate the weekday of the form's date (eg 4/12/2012 = 5) - I have not found a weekday function that works in the above sql.

 

[genomon]

txtEnterDate looks like is being recognized as text and not a date. Try converting it CDate([Forms]![TEST_Export Excel files]![txtEnterDate]). If that doesen't work, try enclosing it in # characters:
"WHERE receipt_date = #" & [Forms]![TEST_Export Excel files]![txtEnterDate] & "#"

Beir bua agus beannacht!

 

[dhookom]

You need to change the SQL of the saved query faq701-7433


Duane
Hook'D on Access
MS Access MVP

 

[lameid]

I suggest you find a teradata forum and find out how to write the query.... Once you know what it is supposed to look like, if you need help getting to work in Access we can help.... Unless you get lucky and someone here knows teradata... Glancing with google I could not figure out how it expects date literals to be formatted.

 

[ScorpioMCN]

Thanks guys for the feedback!

I've tried 3 more formats...all failed :

1) WHERE receipt_date = CDate([Forms]![TEST_Export Excel files]![txtEnterDate])

Error was:
ODBC-call failed.
[Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between '(' and the end of the request. (#-3706)

2) WHERE receipt_date = #" & [Forms]![TEST_Export Excel files]![txtEnterDate] & "#
Error was:
ODBC-call failed.
[Teradata][ODBC Teradata Driver][Teradata Database] Name requires more than 30 bytes in LATIN internal form. (#-3737)

3) WHERE receipt_date = #" & CDate([Forms]![TEST_Export Excel files]![txtEnterDate]) & "#
Error was:
(same as in # 2)

Duane - I looked at that link but I'm not sure how to work that into my sql as it looks like VBA code (???). I'm not using vba code - I will at some point but I need this filter to work first on my pass-through query.

Any ideas how to get the date to be valid?

 

[ScorpioMCN]

lameid - that's why included those WHERE statements that did bring back results...

All these work:
--WHERE receipt_date = current_date
--WHERE receipt_date = '2012-04-16'
--WHERE receipt_date = 1120416
--WHERE receipt_date between 1120416 and 1120417
--WHERE receipt_date >= '2012-04-16' AND receipt_Date <= '2012-04-17'

I just can't get it to use the form date

So it's either looking for it like this: 'YYYY-MM-DD' -OR-
with a 1 (indicating 21st century date) + 2 digit year + mm + dd

 

[genomon]

I found this thread that references Teradata SQL assistant. Maybe worth looking at?

http://datawarehouse.ittoolbox.com/...dbc-driver-and-teradata-sql-assistant-1533478

Beir bua agus beannacht!

 

[ScorpioMCN]

Thanks Genomon - but that's not my issue - as I already have Teradata SQL Assistant on my pc - and I frequently use it to query the same table (and others) with no issues that I'm trying to in Access.

I've also researched the Teradata forums and found nothing that helps my situation - in fact, most questions go unanswered there - ie very little or no feedback - so that's why I'm here - this is the BEST forum.

 

[genomon]

You are right about their fora. I have no experience with Teradata. This thread discusses using their SQL syntax but I can't seem to find any specifics on their ODBC driver. I don't know if it's useful for you or not, but it does briefly discuss the fact that single-digit days/months are a no-no:

http://forums.teradata.com/forum/database/convert-string-to-date-format

Maybe the date formatting on the Access form could use tweaking?

Beir bua agus beannacht!

 

[PHV]

The real issue is that you can't use any parameter nor VBA functions in a PT query.
Why not using a linked table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ScorpioMCN]

Are you sure??? As I read a few posts where people were passing parameters to a PT query and seemed to have had luck doing it...unless they were smokin something (??)

Using a linked table is something I can do but our IT folks aren't going to like me doing that...AND it's going to be MUCH slower to process.

Any suggestions to get this working?

 

[dhookom]

Always believe PHV. I don't anyone has received nearly 10,000 stars for posts in Tek-Tips.

You can't reference controls on forms within a pass-through. That's why I suggested using code to change the SQL of the pass-through so it doesn't include the control reference.



Duane
Hook'D on Access
MS Access MVP

 

[ScorpioMCN]

Understood - so what I'm trying to do is not possible. So I'm fuzzy on how I use the vb code in my pass-through query...can you please explain in more detail how I'd work your code into my query?

I really appreciate all the responses from you guys - you're the best!

 

[lameid]

--OOPS--- I hate it when people don't read especially when it is me


In any case...

You need to set the SQL property of a querydef object to make parameters work, assuming you have the Pass-through query setup already. I wrote a procedure to set the SQL property of a named query... You just pass it the entire pass through statement in code, in effect you concatenate the parameter as a literal into your string.

Sub SetSQL(strQuery, strSQL)
    On Error GoTo SetSQL_Err
    Dim qry As QueryDef
    
    
    Set qry = CurrentDb.QueryDefs(strQuery)
    qry.SQL = strSQL

SetSQL_Exit:
    qry.Close
    Set qry = Nothing
Exit Sub

SetSQL_Err:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error In SetSQL for query " & Nz(strQuery, "Null Query")
    GoTo SetSQL_Err
End Sub

So something like...

strSQL = "SELECT * " & vbCRLF & _
"FROM invoice_detail " & vbCRLF & _
"WHERE receipt_date = '" & Format$([Forms]![TEST_Export Excel files]![txtEnterDate], "yyyy-mm-dd") & "'"

SetSQL "YourExistingQuerywithConnectionstring", strSQL

 

[lameid]

That's funny... the goto in the error hanlder is wrong...
Glad you made me review that today...

Resume SetSQL_Exit

 

[dhookom]

LameID,
Your code similar to the code I suggested earlier.

Assuming the name of the pass-through is "qsptMyName" you could use code in the after update event of txtEnterDate on your form

Dim strSQL As String
Dim strOldSQL As String
Dim strQueryName as String
strQueryName = "qsptMyName"
strSQL = "SELECT * FROM invoice_detail " & _
  "WHERE receipt_date = '" & Format$(Me.[txtEnterDate], "yyyy-mm-dd") & "'"
strOldSQL = fChangeSQL(pstrQueryName As String, strSQL As String)

Duane
Hook'D on Access
MS Access MVP

 

[ScorpioMCN]

You guys ROCK! - I'm going to try those methods when I get time and I'll get back to you on whether it works for me. Thanks again!

 

[ScorpioMCN]

dhookom - there seems to be a problem with the last line of code...

strOldSQL = fChangeSQL(pstrQueryName As String, strSQL As String)

Can you please advise? Thanks.

 

[dhookom]

My bad. Try get rid of the "As String":

strOldSQL = fChangeSQL(pstrQueryName, strSQL)

Duane
Hook'D on Access
MS Access MVP

 

[ScorpioMCN]

Got an error...when I ran the code, it highlighted "fChangeSQL" and says "Compile error: Sub or Function not defined