Syntax Error

[DS]

I have this statement that keeps giving me a compile error on
CDDiscountManID so I'm assuming once again the syntax is wrong...it's in the
first two if parts.

Thanks
DS

'DISCOUNTS
If DateValue(Forms!frmReportDates!TxtStart) < DateAdd("yyyy", -1, Date)
And _
DateValue(Forms!frmReportDates!TxtEnd) < DateAdd("yyyy", -1, Date)
Then
'OLD
Set RS = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS
Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblDiscountDetails "
& _
"WHERE CDBizDay BETWEEN " & Format(Forms!frmReportDates!TxtStart,
"\#yyyy\-mm\-dd\#") & _
"AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#" And
CDDiscountManID = Me.EmployeeID), dbOpenSnapshot)

Me.TxtDiscounts = Nz(RS(0), 0)
RS.Close
ElseIf DateValue(Forms!frmReportDates!TxtStart) < DateAdd("yyyy", -1,
Date) And _
DateValue(Forms!frmReportDates!TxtEnd) >= DateAdd("yyyy", -1, Date)
Then
'OLD AND NEW
Set RS = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS
Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblDiscountDetails "
& _
"WHERE CDBizDay BETWEEN " & Format(Forms!frmReportDates!TxtStart,
"\#yyyy\-mm\-dd\#") & _
"AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#" And
CDDiscountManID = Me.EmployeeID), dbOpenSnapshot)

Set RS2 = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"tblDiscountDetails " & _
"WHERE CDBizDay BETWEEN " & Format(Forms!frmReportDates!TxtStart,
"\#yyyy\-mm\-dd\#") & _
"AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#" And
CDDiscountManID = Me.EmployeeID), dbOpenSnapshot)

Me.TxtDiscounts = Nz(RS(0), 0) + Nz(RS2(0), 0)
RS.Close
RS2.Close
ElseIf DateValue(Forms!frmReportDates!TxtStart) >= DateAdd("yyyy", -1,
Date) And _
DateValue(Forms!frmReportDates!TxtEnd) >= DateAdd("yyyy", -1, Date)
Then
'New
Me.TxtDiscounts = Nz(DCount("CDCheckID", "tblDiscountDetails",
"CDBizDay BETWEEN #" & Forms!frmReportDates!TxtStart & "# AND #" &
Forms!frmReportDates!TxtEnd & "# And CDDiscountManID = " & Me.EmployeeID),
0)
End If

 

[Douglas J. Steele]

Assuming CDDiscountManID is a numeric field:

"WHERE CDBizDay BETWEEN " & _
Format(Forms!frmReportDates!TxtStart, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#") & _
" AND CDDiscountManID = " & Me.EmployeeID), dbOpenSnapshot)

If it's text, then

"WHERE CDBizDay BETWEEN " & _
Format(Forms!frmReportDates!TxtStart, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#") & _
" AND CDDiscountManID = '" & Me.EmployeeID & "'"), dbOpenSnapshot)

I've also found sometimes it's necessary to put parentheses around
Between...And...:

"WHERE (CDBizDay BETWEEN " & _
Format(Forms!frmReportDates!TxtStart, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#") & _
") AND CDDiscountManID = " & Me.EmployeeID), dbOpenSnapshot)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have this statement that keeps giving me a compile error on
CDDiscountManID so I'm assuming once again the syntax is wrong...it's in
the first two if parts.

Thanks
DS

'DISCOUNTS
If DateValue(Forms!frmReportDates!TxtStart) < DateAdd("yyyy", -1, Date)
And _
DateValue(Forms!frmReportDates!TxtEnd) < DateAdd("yyyy", -1, Date)
Then
'OLD
Set RS = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS
Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblDiscountDetails
" & _
"WHERE CDBizDay BETWEEN " & Format(Forms!frmReportDates!TxtStart,
"\#yyyy\-mm\-dd\#") & _
"AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#" And
CDDiscountManID = Me.EmployeeID), dbOpenSnapshot)

Me.TxtDiscounts = Nz(RS(0), 0)
RS.Close
ElseIf DateValue(Forms!frmReportDates!TxtStart) < DateAdd("yyyy", -1,
Date) And _
DateValue(Forms!frmReportDates!TxtEnd) >= DateAdd("yyyy", -1, Date)
Then
'OLD AND NEW
Set RS = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS
Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblDiscountDetails
" & _
"WHERE CDBizDay BETWEEN " & Format(Forms!frmReportDates!TxtStart,
"\#yyyy\-mm\-dd\#") & _
"AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#" And
CDDiscountManID = Me.EmployeeID), dbOpenSnapshot)

Set RS2 = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"tblDiscountDetails " & _
"WHERE CDBizDay BETWEEN " & Format(Forms!frmReportDates!TxtStart,
"\#yyyy\-mm\-dd\#") & _
"AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#" And
CDDiscountManID = Me.EmployeeID), dbOpenSnapshot)

Me.TxtDiscounts = Nz(RS(0), 0) + Nz(RS2(0), 0)
RS.Close
RS2.Close
ElseIf DateValue(Forms!frmReportDates!TxtStart) >= DateAdd("yyyy", -1,
Date) And _
DateValue(Forms!frmReportDates!TxtEnd) >= DateAdd("yyyy", -1, Date)
Then
'New
Me.TxtDiscounts = Nz(DCount("CDCheckID", "tblDiscountDetails",
"CDBizDay BETWEEN #" & Forms!frmReportDates!TxtStart & "# AND #" &
Forms!frmReportDates!TxtEnd & "# And CDDiscountManID = " & Me.EmployeeID),
0)
End If

 

[DS]

Douglas,
I used this one. Its Numeric and I'm stILL getting a compile error. It
says variable not defined.

Set RS = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS
Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblDiscountDetails "
& _
"WHERE (CDBizDay BETWEEN " & Format(Forms!frmReportDates!TxtStart,
"\#yyyy\-mm\-dd\#") & _
" AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#")) And
CDDiscountManID = " & Me.EmployeeID), dbOpenSnapshot)"
Me.TxtDiscounts = Nz(RS(0), 0)
RS.Close

Thanks
DS

 

[Douglas J. Steele]

That's not what I'd suggested.

You're missing an ampersand and double quote between the Format statement
and the closing parenthesis.

"WHERE (CDBizDay BETWEEN " & _
Format(Forms!frmReportDates!TxtStart, "\#yyyy\-mm\-dd\#") & _
" AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#") & _
") AND CDDiscountManID = " & Me.EmployeeID), dbOpenSnapshot)

 

[DS]

Thanks Douglas, This is compiling now.
"WHERE (CDBizDay BETWEEN " & Format(Forms!frmReportDates!TxtStart,
"\#yyyy\-mm\-dd\#") & " AND " & Format(Forms!frmReportDates!TxtEnd,
"\#yyyy\-mm\-dd\#") & ") AND CDDiscountManID = " & Me.EmployeeID,
dbOpenSnapshot)
DS