0

I'm getting a syntax error (missing operator) in my sql statement below and I can't seem to figure out what is wrong.

DoCmd.OpenForm "frmEditPatientFollowUpVisit", acNormal, , "ID= '" & txtID.Value & "' ORDER BY Visit", acFormEdit, acWindowNormal

It works if I remove the 'ORDER BY Visit' but I would like this in to ensure that the records appear in the correct order.

Can anyone see what is wrong?

Thanks!

VT555
  • 91
  • 1
  • 4
  • 14

2 Answers2

1

The OpenForm method doesn't provide a way to specify the sorting. The string where you have put the order by is the filter condition, it can't contain anything other than the condition.

If you want to sort the data you have to do that after you have fetched it, or use a different way of fetching it.

Related: Access VBA OpenForm Grouping and Sorting

Community
  • 1
  • 1
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
1

Victoria, try the SetOrderBy method as documented here:

http://msdn.microsoft.com/en-us/library/office/ff844761(v=office.15).aspx

Something like:

DoCmd.SetOrderBy "Visit"
Jason Barrett
  • 239
  • 1
  • 6