Sorting date in subform

[Guest]

I have a subform based on a query. I am trying to filter and sort a date
field in one of the fileds on the subform (Interview_Date). I am using input
mask dd-mmm-yyyy on a text field. The field is being sorted by dd but knows
nothing about month (mmm placing 08-Oct-2007 ahead of 09-Sep-2007). how do i
fix this?

Private Sub Filter_Click()
Dim FrmS As Access.Form

Set SF = Me.[subfrmProspectFollowUp].Form ' Reference the Subform
through it's *control*

Select Case Me.[ReminderOptions].Value
Case 1 ' Show all Records
DoCmd.ShowAllRecords
SF.OrderBy = "[Last_Name] ASC"
SF.OrderByOn = True
Case 2 ' FollowUp not checked and no interview date
SF.Filter = "[FollowUp_Req] = No and [Interview_Date] Is Null"
SF.FilterOn = True
SF.OrderBy = "[Initial_Call_Date] ASC"
SF.OrderByOn = True
Case 3 'Filter on Interview Date and sort ASC
SF.Filter = "[Interview_Date] Is Not Null"
SF.FilterOn = True
SF.OrderBy = "[Interview_Date] ASC"
SF.OrderByOn = True

End Select
End Sub

 

[John W. Vinson]

I have a subform based on a query. I am trying to filter and sort a date
field in one of the fileds on the subform (Interview_Date). I am using input
mask dd-mmm-yyyy on a text field. The field is being sorted by dd but knows
nothing about month (mmm placing 08-Oct-2007 ahead of 09-Sep-2007). how do i
fix this?

Private Sub Filter_Click()
Dim FrmS As Access.Form

Set SF = Me.[subfrmProspectFollowUp].Form ' Reference the Subform
through it's *control*

Select Case Me.[ReminderOptions].Value
Case 1 ' Show all Records
DoCmd.ShowAllRecords
SF.OrderBy = "[Last_Name] ASC"
SF.OrderByOn = True
Case 2 ' FollowUp not checked and no interview date
SF.Filter = "[FollowUp_Req] = No and [Interview_Date] Is Null"
SF.FilterOn = True
SF.OrderBy = "[Initial_Call_Date] ASC"
SF.OrderByOn = True
Case 3 'Filter on Interview Date and sort ASC
SF.Filter = "[Interview_Date] Is Not Null"
SF.FilterOn = True
SF.OrderBy = "[Interview_Date] ASC"
SF.OrderByOn = True

End Select
End Sub

If you're using the Format() *function* to format the date, don't. It converts
the date/time field into a String - and 09-Oct sorts before 09-Sep because the
letter O sorts before the letter S.

Just use Interview_Date as the actual date/time field and set the Format
property of the form textbox.

John W. Vinson [MVP]

 

[Guest]

i'm not sure of the Format syntax - can you help me with that.

I have a subform based on a query. I am trying to filter and sort a date
field in one of the fileds on the subform (Interview_Date). I am using input
mask dd-mmm-yyyy on a text field. The field is being sorted by dd but knows
nothing about month (mmm placing 08-Oct-2007 ahead of 09-Sep-2007). how do i
fix this?

Private Sub Filter_Click()
Dim FrmS As Access.Form

Set SF = Me.[subfrmProspectFollowUp].Form ' Reference the Subform
through it's *control*

Select Case Me.[ReminderOptions].Value
Case 1 ' Show all Records
DoCmd.ShowAllRecords
SF.OrderBy = "[Last_Name] ASC"
SF.OrderByOn = True
Case 2 ' FollowUp not checked and no interview date
SF.Filter = "[FollowUp_Req] = No and [Interview_Date] Is Null"
SF.FilterOn = True
SF.OrderBy = "[Initial_Call_Date] ASC"
SF.OrderByOn = True
Case 3 'Filter on Interview Date and sort ASC
SF.Filter = "[Interview_Date] Is Not Null"
SF.FilterOn = True
SF.OrderBy = "[Interview_Date] ASC"
SF.OrderByOn = True

End Select
End Sub

If you're using the Format() *function* to format the date, don't. It converts
the date/time field into a String - and 09-Oct sorts before 09-Sep because the
letter O sorts before the letter S.

Just use Interview_Date as the actual date/time field and set the Format
property of the form textbox.

John W. Vinson [MVP]

 

[Guest]

I tried putting "dd-mmm-yyyy" in the *Format* of the Interview_Date but it
produces 12Oct2007 with no "-" when the query is run. It also does not sort
correctly. any help is appreciated.

i'm not sure of the Format syntax - can you help me with that.

