sintax for formatting date to normal date in a query expression field

[edson]

Heeeello Fellows,


Thanks God it is raining down here now. We've been through the worst dry season of the last 84 years. Lots of people with no water at all.

Need help on this one, please:

I have the list Lst1 in my form Frm1.
Lst1 is based on Qry1, which is based on Qry2.
Qry2 has a date field named DTVIG originally formatted as Normal Date, e.g., 31Jan14.
Against my will Qry1 shows the date formatted 31/1/2014 and so do Lst1. Format properties are not available for Qry1 DTVIG column (maybe because Qry1 is the data source for the Lst1 ?, I'am not sure)
Any way, I want Lst1 reading DTVIG as Normal Date.

I have created an extra field in the Qry1 named DTVIGFmtd as an expression that reads:
DTVIGFmtd:Format(DTVIG, "dd mmm yy") and expected to use this field to replace DTVIG in my Lst1 and solve the problem.

Also tried the following sintaxes as per some examples I found in the net:
DTVIGFmtd:Format DTVIG, "dd mmm yy"
DTVIGFmtd:Format DTVIG; Normal Date

They all pop up sintax errors messages.

Any help on how to write the expression correctly would be very welcome.
Thanks

[aytee111]

Not sure what you mean by "originally formatted as Normal Date, e.g., 31Jan14". Is this on the table? Formatting should be left to queries and forms, not done on tables.

Assuming that the table field is defined as "Date/Time" with no formatting then any date formatting in one of the queries will work. Examples are Format(datefieldname," dd mmm yy").

What is the exact error message?

[edson]

Hello aytee111. Thank you for your reply.
Yes, the Normal Date format was in the table. I regularly format fields in the tables since they have format field options available but from now on I will consider your hint to left formatting to queries and forms. The error messages were exactly that: Sintax error on the expression... etc. etc. which pop up during the attempt to run the query. Actually the error message type became irrelevant. The bottom line is that I was missing parenthesis in my sintax. Format(datefieldname," dd mmm yy"). Thank you for that also. It took me a while to find that if Qry1 (data source for Lst1) is based on the table (instead of being based on Qry2) the problem vanishes since the table is already formatted.
I'll mark this thread as solved. Cheers.

[aytee111]

You're welcome. Sounds like you learned a lot during this problem. Working with dates can be tricky sometimes.