472,780 Members | 1,200 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,780 software developers and data experts.

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 4462
32,534 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,534 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,534 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

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

Similar topics

by: Hursh | last post by:
Hi, I have written some stored procedures in SQL and these procedures return some value. I want these values to be captured by the ASP code. I am able to access the tables using ADO(...
by: zlatko | last post by:
There is a form in an Access Project (.adp, Access front end with SQL Server) for entering data into a table for temporary storing. Then, by clicking a botton, several action stored procedures...
by: rsherman | last post by:
I am running a windows app. with a stored procedure and the stored proc runs up to 90 seconds, the time out in the connection string is set for 90 seconds. It seems that the time out setting is...
by: rickcheney | last post by:
I just changed my Access 2002 database to a SQL Server ADP project. I had a form where the user entered a value into a text box and when a command button on the form was clicked a Report was...
by: Bob | last post by:
I'm new to Access projects and SQL server and am not a veteran VB programmer. There's a cry for help! I'm attempting to print the current form on screen by using a command button which the user...
by: Mike Dinnis | last post by:
Hi, I've been working through a number of turorials to try to learn more about retrieving data from a SQL database. I think i've mastered techniques where i create a sql string in the page and...
by: Martin Raychev | last post by:
Hi all, I have the following problem: I have a private method that returns a SqlDataReader. For this to work I have not to close the DB connection in the above method. I do this only to
by: Magy | last post by:
What would be the best way to execute a Oracle stored procedure that excepts several input paramters, through a web method in vb.net. What would be a good way to get to the web method, the Oracle...
by: mirandacascade | last post by:
Questions toward the bottom of the post. Situation is this: 1) Access 97 2) SQL Server 2000 3) The Access app: a) sets up pass-thru query b) .SQL property of querydef is a string, the...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.