passing a date from an Access function to SQL server

[Leslie]

Good morning. I am trying to pass a date variable from MS
Access 2000 to SQL server using a passthrough query. I am
getting the error message 'The getdate function requires 0
arguments'. My query looks like this:

SELECT [txdate], count(*) AS PyxCount FROM [Pyxis Data]
WHERE txdate between getdate(1) and getdate(2)
GROUP BY [txdate];

and my function like this:

Public BeginDate As Date
Public EndDate As Date

Function GetDate(TypeDate As Integer)

'This function used for date parameters for all reports
(Starting and Ending Date Range)

Select Case TypeDate
Case 1: GetDate = BeginDate & " 0:00:00 AM"
Case 2: GetDate = EndDate & " 0:00:00 AM"
Case 3: GetDate = BeginDate
Case 4: GetDate = EndDate
End Select

End Function

Any ideas what I am doing wrong? Thanks very much!, Leslie

 

[Leslie]

Thanks. Is there any way to assign a user-entered date to
a variable then pass that to SQL server? I have a number
of queries that run one after the other in a macro. I
don't want the user to have to enter the date range
multiple times.

I had this assigned to the user-defined Access getdate()
function as below in Access 97 running on Windows NT. We
recently converted to Access 2000 on Windows 2000 and the
queries are running very, very slowly. I had hoped
converting them to pass-thru might speed things up..

Thanks!, Leslie