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.

 

[dhookom]

Did you copy the function from the FAQ link into a new, blank Module and then save the module with a name like "modQueryCode"?

Duane
Hook'D on Access
MS Access MVP

 

[ScorpioMCN]

Well of course I did - just now lol

It works! (only after I changed "pstrQueryName" to "strQueryName" in that last line...must have been a typo?) without errors - I still need to tweak the sql to link in my Access tables - hopefully that will not be another roadblock.

I also need to calculate a date range based on that form date...these formulas work correctly in Access VB:

Dim FormDt As Date, PM2WBDt As Date, PM2WEDt As Date

FormDt = [Forms]![Export Excel Files]![txtEnterDate]

PM2WEDt = IIf(Weekday(FormDt) <= 5, FormDt + (5 - Weekday(FormDt) - 7), FormDt + (5 - Weekday(FormDt)))

PM2WBDt = IIf(Weekday(FormDt) > 5, FormDt + (6 - Weekday(FormDt) - 7), FormDt + (6 - Weekday(FormDt) - 14))

Any ideas how to work these into the sql code? so I can return both PM2WBDt and PM2WEDt ? Thanks so much!

 

[dhookom]

What is the logic of your calculations?
If you need this calculated in the pass-through, it will depend on the syntax supported in the server. For instance, with MS SQL Server, you could get the weekday using an expression like:

DatePart(w,[Your Date Here])

Duane
Hook'D on Access
MS Access MVP

 

[ScorpioMCN]

Thanks D
That isn't working for me though

Dim strSQL As String
Dim strOldSQL As String
Dim strQueryName As String
strQueryName = "edw_Invoice_Detail"
strSQL = "SELECT receipt_date, PC_Item_Key, DATEPART(w,'" & Format$([Forms]![TEST_Export Excel files]![txtEnterDate], "yyyy-mm-dd") & "') As WKDY FROM invoice_detail " & _
"WHERE receipt_date = '" & Format$([Forms]![TEST_Export Excel files]![txtEnterDate], "yyyy-mm-dd") & "'"
strOldSQL = fChangeSQL(strQueryName, strSQL)

It's erroring out...

Run-time error '3061':
Too few parameters. Expected 1.

 

[ScorpioMCN]

SQL above only works if I take out this...
DATEPART(w,'" & Format$([Forms]![TEST_Export Excel files]![txtEnterDate], "yyyy-mm-dd") & "') As WKDY

I've reworked the sql to link in one of my Access tables (deal_items)...this runs without errors...

Dim strSQL As String
Dim strOldSQL As String
Dim strQueryName As String
strQueryName = "edw_Invoice_Detail"
strSQL = "SELECT i.receipt_date, i.PC_Item_Key, d.Item " & _
"FROM invoice_detail i, deal_items d " & _
"WHERE receipt_date = '" & Format$([Forms]![TEST_Export Excel files]![txtEnterDate], "yyyy-mm-dd") & "' AND i.PC_Item_Key = d.Item"
strOldSQL = fChangeSQL(strQueryName, strSQL)

Any ideas on how to calc the weekday (ie 1-7) of the form date?

 

[dhookom]

I've never worked with [Teradata][ODBC Teradata Driver] and expect it might differ from the MS SQL Server syntax.

I don't understand your logic and why you might need to pull the weekday from the server when it can be calculated off a date value in Access.

Duane
Hook'D on Access
MS Access MVP

 

[ScorpioMCN]

True I know how to do it in Access VBA code but I have to be able to filter to only the dated records I need (as Invoice_Detail is a HUGE table) and so I need to know how to write it in SQL...unless I change my form and ask the user to input two dates for the date range (instead of one) - probably what I'll have to end up doing. Was hoping I could get the correct sql syntax for weekday. I'll search teradata forums. Many thanks for your help!

**My logic basically takes the date the user enters (today's date is the default) and finds the most recent COMPLETED fiscal week (our banners have a Friday-Thursday fiscal week) eg if any date from 4/13-4/19/2012 is entered, it will always choose the week of 4/6 (WBDate) thru 4/12 (WEDate)...if any date from 4/20-4/26/2012 is chosen, it will select only matching records for the week of 4/13-4/19 and so on.

 

[dhookom]

I typically use beginning and ending dates with most queries. This might even be more efficient than running a calculation on the server side.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

I don't understand your logic and why you might need to pull the weekday from the server when it can be calculated off a date value in Access.

Friday-Thursday fiscal week) eg if any date from 4/13-4/19/2012 is entered, it will always choose the week of 4/6 (WBDate) thru 4/12 (WEDate)

So why are you not calculating the WBDate and WEDate in VBA and putting those literals in your Query?