[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How to substitute the DateSerial function to make my SQL execute in MS SQL Server

Posted on 2005-04-22
Medium Priority
Last Modified: 2011-10-03
Is there a way to rewrite the code that follows, in Access, that I could place in a SQL string and execute in a MS SQL Server Stored Procedure:

I can't use the following code because DateSerial is not valid in MS SQL Server.
I need to take the date in tblA.dteA and compare it to 6/30th of the year determined by a user by selecting a year from a list box
named lstYear. The year selected is then reduced by the value in tblA.intProp.

tblA.dteA - value of a field from tblA                                                               (for ex: 02/22/1998)
lstYear - calendar year value selected from a list box                                       (for ex: 2005)
tblA.intProp - value of a field from tblA (integer value of 1 or 2 or 3 or 4 or 5)    (for ex: a value of 5

the WHERE CLAUSE I formerly used in my SQL string but which is not acceptable in MS SQL Server is as follows:

WHERE ((tblA.dteA <= DateSerial ("lstYear.Value) & "- tblA.intProp,6,30))

in my example above this WHERE clause translates to:

WHERE 2/22/1998 <= (2005 - 5) ,6,30 which translates to 2/22/1998 <= 6/30/2000

Question by:zimmer9
  • 3
  • 2
LVL 11

Expert Comment

ID: 13847671
Try the following
WHERE ((tblA.dteA <= DateSerial (Form!NameOfForm!lstYear.Value - tblA.intProp,6,30))

should work in query


Author Comment

ID: 13847726
Do you think DateSerial is permissible to use in MS SQL Server stored procedures ?

Author Comment

ID: 13847785
Is there a way to convert this using the DateAdd fucntion instead ?
LVL 11

Accepted Solution

Stephen_Perrett earned 1500 total points
ID: 13848415
Sorry my mistake, somehow I thought you wanted to convert from MS SQL Server to Access

I would write DateAdd as follows in Access

DateAdd("y",-tblA.intProp, #2005/6/30#)

but you need some way to pass year as parameter value to your stored procedure and that's something I don't Know anything about

LVL 11

Expert Comment

ID: 13852074
Thanks zimmer9


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question