I have a subform based on a query. I am trying to filter and sort a date
field in one of the fileds on the subform (Interview_Date). I am using input
mask dd-mmm-yyyy on a text field. The field is being sorted by dd but knows
nothing about month (mmm placing 08-Oct-2007 ahead of 09-Sep-2007). how do i
fix this?

Private Sub Filter_Click()
Dim FrmS As Access.Form

Set SF = Me.[subfrmProspectFollowUp].Form ' Reference the Subform
through it's *control*

Select Case Me.[ReminderOptions].Value
Case 1 ' Show all Records
DoCmd.ShowAllRecords
SF.OrderBy = "[Last_Name] ASC"
SF.OrderByOn = True
Case 2 ' FollowUp not checked and no interview date
SF.Filter = "[FollowUp_Req] = No and [Interview_Date] Is Null"
SF.FilterOn = True
SF.OrderBy = "[Initial_Call_Date] ASC"
SF.OrderByOn = True
Case 3 'Filter on Interview Date and sort ASC
SF.Filter = "[Interview_Date] Is Not Null"
SF.FilterOn = True
SF.OrderBy = "[Interview_Date] ASC"
SF.OrderByOn = True

End Select
End Sub

If you're using the Format() *function* to format the date, don't. It converts
the date/time field into a String - and 09-Oct sorts before 09-Sep because the
letter O sorts before the letter S.

Just use Interview_Date as the actual date/time field and set the Format
property of the form textbox.

John W. Vinson [MVP]

 

[John W. Vinson]

I tried putting "dd-mmm-yyyy" in the *Format* of the Interview_Date

In the Interview_Date of... what!?

I was suggesting that you leave the date *unformatted* in your table and in
your query, and specify the Format in the properties of the textbox in which
the date is displayed. If you're using the query datasheet to display and work
with data... you now know yet another reason why this is a bad idea; it's very
limited in functionality.

but it
produces 12Oct2007 with no "-" when the query is run. It also does not sort
correctly. any help is appreciated.

Please post the SQL view of the query.

John W. Vinson [MVP]

 

[Guest]

I dont have the field formatted in the table or the query. I only have an
input mask.
The field in question [Interview_Date] is in a subform. I'm trying to change
the Format property of [Interview_Date] in the subform but the syntax is not
working.

Here is the query:

SELECT tblClients.ClientNum, tblClients.Sal, tblClients.First_Name,
tblClients.Last_Name, tblClients.Home_Phone, tblClients.Home_Phone2,
tblClients.Client_DoB, tblClients.Employ_Phone, tblClients.Employ_Ph_Ext,
tblClients.[Client Status], tblClients.Age, tblClients.[Client Since],
tblClients.Gender, tblClients.SIN, tblClients.Dependants,
tblClients.Family_Stat, tblClients.Spouse, tblClients.Home_Email,
tblClients.Cell_Phone, tblClients.Employ_Name, tblClients.Employ_Occupation,
tblClients.Employ_Income, tblClients.Employ_City, tblClients.Employ_Fax,
tblClients.Employ_Email, tblClients.MI_AgeGroup, tblClients.MI_RLE,
tblClients.MI_Home, tblClients.MI_Dependents, tblClients.Hobbies,
tblClients.Known_By, tblClients.Referred_By, tblClients.Decline_Reason,
tblAddress.Home_Str_No, tblAddress.Home_Str_Name,
tblAddress.[Home_Suite-Apt], tblAddress.Home_City, tblAddress.Home_Prov,
tblAddress.Home_Postal, tblAddress.Home_Fax, tblAddress.MoveIn_Date,
tblCommLog.ClientNum, tblCommLog.Initial_Call_Date,
tblCommLog.Follow_Up_Date, tblCommLog.FollowUp_Req, tblCommLog.FollowUp_Meth,
tblCommLog.Interview_Date, tblCommLog.UW_Submit_Date,
tblCommLog.Paramedical_Date, tblCommLog.ClientSignoff_Date,
tblCommLog.ClientSignoff, tblCommLog.Prospect_Notes
FROM (tblAddress INNER JOIN tblClients ON tblAddress.Home_Addr_PK =
tblClients.Home_Addr_PK) INNER JOIN tblCommLog ON tblClients.ClientNum =
tblCommLog.ClientNum
WHERE (((tblClients.[Client Status])="Prospect"));

 

[John W. Vinson]

I dont have the field formatted in the table or the query. I only have an
input mask.
The field in question [Interview_Date] is in a subform. I'm trying to change
the Format property of [Interview_Date] in the subform but the syntax is not
working.

Are you using a Datasheet for the subform? It might be simpler if you change
the default view of the subform to Continuous Forms; move the textboxes and
other controls up to the top of the window, jammed together, and you'll get
something that looks very like a datasheet but gives you more control. In
particular the textbox to which the field Interview_Date is bound has a Format
property that you can set.

