Strange date formatting

[fat controller]

I have a form which has a text box which shows the date of a record (txtDate), and another that shows a vehicle ID (txtBonnet), both of which are not visible; I have a third text box (txtDisplay) which shows a string of text, referring to the two other text boxes - - generally works very well indeed, however.....

If the date is later in the month, it will show the date in the UK short date format (so today is 29/09/2015), but if it is earlier in the month, it will show it in the US format (so the 3rd September becomes 09/03/2015);

As part of the string, I tried to get it to format the date Format([txtDate],"dd/mm/yyy"), but that stopped any records appearing at all, so how do I get this string to show me the date in the correct format all the time?

 

[James Deckert]

As a start, you could make your invisible fields visible to try to narrow down when the change is happening (on the original field, or on the later calculation)

 

[fat controller]

The invisible field is the problem - it is changing its format depending on how far through the month the chosen date is. The format of that text box is set to 'Short Date'

Edit - the data stored in the table is in dd/mm/yyyy format, and displays elsewhere without issue.

 

[gemma-the-husky]

it's probably txtdisplay.

if it's not set as "Short Date", then it's just a string, and it depends what your query is actually doing.

 

[fat controller]

it's probably txtdisplay.

if it's not set as "Short Date", then it's just a string, and it depends what your query is actually doing.

I have had a look at the related table, and the field is set to Short Date, and all data so far is stored as dd/mm/yy; the only difference with this particular form is that it is opened with a couple of WHERE conditions, one of which is for the date in the field that txtDate is bound to, to be equal to a chosen date.

 

[James Deckert]

Is the record source for the form a table or query? If query, open the query and see if the dates are all correct at this point.
Is the control source for the invisible field simply the field name from the table/query?
What happens if you use the original field in your string calculation instead of the invisible field?

>> all data so far is stored as dd/mm/yy
The date in the table is stored as a real number date.time. It is the formatting that changes how we see it, not how it is stored. This allows a consistent storage format even though different countries display it differently.

 

[JHB]

The invisible field is the problem - it is changing its format depending on how far through the month the chosen date is. The format of that text box is set to 'Short Date'

Create a new text-control without any formatting, does it show correct then?

 

[fat controller]

The text control is simply a field in a query, and running the query itself shows all dates as I would expect to see them. A new text box, bound to the same field shows exactly the same

Dates are integral to numerous parts of this database, and are used all over the place - I have never had this problem before, which is what is confusing the life out of me.

I have even tried making the Source for the textbox =Format([ServiceDate], "Short Date") to no avail, and even substituted the Short Date for dd/mm/yyyy, or even dd/\mm/\yyyy - still no luck.

EDIT - Cracked it!

The form opens by clicking on a text box on a continuous form, and is then opened with two WHERE conditions, one of which is the date. I had been really stupid and put the following:

Format([ServiceDate],"dd\/mm\/yyyy") when in fact, I should have put Format([ServiceDate], "mm\/dd\/yyyy")

Amazing how some sleep can allow you to see things you didn't see the previous day....

Thanks for your help everyone

 

[arnelgp]

try a test query if indeed the record in question was for the month of september:

select yourDateField, Format(yourDateField, "m") As MonthNo, Format(yourDateField, "mmmm") As MonthName, Year(yourDateField) As Year

 

[fat controller]

Thanks arnelgp - I must have been editing my post when you posted; I have sussed out what I had done wrong (somewhat embarrassing, fairly simple error). Thanks for your help and patience.

 

[arnelgp]

i make wrong post too, i guess most of the time.