Connection String @ runtime (1 Viewer)

[khurram7x]

hi,
I'm looking to use Access Pass-Through queries as a Record Source for reports. There's no Where clause in query.
Now in Docmd.OpenReport Where condition, using the query above, I want to search for records only for specific date.
I've created this query in order to avoid passing SQL from VBA code to keep it clean. Back-end is SQL Server.


Query runs fine if i run the report without Where condition, but displays nothing as soon as I put in this condition.



I'm pasting below a part from procedure calling the function, and function itself. What do I need to do please?


Thanks,
K

---------------Part of calling SP
'Call PTConnStr("qryEmp_w/o_WP")

If IsNull(Me.cbxDate) Then
DoCmd.OpenReport "rptEmp_w/o_WP", acViewReport
Else
DoCmd.OpenReport "rptEmp_w/o_WP", acViewReport, "DateComparison = '" & Me.cbxDate & "'"
End If
-------------Function

Function PTConnStr(queryName As String)

Dim qdef As QueryDef

Set qdef = CurrentDb.QueryDefs(queryName)

qdef.Connect = TempVars("ConnectionString").Value
qdef.ReturnsRecords = True
qdef.Close

'DoCmd.OpenQuery queryName

End Function

 

[pbaldy]

Try using # as the delimiter instead of '. The ' is appropriate in SQL Server, but the where condition is executed in Access.

 

[khurram7x]

Try using # as the delimiter instead of '. The ' is appropriate in SQL Server, but the where condition is executed in Access.

Hi, I've tried both. In fact it was # earlier, and i changed it to ' to see if where condition is going to SQL Server.

 

[pbaldy]

Does the date field have a time component? Is it in US format?

 

[khurram7x]

Does the date field have a time component? Is it in US format?

Ya, it is. Value is stored like this in column 2/6/2017 12:37:42 PM
... I've figured out that it was the format because because SQL Server back-end is generating different date format with - instead of /. So i've changed the query as below and compare it as a string in Where clause of string. Now it works!!
Possible to advice if this is the efficient way please? Isn't it better to create a View at back-end and link as a table?
I'm asking this because I'm not sure if Access treats queries as SQL Server view and keeps stored result during background processing, without a need to run the query at run-time!

SELECT
tblEmployee.FullName,
FORMAT([SignedIn],'MM/dd/yyyy hh:mm tt') AS [Signed In],
FORMAT([SignedOut],'MM/dd/yyyy hh:mm tt') AS [Signed Out],
IIf(Not (IsDate([tblEmp_WP_Clocked_Time].[SignedOut]) = 1),0,(DateDiff("n",[tblEmp_WP_Clocked_Time].[SignedIn],[tblEmp_WP_Clocked_Time].[SignedOut])/60)-(IIf(DateDiff("hh",[SignedIn],[SignedOut])>6,1,0))) AS Availability,
FORMAT([SignedIn],'MM/dd/yyyy') AS DateComparison, tblShift.Next_Day
FROM (tblEmployee INNER JOIN tblEmp_WP_Clocked_Time ON tblEmployee.ID = tblEmp_WP_Clocked_Time.EmployeeID) INNER JOIN tblShift ON tblEmployee.Shift_ID = tblShift.ID
ORDER BY [SignedIn] DESC

 

[pbaldy]

I would be more likely to do a Between comparison on the date, and add 1 to the end date behind the scenes. The problem with yours is that the Format() functions will be applied to every record in the table, not very efficient when the table starts to grow. Along the lines of:

DoCmd.OpenReport "rptEmp_w/o_WP", acViewReport, "FieldName Between #" & Me.cbxDate & "# And #" Me.cbxDate + 1 & "#"

You can use the DateAdd() function, or put the date and 23:59:59 together.

 

[khurram7x]

DoCmd.OpenReport "rptEmp_w/o_WP", acViewReport, "FieldName Between #" & Me.cbxDate & "# And #" Me.cbxDate + 1 & "#"

You can use the DateAdd() function, or put the date and 23:59:59 together.

Thanks pbaldy once again for your useful advice. I am able to change the full query now as:
SELECT
tblEmployee.FullName,
[SignedIn],
[SignedOut],
IIf((IsDate([tblEmp_WP_Clocked_Time].[SignedOut]) = 1),(DateDiff("n",[tblEmp_WP_Clocked_Time].[SignedIn],[tblEmp_WP_Clocked_Time].[SignedOut])/60)-(IIf(DateDiff("hh",[SignedIn],[SignedOut])>6,1,0)), 0) AS Availability,
tblShift.Next_Day
FROM (tblEmployee INNER JOIN tblEmp_WP_Clocked_Time ON tblEmployee.ID = tblEmp_WP_Clocked_Time.EmployeeID) INNER JOIN tblShift ON tblEmployee.Shift_ID = tblShift.ID

...and checked condition as:
'DoCmd.OpenReport "rptEmp_w/o_WP", acViewReport, , "SignedIn >= #" & Me.cbxDate & "# And SignedIn < #" & Me.cbxDate + 1 & "#"

...out of curiosity I tried to work with DateAdd things as well but if i do as below to avoid data from next day, i lose a second from selected date as well and will end up with inaccurate results. Most probably, I've not got you properly?
DoCmd.OpenReport "rptEmp_w/o_WP", acViewReport, , "SignedIn >= #" & Me.cbxDate & "# And SignedIn < #" & DateAdd("s", -1, Me.cbxDate) + 1 & "#"

 

[pbaldy]

You probably want to lose that second. Otherwise, a record for tonight at midnight (which technically is tomorrow) will show up when you run the report for today. It will actually show up twice, which presumably you don't want; when you run the report for today, and again for tomorrow.