Order continuous forms by text box value 2

[djayam]

Hi all,

I have a continuous form based on a table (Deliveries). I have created a text box with a control of "WeekDay(ShipDate,2)" and I want to order by this. If I put either the text box name or the expression into the Order By property I get asked for the variable on opening the form...!!

On a slightly related point - I have a number field in the deliveries table called [DeliveryDay]. I have tried to create an update query that updates this field to "WeekDay(ShipDate,2) but I get a data type mismatch!!??

Any ideas on either of these?

Cheers,

Jason

 

[TheAceMan1]

How are ya djayam . . .

Realize . . . if you sort this way all of the same days will be grouped together! . . . that is All Sunday's, Mondays, ect.

[blue]Is this what you really want?[/blue]

See Ya! . . . . . .

 

[djayam]

Hi AceMan,

Yep - that's what I want! The client wants a list of deliveries due this week and they need to be grouped in days. They also need to be directly editable in case any were delayed, hence the form is based on the table directly and not a query (which would have been easy to order!)

Cheers mate.

 

[TheAceMan1]

djayam . . .

Who said queries were not editable?

See Ya! . . . . . .

 

[djayam]

...err...a little beyond my capabilities!? Doesn't mean I might not have been better off doing it that way first...but I've done the form based on the table now! Is there a way to sort it by WeekDay?

 

[djayam]

You abandoned me, AceMan?

 

[BoxHead]

djayam,

A couple of things:

The point I believe AceMan was making is that if you sort the way that you are trying to sort, it will show all deliveries for Monday - not just this Monday.

If it's Monday and you have a delivery scheduled for a Monday six years from now, it will show up on the form before tomorrows' deliveries are displayed.

To base the form on a query is still very simple.
1. Make a backup of the form
In database window, highlight the form name, on the keyboard hit CTRL+C then hit CTRL+V. You'll be prompted for a new form name. Use the existing form name with a "BK" at the end.

2. Make a query based on the table
On the database window's Queries tab, double-click Create Query using Wizard. Select the name of your table and add all fields. Click OK. Name the query 'qryDeliveries'.

3. Change the form's Record Source
Open your form in design view and display the properties window. If it isn't already showing the properties for the form itself, click on the top left hand corner of the form. Change the Record Source from 'Deliveries' to 'qryDeliveries'.

Open the form in Form View.

Close the form and open the new query in design view. Sort as necessary and/or add your field WeekDay(ShipDate,2) to the query and sort by that.


HTH

 

[djayam]

Hi BoxHead - thanks for stepping in.

I was aware of all deliveries being there - I had a filter based on a date range selection in another form.

I didn't realise that when writing a query based directly on a table and using it to populate a form you could update the fields and the table would be updated. So simple...thanks loads for pointing it out. And cheers again AceMan for your time mate.

Laters,

Jason

 

[TheAceMan1]

djayam . . .

Sorry about the delay. I moved and my service provider promised to install my cable the next day . . . needless to say :-(

Thank you [blue]BoxHead[/blue] for filling in . . . ;-)

See Ya! . . . . . .

 

[djayam]

No worries AceMan - glad you got it sorted.