You don't say what syntax you're using to set the format so I am not sure how
to suggest that you change it!

John W. Vinson [MVP]

 

[Guest]

Yes, i'm using a datasheet as the subform. I dont wish to use continuous
forms for this. The subform has numerous fields and you can click on them in
Design view, you can see all of the properties. One of the properties is
*Format* and i tried entering dd-mmm-yyyy (plus others trying to get it to
format) and no matter what i put in the Format field the display in the
subform is always ddmmmyyyy (no "-") and it doesn't sort properly. I tried
removing the mask that is there on the control and that didn't work either. I
tried putting the sort on the query and that didn't work. Is there a way to
format the bound field (Interview_Date) such that is sorts correctly in the
VBA (see my original post) ? Should i try an unbound control in the
subform??? really perplexed now.

I dont have the field formatted in the table or the query. I only have an
input mask.
The field in question [Interview_Date] is in a subform. I'm trying to change
the Format property of [Interview_Date] in the subform but the syntax is not
working.

Are you using a Datasheet for the subform? It might be simpler if you change
the default view of the subform to Continuous Forms; move the textboxes and
other controls up to the top of the window, jammed together, and you'll get
something that looks very like a datasheet but gives you more control. In
particular the textbox to which the field Interview_Date is bound has a Format
property that you can set.

You don't say what syntax you're using to set the format so I am not sure how
to suggest that you change it!

John W. Vinson [MVP]

 

[Marshall Barton]

I dont have the field formatted in the table or the query. I only have an
input mask.
The field in question [Interview_Date] is in a subform. I'm trying to change
the Format property of [Interview_Date] in the subform but the syntax is not
working.

Are you using a Datasheet for the subform? It might be simpler if you change
the default view of the subform to Continuous Forms; move the textboxes and
other controls up to the top of the window, jammed together, and you'll get
something that looks very like a datasheet but gives you more control. In
particular the textbox to which the field Interview_Date is bound has a Format
property that you can set.

You don't say what syntax you're using to set the format so I am not sure how
to suggest that you change it!

PMJI, John, but have you guys established that this really
is a date field and not a Text field?

 

[John W. Vinson]

Yes, i'm using a datasheet as the subform. I dont wish to use continuous
forms for this. The subform has numerous fields and you can click on them in
Design view, you can see all of the properties. One of the properties is
*Format* and i tried entering dd-mmm-yyyy (plus others trying to get it to
format) and no matter what i put in the Format field the display in the
subform is always ddmmmyyyy (no "-") and it doesn't sort properly. I tried
removing the mask that is there on the control and that didn't work either. I
tried putting the sort on the query and that didn't work. Is there a way to
format the bound field (Interview_Date) such that is sorts correctly in the
VBA (see my original post) ? Should i try an unbound control in the
subform??? really perplexed now.

I think Marshall's got the right answer here. Open the Table in design view.
What is the Datatype of this field? I strongly suspect that it is indeed a
text string - which to Access is *JUST* a text string, and which will not be
interpreted as a date, and will not respond to date formats!

You'll need to create a new date field in the table, and run an Update query
updating it to

CDate(Left([Interview_Date], 2) & "-" & Mid([Interview_Date], 3, 3) & "-" &
Right([Interview_Date], 4))

if it is in fact a text field.

John W. Vinson [MVP]

 

[Guest]

Hi guys, my first post indicated it was a *text string*....
So i create a new field in the table [srtIntDate], add it the query and i
make the control source of [srtIntDate] the following expression in the
subform:

CDate(Left([Interview_Date], 2) & "-" & Mid([Interview_Date], 3, 3) & "-" &
Right([Interview_Date], 4))

Do i have this correct? Is [srtIntDate] a date field in the table? (ie. not
text)

Yes, i'm using a datasheet as the subform. I dont wish to use continuous
forms for this. The subform has numerous fields and you can click on them in
Design view, you can see all of the properties. One of the properties is
*Format* and i tried entering dd-mmm-yyyy (plus others trying to get it to
format) and no matter what i put in the Format field the display in the
subform is always ddmmmyyyy (no "-") and it doesn't sort properly. I tried
removing the mask that is there on the control and that didn't work either. I
tried putting the sort on the query and that didn't work. Is there a way to
format the bound field (Interview_Date) such that is sorts correctly in the
VBA (see my original post) ? Should i try an unbound control in the
subform??? really perplexed now.

I think Marshall's got the right answer here. Open the Table in design view.
What is the Datatype of this field? I strongly suspect that it is indeed a
text string - which to Access is *JUST* a text string, and which will not be
interpreted as a date, and will not respond to date formats!

You'll need to create a new date field in the table, and run an Update query
updating it to

