Syntax issue with Function

[Cafe2UEMP]

Hi



I am trying to create a function to compare sales to date in this financial year compared with last financial year.

My function to get this years result is:

Public Function FinYTDCash() As Currency
Dim EndDate As Date
Dim StartDate As Date
Dim ThisYear As Integer


ThisYear = Year(Date)
EndDate = (Date)
StartDate = DateSerial(Year(Date), 7, 1)


FinYTDCash = DSum("[Cash]", "QrySalesFiscalandCal", "[date1] BETWEEN #" & StartDate & "# AND #" & EndDate & "# " And _
"[FY] = '" & ThisYear & "'")


End Function

Each criteria works individually but not when I combine them leading me to think the syntax is wrong.

Any assistance would be greatly appreciated.

Thanks

David

[RuralGuy]

Let's look at the 3rd argument:
"[date1] BETWEEN #" & StartDate & "# AND #" & EndDate & "# " And _
"[FY] = '" & ThisYear & "'")
...removing the line continuation...
"[date1] BETWEEN #" & StartDate & "# AND #" & EndDate & "# " And "[FY] = '" & ThisYear & "'"
see anything wrong?

[Cafe2UEMP]

Let's look at the 3rd argument:
"[date1] BETWEEN #" & StartDate & "# AND #" & EndDate & "# " And _
"[FY] = '" & ThisYear & "'")
...removing the line continuation...
"[date1] BETWEEN #" & StartDate & "# AND #" & EndDate & "# " And "[FY] = '" & ThisYear & "'"
see anything wrong?

Sorry but no. I still cannot see the problem

[Minty]

Ranman is pointing out that your use of the & and AND aren't escaped correctly. The DSum should be

DSum("[Cash]", "QrySalesFiscalandCal", "[date1] BETWEEN #" & StartDate & "# AND #" & EndDate & "#  And [FY] = " & ThisYear )

Note that this year is an integer so I've removed the quotes around it.

[Cafe2UEMP]

Ranman is pointing out that your use of the & and AND aren't escaped correctly. The DSum should be

DSum("[Cash]", "QrySalesFiscalandCal", "[date1] BETWEEN #" & StartDate & "# AND #" & EndDate & "#  And [FY] = " & ThisYear )

Note that this year is an integer so I've removed the quotes around it.

Thanks for this. Unfortunately, it still produces a syntax error

[RuralGuy]

Copy and paste EXACTLY what you have please.