> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Convert Common Access Field Defaults to SQL Server
- URL: https://nolongerset.com/access-field-defaults-to-sql-server/
- Published: 2022-04-22T02:39:59.000Z
- Updated: 2026-05-08T12:58:08.000Z
- Description: Here's the Select Case statement I use to convert common Access field default calculations--Now(), Date(), etc.--to their SQL Server equivalents.
- Author: Mike Wolfe
- Tags: Migration, SQL Server, #Import 2026-05-20 02:59

I wrote a custom upsizing Access application that converts Microsoft Access backend data files to SQL Server.

One part of that application deals with converting common Microsoft Access field defaults into their SQL Server equivalents. For example, the VBA `Now()` function gets converted to the SQL Server `GETDATE()` function. The VBA date literal wrappers (`#`) are replaced with T-SQL date literal wrappers (i.e., single quotes: `'`). Note that I replace Yes/True defaults with `-1` and No/False defaults with `0` because [I use SQL Server smallint fields to replace MS Access Yes/No fields](https://nolongerset.com/yes-no-fields-in-sql-server/).

Here is the `Select Case` statement I use:

```vba
Select Case Fld.DefaultValue
Case "Yes", "True"
    DefaultVal = -1
Case "No", "False"
    DefaultVal = 0
Case "=Now()", "Now()"
    DefaultVal = "GetDate()"
Case "=Date()", "Date()"
    DefaultVal = "DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))"
Case "=Time()", "Time()"
    DefaultVal = "CONVERT(TIME, GETDATE())"
Case "=DatePart(""yyyy"",Now())"
    DefaultVal = "YEAR(GETDATE())"
Case Else
    DefaultVal = Replace(Fld.DefaultValue, """", "'")
    DefaultVal = Trim(Replace(DefaultVal, "#", "'"))
    
    'remove leading equal sign (valid in Access but not SQL Server)
    If Left(DefaultVal, 1) = "=" Then DefaultVal = Trim(Mid(DefaultVal, 2))
End Select
```

This is not an exhaustive list, but it covers many of the most common default field value scenarios. If you think I left something out, let me know in the comments below.

### Referenced articles

[Yes/No Fields in SQL ServerThe SQL Server equivalent of an Access Yes/No field is the “bit” data type. Or is it?![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/01/pexels-pixabay-210600.jpg)](https://nolongerset.com/yes-no-fields-in-sql-server/)

*Image by [Gerd Altmann](https://pixabay.com/users/geralt-9301/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=3753417) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=3753417)*