By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,163 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

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

P: 18
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
Jan 13 '09 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,541
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.
Jan 13 '09 #2

P: 18
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.....
Jan 13 '09 #3

NeoPa
Expert Mod 15k+
P: 31,541
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.
Jan 13 '09 #4

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

Thanks

Gary
Jan 13 '09 #5

NeoPa
Expert Mod 15k+
P: 31,541
Consider it done (It has been :D).
Jan 13 '09 #6

ck9663
Expert 2.5K+
P: 2,878
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
Jan 14 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.