russell576
Registered User.
- Local time
- Today, 02:01
- Joined
- Sep 19, 2014
- Messages
- 14
I have created a form for the user to choose from one of six reports. After making a choice from a combobox, they click the Print Preview button which runs the On Click Event.
So far I have written procedures for the first three. In each procedure, a Query is created in VBA from a Select statement. The correct report is then opened. I initially created a Query using the usual design tools. I then clicked the SQL to copy and paste this into my VBA code.
For the fourth report, the created Query works perfectly (that is, when I open it, the correct fields are filled with the correct data based on the selection in the form) and the correct form is opened. However, when I use the SQL in code, I keep getting a syntax error.
This is the SQL:
"SELECT Master.MemberID, Master.NameLast, Master.NameFirst, Master.NameFull, " & _
"[NameFirst]+' '+[NameLast] AS FullName, Master.ClassName1, Master.ClassName2, " & _
"Master.DOBMonth, IIf(IsNull([Master]![DOBMonth]),Nz([Master]![DOBMonth],""), " & _
"MonthName([Master]![DOBMonth])) AS NameMonth, Master.DOBDay, Master.Age, [Age]+1 AS AgeNext, " & _
"Master.Left " & _
"FROM Master " & _
"WHERE Master.ClassName2 = [Forms]![frmMenuReports]![cbxShowBirthdays] And Master.Left = False " & _
"ORDER BY Master.NameLast, Master.NameFirst;"
I have a feeling it has something to do with the IIF statement. This is necessary I want the report to Group on the month from the DOB (date of birth) field. As we don't have that detail for every member, an error occurs if the method MonthName is run on an empty field.
I would be grateful is someone could look at this for me an offer some help.
So far I have written procedures for the first three. In each procedure, a Query is created in VBA from a Select statement. The correct report is then opened. I initially created a Query using the usual design tools. I then clicked the SQL to copy and paste this into my VBA code.
For the fourth report, the created Query works perfectly (that is, when I open it, the correct fields are filled with the correct data based on the selection in the form) and the correct form is opened. However, when I use the SQL in code, I keep getting a syntax error.
This is the SQL:
"SELECT Master.MemberID, Master.NameLast, Master.NameFirst, Master.NameFull, " & _
"[NameFirst]+' '+[NameLast] AS FullName, Master.ClassName1, Master.ClassName2, " & _
"Master.DOBMonth, IIf(IsNull([Master]![DOBMonth]),Nz([Master]![DOBMonth],""), " & _
"MonthName([Master]![DOBMonth])) AS NameMonth, Master.DOBDay, Master.Age, [Age]+1 AS AgeNext, " & _
"Master.Left " & _
"FROM Master " & _
"WHERE Master.ClassName2 = [Forms]![frmMenuReports]![cbxShowBirthdays] And Master.Left = False " & _
"ORDER BY Master.NameLast, Master.NameFirst;"
I have a feeling it has something to do with the IIF statement. This is necessary I want the report to Group on the month from the DOB (date of birth) field. As we don't have that detail for every member, an error occurs if the method MonthName is run on an empty field.
I would be grateful is someone could look at this for me an offer some help.