Get the system date and convert to the same format

[UAMI]

Hi!

I have a field (DTAIN) in a table(TF01) of type date/time and i want to obtain the system date of the pc and convert it to a date of yyyy-mm-dd
TF01("DTAIN&quot = Year(Now()) * 10000 + Month(Now()) * 100 + Day(Now())

After executing this step, i got an error:
Data type conversion error.

I am new at this language, need help!

 

[scriverb]

try this:

TF01("DTAIN&quot = DateValue(Year(Now()) * 10000 + Month(Now()) * 100 + Day(Now()))


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Actually you don't need to do what you are attempting. The date value is stored internally the same no matter how you assign it. It is when you display it with Format that you will see the different formats. It is not necessary to convert it to the yyyy-mm-dd format just to store the system date.

TF01("DTAIN&quot = Now()

Now when you reference the date value for display just use the Format function:

Format("yyyy-mm-dd", TF01("DTAIN&quot

Let me know if this is not what you are looking for.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[UAMI]

Thanks scriverb, but ...
Your 1º suggestion, occured and error:
"Type mismatch"

Your 2º suggestion, the instruction seems to wrong because it's red.

I know that the pc where the software works has the dateformat dd-mm-yyyy but i want that the field in the table yyyy-mm-dd. But another pc can have another format and that's why i want to make this assignment.

Any more suggestions?

 

[scriverb]

First of all ACCESS doesn't store the date field value in a particular format if the field type is Date/Time. It stores the date as the real number with the whole number to the left of the decimal being the date and the decimal portion to the right being the time. The format displayed is just that. A format. You can make comparisons from one date/time field to another and not have to worry about formatting.

I think what you are referring to is that each PC can have the international date/time format set to different settings. This is true but the value of the date/time field will be the same. You see when the system date is assigned like I suggested with a straight unformated assignment the value is stored as a whole number in the date/time field no matter what the PC settings are. If the date is passed from one pc to another the value is still the same. The display of that value is different.

The second suggestion to make the direct assignment should be okay. TF01("DTAIN&quot = Now()

Is this statement being used within a recordset assignment or is it on a form? It looks like within a recordset VBA code.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[UAMI]

I understand what you explain, and TF01("DTAIN&quot = Now()
works but, than i have in the save field, this values:
09-01-2004 17:18:27
09-01-2004 17:19:43
20-11-2003 16:34:56
2003-08-28-10.35.51
2003-08-28-10.12.10
05-11-2003 16:31:04
2003-10-13-11.53.20
2003-07-29-08.48.43
2003-10-09-09.45.19
2003-08-28-10.32.11

This statement is being used within a recordset VBA code.





If i need to order the record's by date, or make a seleccion in a range of dates, it's all wrong.

 

[scriverb]

Then use the following:

TF01("DTAIN&quot = Format("yyyy-mm-dd", Now())

Are you sure that the field is of the Date/Time format. What you are explaining sounds like a Text field with the Dates stored as a string value but in the date format that you described.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[UAMI]

IT is defined like a date/time field.

Like you suggested, TF01("DTAIN&quot = Format("yyyy-mm-dd", Now())

TF01("DTAIN&quot returned null.

Then i inverted the statemente, like this:
TF01("DTAIN&quot = Format(now(), "yyyy-mm-dd&quot
Now i have the error: Sub or function not defined