Help with Order By on Continuous Form

[rekabeilla]

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

 

[theDBguy]

Hi. Welcome to AWF!

Maybe something like?

...ORDER BY InsuranceTypeID=12, DateField

 

[rekabeilla]

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]

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]

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]

Try
me.filter = "[ClinicName] <> 'In Office' OR [ClinicName] <> 'No Provided'"
me.filterOn = true

 

[MajP]

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]

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]

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]

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.

 

[rekabeilla]

Sorry. I thought you were doing this in code.

I tried it in code and it didnt work either.

 

[rekabeilla]

Hi. What is the data type of ClinicName?

Short Text

 

[MajP]

Are you sure the field in ClinicName not Clinic Name or Clinic_Name or a misspelling

 

[rekabeilla]

Are you sure the field in ClinicName not Clinic Name or Clinic_Name or a misspelling

Yes, I just double checked it's ClinicName

 

[rekabeilla]

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

 

[rekabeilla]

Thank you both for trying to help. I really appreciate it!

 

[theDBguy]

Thank you both for trying to help. I really appreciate it!

Glad to hear you got it sorted out. Cheers!

 

[zeroaccess]

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

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

?