Help with Order By on Continuous Form (1 Viewer)

rekabeilla

New member
Local time
Today, 17:51
Joined
Apr 18, 2020
Messages
17
Long time reader, first time poster lol

I have a continuous form with 3 fields:
1) ClinicID
2) ClinicDate
3) InsuranceTypeID

I want the main order to be sorted by date BUT if they have a specific insurance (InsuranceTypeID = 12), I want to display those records first, then display the rest of the records by ascending date.

Is this possible?

I've attached a picture of the form for more reference, please ignore my dumb test names
 

Attachments

  • BillingQueue.png
    BillingQueue.png
    63.8 KB · Views: 95

theDBguy

I’m here to help
Staff member
Local time
Today, 15:51
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Maybe something like?

...ORDER BY InsuranceTypeID=12, DateField
 

rekabeilla

New member
Local time
Today, 17:51
Joined
Apr 18, 2020
Messages
17
Hi. Welcome to AWF!

Maybe something like?

...ORDER BY InsuranceTypeID=12, DateField

Yess, that worked! I've been googling this for 2 hours, so I'm very thankful! Thank you!!!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:51
Joined
Oct 29, 2018
Messages
21,358
Yess, that worked! I've been googling this for 2 hours, so I'm very thankful! Thank you!!!
Hi. You're welcome. Glad we could assist. Good luck with your project.
 

rekabeilla

New member
Local time
Today, 17:51
Joined
Apr 18, 2020
Messages
17
Hi. You're welcome. Glad we could assist. Good luck with your project.
Thank you.

One more question, this may be simple as well but in Filter on the property sheet, I'm trying the same syntax. I don't want the form to display "In Office" or "Not Provided" so i tried:

[ClinicName]<>'In Office' Or 'Not Provided'
[ClinicName] Not 'In Office' Or 'Not Provided'

I tried them both it doesn't work or stay in the filter box...

Just as a test i tried:
[ClinicName] = 'In Office'
and that doesnt work either or stay in the filter box either. I also tried without the ' '

What am I doing wrong?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:51
Joined
May 21, 2018
Messages
8,463
Try
me.filter = "[ClinicName] <> 'In Office' OR [ClinicName] <> 'No Provided'"
me.filterOn = true
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:51
Joined
May 21, 2018
Messages
8,463
For a string you defintely need single quotes. No quotes if numeric field. You always have to repeat the field

SomeField = 1 or SomeField = 2
not
SomeField = 1 or 2

Same in VBA
X = 1 or X = 2
not
X = 1 or 2
 

rekabeilla

New member
Local time
Today, 17:51
Joined
Apr 18, 2020
Messages
17
Try
me.filter = "[ClinicName] <> 'In Office' OR [ClinicName] <> 'No Provided'"
me.filterOn = true

That doesn't work either :( but thank you. Idk why it wouldn't work.

Allow Filter = Yes
Filter On Load = Yes

Do you know if there anything else i should check for?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:51
Joined
Oct 29, 2018
Messages
21,358
That doesn't work either :( but thank you. Idk why it wouldn't work.

Allow Filter = Yes
Filter On Load = Yes

Do you know if there anything else i should check for?
Hi. What is the data type of ClinicName?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:51
Joined
May 21, 2018
Messages
8,463
Sorry. I thought you were doing this in code so you can toggle the filter. If you are not changing the filter, simply build the form based on a query.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:51
Joined
May 21, 2018
Messages
8,463
Are you sure the field in ClinicName not Clinic Name or Clinic_Name or a misspelling
 

rekabeilla

New member
Local time
Today, 17:51
Joined
Apr 18, 2020
Messages
17
Are you sure the field in ClinicName not Clinic Name or Clinic_Name or a misspelling
I figured it out!! The cycle was set to "Current Page" and i changed it to "All Records" and that fixed it
 

zeroaccess

Active member
Local time
Today, 17:51
Joined
Jan 30, 2020
Messages
671
Try
me.filter = "[ClinicName] <> 'In Office' OR [ClinicName] <> 'No Provided'"
me.filterOn = true
I've been wondering: is there a functional difference between that and

SQL:
    DoCmd.ApplyFilter , "[ClinicName] <> 'In Office' OR [ClinicName] <> 'No Provided'"
?
 

Users who are viewing this thread

Top Bottom