SQL Prompt for Input from Form

[dodyryda]

Hi..

I have the query below setup in my access adp project. It works, ok but rather than have the standard prompts I want the sql stored procedure to use the details from an unbound form. I have the form setup but unsure of the sql code. Does anyone know how to achieve this?

SELECT dbo.Calendar.[Job Date], dbo.JobSpec.Driver, dbo.JobSpec.[Job Type], dbo.JobSpec.[Company Name], dbo.JobSpec.[Site Location],
dbo.JobSpec.Equipment, dbo.JobSpec.[Purchase Goods], dbo.JobSpec.[Deliver Time]
FROM dbo.Calendar INNER JOIN
dbo.JobSpec ON dbo.Calendar.ID = dbo.JobSpec.ID
WHERE (dbo.JobSpec.Driver = @Enter_Driver) AND (dbo.Calendar.[Job Date] BETWEEN @StartDate AND @EndDate)

 

[MStef]

In query criteria put:
[Forms]![FormName]![FieldName]

 

[dodyryda]

I tried changing the text from BETWEEN @StartDate AND @EndDate

to Between [forms]![RPT Driver Criteria]![StartDate] AND [forms]![RPT Driver Criteria]![EndDate]

but it will not accept the text stating your entry cannot be converted to a valid date time value

 

[MStef]

You do something wrong, obviously.

 

[JamesMcS]

Helpful, MStef.

Try using cdate around each field name:

Between cdate([forms]![RPT Driver Criteria]![StartDate]) AND cdate([forms]![RPT Driver Criteria]![EndDate])

You might also want to change the @Enter_Driver to an expression that refers to the control on the form, as you've done with Start and EndDate.

 

[dodyryda]

thanks for the suggestion james but this erros with the following

Error in list of function arguments: '!' not recognized.
Error in list of function arguments: ')' not recognized.
Unable to parse query text.

 

[JamesMcS]

OK Try this:

Between cdate([forms]![RPT Driver Criteria].form![StartDate]) AND cdate([forms]![RPT Driver Criteria].form![EndDate])

 

[dodyryda]

seems to accept the structure now James but it kicks with 'ADO:'cdate' is not a recognized built in function name. is their an ado equivalent?

 

[JamesMcS]

Hmmm I've not had much experience with ADO. Er, anything on google or in the help file?

It might just be a case of surrounding the variables in #, but I wouldn't know how to do this outside of VBA.

 

[dodyryda]

zip in the help file and googling brought up very little

 

[JamesMcS]

Brilliant. I did have a bit of a look myself, to no avail... the only other thing I can suggest is, is there a searchable MS ADO website, like the MSDN site?

 

[dodyryda]

solved it.. with a little help from this page http://www.tech-archive.net/Archive...ic.access.adp.sqlserver/2006-10/msg00120.html

basically the @ parameters are left in the sql query. On the report set the input parameters property to similar to the following.

@Driver char=Forms![RPT Driver Criteria]!name, @StartDate datetime=Forms![RPT Driver Criteria]!SD, @EndDate datetime=Forms![RPT Driver Criteria]!ED

have a button on your form that opens the report, job done..

real easy when you know how..

 

[doco]

Shouldn't it be

    "...Between '" & cdate([forms]![RPT Driver Criteria].form![StartDate]) & _
 "'  AND  '" &  cdate([forms]![RPT Driver Criteria].form![EndDate]) & "'"

 

[G37Sam]

Don't you need wrap dates with the # ?

Well if it's solved it's solved I guess

 

[doco]

It would only need the '#' if the query were going to be run client side in Access. It is my understanding the query is run in SQL Server so "'" single quotes are needed