Passing Time Value To Stored Proc in through Input Parameters property of Form. ADP

[garyb2008]

Hello All

Ive been creating my first access project in Access 2000 and SQL Server. Its up and running now for 35 users and has been pretty much a success, though ive been stopped in my tracks quite often to figure out how to get these applications to talk with eachother.

Todays problem:
I want to pass time figures to a stored procedure using a forms Input Paramaters property, namely @StartTime and @EndTime (both are defined as datetime typesin the stored proc).

Ive tried
@StartTime = '00:00', @EndTime = '00:00'
@StartTime = #00:00#, @EndTime = #00:00#
and a few others.

PS the stored procedure works fine if run it from the stored procs tab and i type 00:00 and 23:59 into the input boxs when prompted.

I know that access stores a 0 date as 1899-12-30 and SQL uses a different date, but i dont think thats the issue as all dates stored in the db come from the Access front end.

Can anyone help?

Regards

Gary

[NeoPa]

How are you invoking the stored procedure Gary?

I would expect the two values to be passed as parameters, but there are probably various other ways.

Can you put in some examples of your code to illustrate please. Not the whole process necessarily - simply enough to illustrate how you're attempting to pass the values across.

[garyb2008]

To clarify

In the forms property page the recordsource of the form is set to the name of the stored procedure.

The Input parameters property of the form will contain the parameters that the stored procedures requires.
This works fine for stored procs that require integer or string datatypes eg

@intMyParam1 = 6, @strMyParam2 = 'xyz'

Would work fine, but i need to know the syntax to pass times to the stored proc.....

[NeoPa]

I don't know about that way of passing data (communicating) but I do know that date and time literals in ANSI SQL standard (ANSI-92) are all enclosed in hashes (#). How a time without a date is used I'm not positive. I'd have said simply as #01:30# or such like, but you've already found that not to work it seems. Maybe the AM/PM is required #01:30 PM#.

If you don't get any joy I'll be happy to move this across to the SQL Server forum for you. Just let me know.

[garyb2008]

Might be best to try the SQL Server Forum then Neo if you would

Thanks

Gary

[NeoPa]

Consider it done (It has been :D).

[ck9663]

I would recommend that you include the date part in the parameter. Here's why. If your start time is before midnight (1130PM) and your end time is after (1230AM) midnight, and you only include the time, it will default to the same day. That would mean the calculated duration will be 11 hours instead of just 1 hour. Unless you are 100 percent that it will not be the case.

For the passing if the parameter part, could you hard code the dates when passing just to test the SP.

Also, see if you can find something here.


-- CK