Solved

Help with dates in a SQL server pass through query

Posted on 2011-09-19
13
428 Views
Last Modified: 2012-05-12
I use the following expression as query criteria  in access to return records that were entered during the previous 14 days only.

labor_date = Between Date()-14 and Date()

What would be the equivalent syntax for a pass through query against a SQL Server table.  I know that SS handles dates differently than access.

Thanks
0
Comment
Question by:snyperj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 36561802
It has been a while since I've used SQL Server, but I believe you are going to have to build that SQL string manually.

strSQL = "SELECT ... FROM yourTable WHERE [Labor_Date] BETWEEN '" & Format(Date()-14, "yyyy-mm-dd") & "' " _
           & "AND '" & Format(Date(), "yyyy-mm-dd") & "'"

Then push that SQL into the SQL property of your query:

currentdb.Querydefs("YourPassThruQueryName").SQL = strSQL

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36561803
try using dateserial()

labor_date between dateserial(year(date()),month(date()),day(date())) and dateserial(year(date()),month(date()),day(date())-14)
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 36562899
SQL Server will deal with date literals surrounded by single quotes
Here's the SQL text of my pass-through

Exec SpInspectorRevenueSummary '27-May-10 12:00 AM' ,'02-Jun-10 11:59 PM', 1

It EXECutes SpInspectorRevenueSummary passing in two date literals and a third integer parameter
Dates are fun things :(
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Closing Comment

by:snyperj
ID: 36563213
Thanks, I was able to pass:

 between '2011-09-05' and '2011-09-19'
0
 

Author Comment

by:snyperj
ID: 36563220
using variables set beforehand for the actual dates
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36563288
Once you get the hang of it, it is easy enough.
Use VBA to build a T-SQL compliant string
Use a querydef, and change it's SQL property to that string
Give 'er
 
Dim db As Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRecordSource As String

'how this works:
'On the SQL db there is a stored procedure WhatEverYourSPisNamed  that needs parameters
'In the mdb there is a passthrough query that has an ODBC connection string and returns records = true
'The text in the passthrough query matters little, it could even be blank. It gets replaced
'by the code below, then executed and the records populate the report.
'the stored procedure is WhatEverYourSPisNamed 
Set db = CurrentDb
Set qdf = db.QueryDefs("SomePassThroughQuery")
With Forms!frmInspectorRevenueSummary
    strRecordSource = "Exec WhatEverYourSPisNamed " & Chr(39) & Nz(!StartDate, "'1/1/2005'") & Chr(39) & " ," & Chr(39) & Nz(!EndDate, Now()) & Chr(39) & ", " & Me.FrameDateType
End With
qdf.SQL = strRecordSource
Me.RecordSource = "SomePassThroughQuery"

Open in new window

0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 36564087
If you passed:

between '2011-09-05' and '2011-09-19'

Then why did you not select my response as the answer?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36564114
It wouldn't have worked @fyed.
For a passthrough, it has to be bare T-SQL syntax
It would have needed to be literally

exec somepassthrough @p1, @p2....@p1000

You got the formatting of the date literals surrounded by single quotes right, but the sproc is going to take the parameters and do stuff with them, so the rest of the SQL was wrong.
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 36564263
Nick,

I disagree.  The OP didn't say anything about a stored procedure, and I the SQL syntax I provided would work just fine as the SQL of a passthrough query.

The OP could then set that query as the Recordsource of a report or form, or as the RowSource of a list or combo.

Dale
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36564293
I guess I had sproc on the brain.  It'd the only thing I use passthroughs for.  You could be right about your syntax working
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 36564478
Nick,

I rarely contest the OP's selection of the correct answer, but nothing you provided contained the

" between '2011-09-05' and '2011-09-19' "

syntax the OP claimed to use in the problem solution.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36565320
Points aren't a big deal.
The accepted solution does show that I feed in date literals surrounded by single quotes.
That's the needed chunk of the syntax, knowing that SQL Server needs single quotes and not hashes (#)
Yours shows that too, but maybe it got lost in the '" '" mega quote notation
Your syntax would have worked

There's no much question how the dates are going in here
Exec SpInspectorRevenueSummary '27-May-10 12:00 AM' ,'02-Jun-10 11:59 PM', 1
<SQL Server will deal with date literals surrounded by single quotes>

@Nick67
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36567856
@fyed
I do have to thank you.
I've never seen anything like <Date()-14> before or realized that that would work
Or that more generally DateValue("SomeStringDate')-14 would work
I've always used the more tortured
Format(DateSerial(Year(Now()), Month(Now()), Day(Now()) - 14), "dd-mmm-yyyy")
to do date calculations.

Thank you!
I learned something new.

@Nick67
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

696 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