problem with date formatting and textfile

[pungentSapling]

I have a field that requires a "fiscal week" for data. I formatted the txt box "yyww"...this works fine, it shows the last two digits of the year followed by the number representing the week.example: 0201

Problem is that this all has to be exported to a text file and when the yyww data goes to text it ends up taking on a more standard format. 01/01/2002 for example

I tried copying the value into an unformatted textbox on my for (from one formatted txtBox yyww to unformatted txtBox2)
....me.txtBox2.value = me.txtBox.value....
when the data goes to the new txtBox it is back in date format ie:01/01/2002
I tried putting the yyww value into a variable of type String and then putting the variable into txtBox2 with the same result 01/01/2002

How can I make the value inside the textbox the REAL "displayed" value?
Or make a variable equal to the displayed value of txtBox


thanks p

 

[pungentSapling]

thanks, that worked great for getting the data into the correct format...
Now I am back to the problem of access dropping the leading 0 in the week part of the format for week numbers below 10.
how can I force it to preserve leading zeros in the "yyww" format when the format is forced through an sql query statement?

I don't think iif s are allowed in sql... are they?



thanks for the help
p

 

[Jbozward]

You cold also use this....

IIf(Len(Format([Date],"ww"))=1,Format([Date],"yy-" & "0" & "ww"),Format([Date],"yy-ww"))

I have used IIF statements in SQL before.....