Sorting a report by First or Last Name from a form

[timnicholls]

Hi,

I wish to have an option or checkbox on a form.
Who's choices are:

1) First Name & Surname
2) Surname & First Name

After having selected 1 or 2, a report is chosen from a combobox.

Having selected 1, the report will be sorted by first name, and have the names in that order (ie) Bill Blog

Having selected 2, the report will be sorted by last name, and have the names in that order (ie) Blog Bill

So...is this done is a query or in some VB...either way I could use some help.

Thank You
Tim

 

[KenReay]

Hi

Report Sort Order is dependant on the .OrderBy and .OrderByOn properties of the report, you need to set the relevant columns into the .OrderBy property depending on the selection on the form

this needs to be done in the Report Open event

is your VBA good enough to do this, or do you need sample code ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[timnicholls]

Ken,

Thanks for your reply.
No my VB is not up to scratch, and yes I would be much obliged if you could provide some sample code.

Do you mean doing what I wish is possible???
<stunned look on face>

Thank You
Tim

 

[KenReay]

Hi

Yes, it is quite Possible

Let us assume you have an Option Box (opgSort) on a form MyForm with possible values 1 = FirstName, Secondname and 2 = Secondname, FirstName

Let us assume the columns in the table are called FirstName and SecondName

In the On Open Event of the report put code so

If Forms!MyForm!opgSort = 1 Then
Me.OrderBy = &quot;[FirstName], [SecondName]&quot;
Else
Me.OrderBy = &quot;[SecondName], [FirstName]&quot;
End if
Me.OrderByOn = True

The form MyForm must be open at the time the report runs


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[timnicholls]

Thanks Ken,

This is my code:

If Forms!ClientSwitchboard!optChangeName = 1 Then
Me.OrderBy = &quot;[First Name],[Surname]&quot;
Else
Me.OrderBy = &quot;[Surname], [First Name]&quot;
End If
Me.OrderByOn = True

OK, so should the sorting and grouping box available in design view on the report be all blank??

I ask because I have a sorting group before the &quot;Names&quot; level. I should have told you!!

I first sort by &quot;Fund Code&quot; then under those codes, there are names that I wish to have sorted. This is where the code comes in.

Once I have the names sorted as I wish how will it change from Bill Blogs to Blogs Bill on the actual report.
The textboxes holding the names are physically placed there? Maybe that can come later

Thanks
Tim

 

[timnicholls]

Ken,

With you code I have the Sorting working.
I left the &quot;Fund Codes&quot; Grouping there and it worked!
Thank You

As to the second part making it appear as Bill Blogs then Blogs Bill as per the sort selection, do you have any thoughts.

An IIF statement perhaps??
In the textbox holding both the names?
IIF (optChangeName = 1, [First Name]& &quot; &quot; &[Surname], [Surname] & &quot; &quot; [First Name]) ???

Can you reference a control on a form (optChangeName) on a form?

Thanks Again
Tim

 

[KenReay]

Hi

Yes the Sorting in the report should be blank as far as I know, at least that is the only way I have actually tried it before

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[timnicholls]

Ken,

With you code I have the Sorting working.
I left the &quot;Fund Codes&quot; Grouping there and it worked!
Thank You

As to the second part making it appear as Bill Blogs then Blogs Bill as per the sort selection, do you have any thoughts.

An IIF statement perhaps??
In the textbox holding both the names?
IIF (optChangeName = 1, [First Name]& &quot; &quot; &[Surname], [Surname] & &quot; &quot; [First Name]) ???

Can you reference a control on a form (optChangeName) on a report?

Thanks Again
Tim

 

[KenReay]

Hi

Yes, but you would have to qualify it with the Form name, so

IIF (Forms!MyForm!optChangeName = 1, [First Name]& &quot; &quot; &[Surname], [Surname] & &quot; &quot; [First Name]) ???


just like we did in the original code, only rule is form must be open


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[timnicholls]

Ken,

You are a certified Genius

I started 2 hrs ago not knowing if it could actually be done. I am now finished! (This problem at least!)

Thanks for all your help.

All the best

Tim

 

[KenReay]

Hi

I am pleased you are pleased!

Are you in the UK?

If yes you can go home and set your fireworks off!

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[timnicholls]

I am in Australia. Its 2:20 in the AM. So its a good time for lighting fireworks. Unfortunately our country has banned them in almost all states.

At 36yrs I can remember a time when we actually had sky candles, sky rockets etc....

Ahhh the memories...I hope you enjoy yours!

I am still pleased, and you should be too. The Access project is a gratis thing for a Disability group in Victoria. They need help and I (with your help even though you didn't know it) are doing something for them...

Thanks again

Tim Nicholls
Geelong, Victoria
Australia