String variable causes Compile Error

[charles.kendricks]

I am trying to put together a string variable for an SQL query that
contains a DatePart function. Part of the code goes as follows:

Dim dteTuesdayDate As Date
Dim strSql As String
Dim StrCriteria
Dim Mo As String
Dim Da As String

dteTuesdayDate = Me!cmbTuesdayDate
Mo = DatePart("m", dteTuesdayDate)
Da = DatePart("d", dteTuesdayDate)
StrCriteria = "Where DatePart("m", Birthdate) = Mo AND"
StrCriteria = StrCriteria & " DatePart("d", Birthdate) Between "

The double quotes are causing a compile error. Can anyone give me
some guidance as to how I should form this string variable StrCriteria?

 

[Dirk Goldgar]

In

I am trying to put together a string variable for an SQL query that
contains a DatePart function. Part of the code goes as follows:

Dim dteTuesdayDate As Date
Dim strSql As String
Dim StrCriteria
Dim Mo As String
Dim Da As String

dteTuesdayDate = Me!cmbTuesdayDate
Mo = DatePart("m", dteTuesdayDate)
Da = DatePart("d", dteTuesdayDate)
StrCriteria = "Where DatePart("m", Birthdate) = Mo AND"
StrCriteria = StrCriteria & " DatePart("d", Birthdate) Between "

The double quotes are causing a compile error. Can anyone give me
some guidance as to how I should form this string variable
StrCriteria?

Either this:

StrCriteria = "Where DatePart(""m"", Birthdate) = Mo AND"
StrCriteria = StrCriteria & " DatePart(""d"", Birthdate) Between "

Or this:

StrCriteria = "Where DatePart('m', Birthdate) = Mo AND"
StrCriteria = StrCriteria & " DatePart('d', Birthdate) Between "

You can embed a quote in a quoted string by doubling it up, or you can
take advantage of the fact that SQL accepts either double quotes (") or
single quotes (') as string delimiters.