Pass Through Queries with parameter from form

[Page410]

I'm looking write a pass through query that will contain a parameter supplied by a form. I'm having some difficulty getting this to fire. Does anyone have any experience in doing this?

So far, this is what I have:
SELECT DISTINCT dbo.clmsttus_hist_lmy_v.clm_num, dbo.clmsttus_hist_lmy_v.clm_sttus_cde, dbo.clmsttus_hist_lmy_v.clm_change_dte
FROM dbo.clmsttus_hist_lmy_v
WHERE dbo.clmsttus_hist_lmy_v.clm_sttus_cde = "IP" AND dbo.clmsttus_hist_lmy_v.clm_change_dte > 'USER DEFINED FUNCTION'

Any help would be appreciated.

 

[tunsarod]

If the parameter is contained in a form's textbox then use

Forms!Formname!Fieldname

SELECT DISTINCT dbo.clmsttus_hist_lmy_v.clm_num, dbo.clmsttus_hist_lmy_v.clm_sttus_cde, dbo.clmsttus_hist_lmy_v.clm_change_dte
FROM dbo.clmsttus_hist_lmy_v
WHERE dbo.clmsttus_hist_lmy_v.clm_sttus_cde = "IP" AND dbo.clmsttus_hist_lmy_v.clm_change_dte > Forms!Forname!Fieldname;

 

[Page410]

Thank you for the offering but that was the first thing that I've tried that and it's a definite no go. I'm trying to hit this table through an ODBC connection to a SQL Server and the syntax that you would use with the jet isn't going to work.
I need to find a way to pass the parameter in a manner that the the servers native language can understand.

Again, any help would be appreciated.

 

[cmmrfrds]

You question appears to be related to control flow, but there is no indication of what the current flow is. Are you planning on running the query after a button click or an afterupdate event or got focus envent or other????

 

[Page410]

Correct. The user initiates the flow by clicking a button click on a form.

 

[cmmrfrds]

When the user clicks the button put the date variable from the Form into a global variable and return the date from your user defined function.

Standard Module.
Public mydate as date

You will need single quotes around the date in sql server so you might be able to supply it form the function or else put in the sql statement that is being built.

Function Returndate() as date
Returndate = mydate
End Function
Alternative to try.
Function Returndate() as string
Returndate = "'" & mydate & "'"
End Function

Hope this is what you were asking.

 

[Page410]

I have the function built. It's represented by the 'User Defined Function' in my initial post. My problem arises when I try to pass that value to the server. I get error #195
ODBC--Call Failed
[MERANT][ODBC Sybase driver][SQL Server]'FCalc' is not a recognized built-in function name.

 

[cmmrfrds]

Okay, it didn't register when I first looked at the code. You cannot include an Access Function in a pass through query, any value returned from the function needs to be concatenated to the original SQL Statement.

See this link on how to accomplish.

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q131534&

 

[Page410]

That appears to be it, thanks. All this trouble makes me wish we had better tools to work with.

Thanks for the tip!