CDate(Left([Interview_Date], 2) & "-" & Mid([Interview_Date], 3, 3) & "-" &
Right([Interview_Date], 4))

if it is in fact a text field.

John W. Vinson [MVP]

 

[John W. Vinson]

Hi guys, my first post indicated it was a *text string*....
So i create a new field in the table [srtIntDate], add it the query and i
make the control source of [srtIntDate] the following expression in the
subform:

CDate(Left([Interview_Date], 2) & "-" & Mid([Interview_Date], 3, 3) & "-" &
Right([Interview_Date], 4))

Do i have this correct? Is [srtIntDate] a date field in the table? (ie. not
text)

No.

Putting a control on a form does NOT automagically fill in all the existing
records in your table; and setting the control source of a control on a form
to an expression does not load data into your table.

Do you need to edit this interview date? If so, I'd really, really suggest
changing the table definition to a date/time field. Jumping back and forth
from text to date is a major pain.

John W. Vinson [MVP]

 

[Guest]

If i go back to my orginal requirement, it is to be able to sort a date in a
subform.
Given the bound field [Interview_date] is a text string with an input mask
and is already in the table, i dont really need another field with the exact
data in the table. (do i?) Additionaly, I dont need to update the field in
the subform - its only a visual read only for the user. I do need to sort and
filter as part of the main form Option Group.

Hi guys, my first post indicated it was a *text string*....
So i create a new field in the table [srtIntDate], add it the query and i
make the control source of [srtIntDate] the following expression in the
subform:

CDate(Left([Interview_Date], 2) & "-" & Mid([Interview_Date], 3, 3) & "-" &
Right([Interview_Date], 4))

Do i have this correct? Is [srtIntDate] a date field in the table? (ie. not
text)

No.

Putting a control on a form does NOT automagically fill in all the existing
records in your table; and setting the control source of a control on a form
to an expression does not load data into your table.

Do you need to edit this interview date? If so, I'd really, really suggest
changing the table definition to a date/time field. Jumping back and forth
from text to date is a major pain.

John W. Vinson [MVP]

 

[John W. Vinson]

If i go back to my orginal requirement, it is to be able to sort a date in a
subform.
Given the bound field [Interview_date] is a text string with an input mask
and is already in the table, i dont really need another field with the exact
data in the table. (do i?) Additionaly, I dont need to update the field in
the subform - its only a visual read only for the user. I do need to sort and
filter as part of the main form Option Group.

Ok... you don't want to be able to use Access' built in date validity
checking, you want to allow people to enter 45-XEZ-2155 in the field (or else
put in some elaborate validation rules), you want to use an inefficient
computed value to sort the data...

WHY!?

What's your objection to storing date data in a date field?

But ok... you can create a calculated field in your Query. The input mask is
*totally irrelevant* and plays no role here. If Interview_Date is stored in a
text field of the format ##AAA#### you can use

SortDate: CDate(Format([Interview_Date], "@@-@@@-@@@@"))

and use this date for sorting and filtering. Of course any index on
Interview_Date will be useless (since you're converting the value prior to
sorting or searching), but it will still let you do a sort or search - just
slower.

John W. Vinson [MVP]

 

[Guest]

Hi John, thanks for all your advise...it took a little bit of work but i
decided to change all date fields from *Text* to *Date* control and then i
added *Medium Date* as the format for the control. Everything is fine now and
i can sort correctly in the subform.

If i go back to my orginal requirement, it is to be able to sort a date in a
subform.
Given the bound field [Interview_date] is a text string with an input mask
and is already in the table, i dont really need another field with the exact
data in the table. (do i?) Additionaly, I dont need to update the field in
the subform - its only a visual read only for the user. I do need to sort and
filter as part of the main form Option Group.

Ok... you don't want to be able to use Access' built in date validity
checking, you want to allow people to enter 45-XEZ-2155 in the field (or else
put in some elaborate validation rules), you want to use an inefficient
computed value to sort the data...

WHY!?

What's your objection to storing date data in a date field?

But ok... you can create a calculated field in your Query. The input mask is
*totally irrelevant* and plays no role here. If Interview_Date is stored in a
text field of the format ##AAA#### you can use

SortDate: CDate(Format([Interview_Date], "@@-@@@-@@@@"))

and use this date for sorting and filtering. Of course any index on
Interview_Date will be useless (since you're converting the value prior to
sorting or searching), but it will still let you do a sort or search - just
slower.

John W. Vinson [MVP]

 

[John W. Vinson]

Hi John, thanks for all your advise...it took a little bit of work but i
decided to change all date fields from *Text* to *Date* control and then i
added *Medium Date* as the format for the control. Everything is fine now and
i can sort correctly in the subform.

Thanks, Chuck, and my apologies for yelling at you.

John W. Vinson [MVP]