SQL Passthrough using dates

  • Thread starter Thread starter Dave Flowers
  • Start date Start date
D

Dave Flowers

Hi all,

I'm using an Access 2003 database (mdb) linked to SQL 2005 database tables.
Everything works fine until I want to query the database using dates.

Here is the sql string from my query:

SELECT dbo_tblDistrict.DistrictName, dbo_tblContact.Name,
dbo_tblSupportLog.TicketID, dbo_tblProducts.ProdDescription,
dbo_tblSupportLog.Date, dbo_tblSupportLog.Resolution,
dbo_tblSupportLog.Start, dbo_tblSupportLog.End, Format([End]-[Start],"Short
Time") AS Total
FROM dbo_tblProducts, (dbo_tblSupportLog INNER JOIN dbo_tblDistrict ON
dbo_tblSupportLog.DID = dbo_tblDistrict.DID) INNER JOIN dbo_tblContact ON
(dbo_tblDistrict.DID = dbo_tblContact.DID) AND (dbo_tblSupportLog.CID =
dbo_tblContact.CID)
WHERE (((dbo_tblSupportLog.Date) Between
[Forms]![frmMonthlyTimeReport]![dtpStart] And
[Forms]![frmMonthlyTimeReport]![dtpEnd]));

This works fine using the Access tables but now that they have been linked
to the SQL database I just get all the records. It doesn't filter anything
out based on date.

Dave
 
You need to use some code to change your sql. On your form you enter the
dates you can add a button to do:

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "“SELECT dbo_tblDistrict.DistrictName, dbo_tblContact.Name,
dbo_tblSupportLog.TicketID, dbo_tblProducts.ProdDescription,
dbo_tblSupportLog.Date, dbo_tblSupportLog.Resolution,
dbo_tblSupportLog.Start, dbo_tblSupportLog.End, Format([End]-[Start],"Short
Time") AS Total
FROM dbo_tblProducts, (dbo_tblSupportLog INNER JOIN dbo_tblDistrict ON
dbo_tblSupportLog.DID = dbo_tblDistrict.DID) INNER JOIN dbo_tblContact ON
(dbo_tblDistrict.DID = dbo_tblContact.DID) AND (dbo_tblSupportLog.CID =
dbo_tblContact.CID)
WHERE (((dbo_tblSupportLog.Date) Between ‘â€&
[Forms]![frmMonthlyTimeReport]![dtpStart] &â€â€™ And Ԡ&
[Forms]![frmMonthlyTimeReport]![dtpEnd] &â€â€™))â€

Set dbCurr = CurrentDb
Set qdfCurr = dbCurr.QueryDefs("PassThroughQueryName")
qdfCurr.SQL = strSQL

You will also have to change the format comand on the date since the SQL is
different, but I do not know what it is off the top of my head. I would
suggest DateDiff(day,[end],[start]) since that looks like what you are trying
to accomplish. After this you can run the query.
 
What are the date ranges in your data?
What were the date values entered on the form frmMonthlyTimeReport?
 
Hi Steve,

The dates are the first day of the current month to the current date. The
values are in "Short Date" format.

I've taken some of what schasteen suggested and tried to run it but now I
get an error in the JOIN syntax.

Here's the code from the button click event on frmMonthlyTimeReport:
-------------------------------------------------------
Private Sub cmdReport_Click()
On Error GoTo Err_cmdReport_Click

Dim stDocName As String
Dim dbCurr As DAO.Database
Dim RS As DAO.Recordset
Dim qrDef As DAO.QueryDef
Dim strSql As String
Dim sSDate As String
Dim sEDate As String

sSDate = Format(dtpStart.Value, "Short Date")
sEDate = Format(dtpEnd.Value, "Short Date")

strSql = "SELECT dbo_tblDistrict.DistrictName, dbo_tblContact.Name, " &
_
"dbo_tblSupportLog.TicketID, dbo_tblProducts.ProdDescription,
dbo_tblSupportLog.Date, " & _
"dbo_tblSupportLog.Resolution, dbo_tblSupportLog.Start,
dbo_tblSupportLog.End, " & _
"DateDiff(dbo_tblSupportLog.End - dbo_tblSupportLog.Start) AS Total " &
_
"FROM dbo_tblProducts, (dbo_tblSupportLog " & _
"INNER JOIN dbo_tblDistrict ON dbo_tblSupportLog.DID =
dbo_tblDistrict.DID) " & _
"INNER JOIN dbo_tblContact ON (dbo_tblDistrict.DID = dbo_tblContact.DID)
" & _
"AND (dbo_tblSupportLog.CID = tblContact.CID) " & _
"WHERE (((dbo_tblSupportLog.Date) Between '" & sSDate & "' And '" &
sEDate & "'));"

Set dbCurr = CurrentDb
Set qrDef = dbCurr.CreateQueryDef("", strSql)

Set RS = qrDef.OpenRecordset(dbOpenSnapshot)

stDocName = "Monthly Time Report"
DoCmd.OpenReport stDocName, acPreview, RS

Exit_cmdReport_Click:
Exit Sub

Err_cmdReport_Click:
MsgBox Err.Description
Resume Exit_cmdReport_Click

End Sub
------------------------------------------------------------------------------------
Thanks Steve

[MVP] S.Clark said:
What are the date ranges in your data?
What were the date values entered on the form frmMonthlyTimeReport?

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Dave Flowers said:
Hi all,

I'm using an Access 2003 database (mdb) linked to SQL 2005 database
tables. Everything works fine until I want to query the database using
dates.

Here is the sql string from my query:

SELECT dbo_tblDistrict.DistrictName, dbo_tblContact.Name,
dbo_tblSupportLog.TicketID, dbo_tblProducts.ProdDescription,
dbo_tblSupportLog.Date, dbo_tblSupportLog.Resolution,
dbo_tblSupportLog.Start, dbo_tblSupportLog.End,
Format([End]-[Start],"Short Time") AS Total
FROM dbo_tblProducts, (dbo_tblSupportLog INNER JOIN dbo_tblDistrict ON
dbo_tblSupportLog.DID = dbo_tblDistrict.DID) INNER JOIN dbo_tblContact ON
(dbo_tblDistrict.DID = dbo_tblContact.DID) AND (dbo_tblSupportLog.CID =
dbo_tblContact.CID)
WHERE (((dbo_tblSupportLog.Date) Between
[Forms]![frmMonthlyTimeReport]![dtpStart] And
[Forms]![frmMonthlyTimeReport]![dtpEnd]));

This works fine using the Access tables but now that they have been
linked to the SQL database I just get all the records. It doesn't filter
anything out based on date.

Dave
Click to expand...
Click to expand...
 
Back
Top