SQL Error (1 Viewer)

russell576 · Sep 19, 2014

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.

pr2-eugin · Sep 19, 2014

Try this,

Code:

"SELECT Master.MemberID, Master.NameLast, Master.NameFirst, Master.NameFull, " & _
"[NameFirst]+' '+[NameLast] AS FullName, Master.ClassName1, Master.ClassName2, " & _
"Master.DOBMonth, IIF(IsNull(Master.DOBMonth), "", MonthName(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;"

russell576 · Sep 19, 2014

Thank you for your help but it is still giving me the same syntax error message.

I think the way round it would be to alter the Master table to contain and empty field for the Month rather than a Null value. Also, I'll run the the MonthName method in the code used to create the date of birth on the original form.

namliam · Sep 19, 2014

Problem #1, your code is unreadable, so lets fix that first.

Code:

" SELECT Master.MemberID " & _
      ", Master.NameLast " & _
      ", Master.NameFirst " & _
      ", Master.NameFull " & _
      ", [NameFirst]+' '+[NameLast] AS FullName " & _
      ", Master.ClassName1 " & _
      ", Master.ClassName2 " & _
      ", Master.DOBMonth " & _
      ", IIF(IsNull(Master.DOBMonth), [COLOR="Red"]""[/COLOR] " & _
                                   ", MonthName(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;"

The problem indeed is in the IIF, try replacing the red "" by either """" or ''

russell576 · Sep 19, 2014

Dear namliam

Your changes worked - it needed """"" to replace the characters highlighted in red.

Thank you so much.

Wish I'd joined this forum earlier! Having said that, you learn better by trying to work things out yourself but this really had me stumped.

namliam · Sep 19, 2014

Just make sure to not only fix this problem but also fix your #1 problem

SQL Error (1 Viewer)

russell576

Registered User.

pr2-eugin

Super Moderator

russell576

Registered User.

namliam

The Mailman - AWF VIP

russell576

Registered User.

namliam

The Mailman - AWF VIP

Similar threads

Users who are viewing this thread