Solved

Help with dates in a SQL server pass through query

Posted on 2011-09-19
13
383 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
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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
 

Author Closing Comment

by:snyperj
Comment Utility
Thanks, I was able to pass:

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

Author Comment

by:snyperj
Comment Utility
using variables set beforehand for the actual dates
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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
Comment Utility
@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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now