SQL Server does not allow for a dynamic set, which can be problematic if you need to pivot based on the data that comes from rows and you can have various values in the column. In general, you need a stored procedure and dynamic SQL in order to handle the dynamic pivots. This requires uses…
How to Display a Form Automatically when your Application Opens
This is a quick tip on how to automatically display a form when the user opens your Access Application. In addition to improving User Friendliness, your database design will also appear much more professional. The step by step instructions below will display the Switchboard form in figure 1 immediately upon the application opening. This will…
Access IIf() function to SQL Server equivalent
What is the SQL Server equivalent to Access IIf() function? Access SQL IIf([ResultValue]>=1, [Result Value], Null) SQL Server CASE WHEN [Result Value]>=1 THEN [Result Value] ELSE NULL END
Access CDate() function to SQL Server equivalent
What is the SQL Server equivalent to Access CDate() function? Access SQL CDate([Event Timestamp]) SQL Server Equivalent cast([Event Timestamp] as datetime) convert(datetime, [Event Timestamp])
Access NZ() function to SQL Server equivalent
What is the SQL Server equivalent to Access NZ() function? Access SQL NZ([Event Timestamp]) SQL Server Equivalent COALESCE([Event Timestamp],0) ISNULL([Event Timestamp],0) * do not confuse this with the Access isnull() function.
Use controls, not fields
Sometimes we need to use fields from a RecordSource that aren’t shown on the form. It is legal to reference them directly even if they don’t have a control. For example, we can have a form bound to a RecordSource like so: SELECT c.CompanyID, c.CompanyName FROM Companies; And only display CompanyName in a textbox but…
Access InStr() function to SQL Server equivalent
What is the SQL Server equivalent to Access InStr() function? TargetField: String expression being searched SearchValue: String expression being sought StartPosition: Starting position for each search (optional) Access SQL InStr(TargetField, SearchValue) InStr(StartPosition, TargetField, SearchValue) SQL Server CHARINDEX(SearchValue, TargetField) CHARINDEX(SearchValue, TargetField, StartPosition)
Designing a lookup table with logic
It is very common for us to embed some kind of logic based on a lookup table or in some cases, a column of a table. Because we have application logic connected to it, the code are fragile and subject to changes as the requirements develops. We want to avoid this situation where we might…