Another SQL Error

[russell576]

Earlier today I posted a thread ('SQL Error') which was answered very promptly. In the same database, on the same form, I have tried to update the last SQL to create the same table but with two amendments.

I want to produce a report of members' birthdays which used the query created in the original SQL (as amended on this forum). However, I couldn't get it to group the months in chronological order as the fields were all strings.

I amended my original query to create two more fields holding the integer values of the months and days, clicked on the SQL view and copied the code into the event procedure.

After a little modification, this is the 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 " & _
", IIf(IsNull(Master.DOBMonth), '0'" & _
", CInt(DOBMonth)) AS MonthNum " & _
", Master.DOBDay " & _
", IIf(IsNull(Master.DOBDay), '0'" & _
", CInt(DOBDay)) AS DayNum " & _
", Master.Age " & _
", [Age]+1 AS AgeNext " & _
", Master.Left " & _
"FROM Master " & _
"WHERE Master.ClassName2 = '" & [Forms]![frmMenuReports]![cbxShowBirthdays] & "' " & _
"And Master.Left = False " & _
"ORDER BY Master.MonthNum, Master.DayNum;"

This time, it's the lines beginning with the function CInt that causes the problem. As before, the query as amended works, that is, I do get another two fields called MonthNum and DayNum calculated from the date of birth strings each containing numbers. But, this code falls over on the last line beginning ORDER. If I comment this out, the code runs but the MonthNum and DayNum fields contain the original strings from the DOBMonth & DOBDay fields respectively (though any fields with a Null value do get a zero) but the fields are all Strings.

So, the lines of code beginning CInt don't created integers.

Hope I've made myself clear and hope someone can help.

 

[pbaldy]

I'm not clear on the problem, but for starters:

"ORDER BY Master.MonthNum, Master.DayNum;"

Those are not fields in the Master table, they were created and aliased in the SQL above. Try the formulas there. Also, take the single quotes off the 0 if you want a numeric value returned. The Nz() function would be simpler:

Nz(Master.DOBMonth, 0)

 

[russell576]

Paul - another pair of eyes always helps! Removing the quotes got me my zeros (integers) and removing the reference to the master table in the ORDER by line of code stopped the error.

However, it has now thrown up another problem. When the query runs, a dialog box appears asking for MonthNum. Leaving it blank and clicking OK gets rid of it but another appears asking for DayNum. Clicking OK dismisses that.

Next, the new query is opened in Datasheet view and the fields are as they should be. However, the two dialog boxes appear again. After clicking OK twice to dismiss them lets the rest of the code run (the form opens) but it is not sorted as per the ORDER code.

 

[pbaldy]

Like I said, try the formulas there instead of the name. Due to the order of execution, you can't use alias names in the ORDER BY clause.

 

[russell576]

I removed the ORDER by line of code which stopped the dialog boxes. I didn't realise you couldn't order by on aliases - I created the table first (using code where necessary), then clicked on 'Sort' in the field. It was after that I clicked on the SQL view to generate the code to use in the Event Procedure.

I have moved the code for those two fields to the bottom before the FROM line but it still doesn't sort the records in chronological order.

The report looks OK and the records are grouped but instead of having January first, the month of the first record in the table is at the top.

 

[pbaldy]

You should be able to have

ORDER BY OneFormula, OtherFormula

where the formulas are the same as used to create the aliased fields.

 

[pbaldy]

Oh, and in reports you need to use Grouping and Sorting to get the desired sort anyway (on the ribbon).