470,569 Members | 2,639 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,569 developers. It's quick & easy.

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

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?


Jan 13 '09 #1
6 4272
32,295 Expert Mod 16PB
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
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
32,295 Expert Mod 16PB
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
Might be best to try the SQL Server Forum then Neo if you would


Jan 13 '09 #5
32,295 Expert Mod 16PB
Consider it done (It has been :D).
Jan 13 '09 #6
2,878 Expert 2GB
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.

Similar topics

3 posts views Thread by Hursh | last post: by
3 posts views Thread by rsherman | last post: by
4 posts views Thread by Mike Dinnis | last post: by
reply views Thread by mirandacascade | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.