Date Problem!

[legs00]

In one of my access forms, I have a ComboBox that contains Weeks which come from a table in my access database. In the ComboBox there is a blank entry which allows the user to choose it and it returns all the weeks in that table.

My code is as follows:

cmbWeekReject.RowSource = "SELECT Week FROM RejectTimeSheet
UNION SELECT ' ' FROM
RejectTimeSheet"


I set the format of my ComboBox to MEDIUM DATE and the field WEEK in the database is also MEDIUM DATE. The problem is that once the recordsource is set I get a SHORT DATE format and not a medium date format in the ComboBox!! Shouldn't the SQL query statement return the same format as in the table??

Please does anyone have any idea!!!
Legs

 

[tomschafer]

Try this...

cmbWeekReject.RowSource = "SELECT format(Week, "Medium Date&quot FROM RejectTimeSheet
UNION SELECT ' ' FROM
RejectTimeSheet"

 

[legs00]

Hi Pezamystik,

I tried what you suggested but i still don't get what I want. When I initially open the form, the value in my combo box has Medium Date format and so does the values when I drop dowm the combo box. BUT when I select one of the dates the value I get in the combo box is Short Date format.

I tried to define my input mask to &quot;99\->L<LL\-0000&quot;, which gives me the correct format but I get an error message saying that &quot;The value entered isn't valid for this field. That maybe a text was entered instead of numeric or that that the number is larger than the field size.&quot;!!!

What do you think about this? Do you have any idea what I can do???

Legs

 

[james33]

Dear legs00
Have you checked Start|Settings|Control Panel|Regional Settings|Date
To see what the format is set to there?
HTH

 

[legs00]

Thanks alot James33!! You are fantastic!!!;-)

 

[james33]

Thanks very much for the compliment LegsOO
But make sure you check any other forms that you use dates on and....
If you put the DB on another Computer make sure that it is set similarly.
Regards Jim

 

[legs00]

James
I have another question for you. Everything works fine with my UNION and Date but now I would like to order my date for past to present. Although what I get is:

02-Nov-2001
03-Aug-2001
10-Feb-2002
27-Dec-2001

I have a feeling the reason why the order by doesn't work is because of the UNION!!!!!!

Do you know if there is a way to fix this or another way to get a blank choice in the combo box so the user has a way to retrieve all the records in the table.

Legs

 

[james33]

Dear Legs,
It is sorting by day of month at the moment,
(Pause while I Think to myself)
??
......
Where is the ORDER BY Statement?

and I have to admit with the UNION in SQL, I'm getting rapidly out of my depth.

It might be a good idea to post it again as a SQL problem and the circling SQL sharks will pounce on it.

(I'm only continuing with my watery metaphors 'they' are actually lovely people! None better!)

Sorry I couldn't take you any further
Good Luck
Jim

 

[tomschafer]

To add an order by to a union query add the order by to the last sql statement....

cmbWeekReject.RowSource = &quot;SELECT Week FROM RejectTimeSheet
UNION SELECT ' ' FROM
RejectTimeSheet order by week desc&quot;

 

[legs00]

Hi Jim,

I tried what you suggested but I get this error message:

The ORDER BY expression(WEEK) includes fields that are not selected by the query. Only those fields in the first query can be ordered in an ORDER BY expression.

Therefore I rearranged the query to,

cmbWeekReject.RowSource = &quot;SELECT Week FROM RejectTimeSheet
order by week desc UNION
SELECT ' ' FROM RejectTimeSheet &quot;


But this doesn't change anything, I still have my original problem. It just orders the day of the date and not the date as a whole.
Is there a way of fixing this?? Does it have to do with SQL or Access?

Legs

 

[james33]

Legs,
The last post was by Pezamystik not me,
Who seems to be more able to answer the Question!
I am confused by the

UNION SELECT ' ' FROM RejectTimeSheet order by week desc

What is this selecting?
Regards Jim

 

[legs00]

Sorry about that! Pezamystik, my last reply was for you.
As for Jim, the reason I am using &quot;UNION Select '' From RejectTimeSheet&quot; is so I can get an empty entry in my combo box. Usually you would see '(All)' as the first entry in the combo box, but I can't put (ALL) because it is a text format and not a date format. I tried that one.

Thanks
Legs

 

[james33]

I suppose the point I am making is that I wanted to know what the UNION is of
From Access Help I get this:
Use aliases only in the first SELECT statement because they are ignored in any others. In the ORDER BY clause, refer to fields by what they are called in the first SELECT statement

i.e What tables and /or Queries are involved in the union and are there duplicates that the predicate ALL would remove or are you just talking about a possible entry in the Text box that it would not support due to the Format?

Could you not format the combo to be blank or disabled with another unbound text box over it and use a GotFocus event for the unbound text box that made the Combo visible and GetFocus and the unbound text box invisible and to lose focus
and forget about the zero length string if that is what is causing the lack of sorting?

Regards Jim

 

[tomschafer]

For the sql my last post is exactly what you want maybe change the order by...
cmbWeekReject.RowSource = &quot;SELECT Week FROM RejectTimeSheet
UNION SELECT ' ' FROM
RejectTimeSheet order by Week desc&quot;

Look up UNION operation in help. To get a better understanding or order by and group by clauses in union queries.