Format Date

I have to import a text file into access. When I import
the file using the date format on the table I get import
errors. Since I cannot control the text file data I must
import the field as a general number. As a result I need
to convert the format in the query or report to MM/DD/YYYY
but, I do not know how to do it. Does anybody have any
ideas? Thank You in advance. Tiffany

Tiffany

What is the underlying structure of the "date field"? You describe it as a
"general number", but is it a number like Access uses, or is it a text
string like "20040530"?

Access has a DateSerial(Y,M,D) function you could check into using (try HELP
on DateSerial).


--
Good luck

Jeff Boyce
Access MVP

Jeff it is a text string "20040530". I took your advice
and looked it up in Help but I'm confused I only want to
convert the string into a date. The example returns a
number of days? So this is what I think I should do.


Function ConvertTxtToDate(dteInput As Date) As Integer
Dim intDays As Integer

' Add one month, subtract dates to find difference.
intDays = DateSerial(Year(dteInput), _
Month(dteInput), Day(dteInput)) _
DateSerial(Year(dteInput), _
Month(dteInput), Day(dteInput))
ConvertTxtToDate = intDays
Debug.Print intDays
End Function

The following is the Sub procedure for the
ConvertTxtToDate function.

Sub CallConvertTxtToDate()
Dim intDays As Integer

intDays = DaysInMonth("4-1-1996")

End Sub


If this is correct then I know I should make a module
called ConvertTxtToDate but I do not know where or how to
put the Sup procedure. In the Query, Report Query, SQL in
the query? If in the SQL then how to add and where? If
anywhere then how to add and where?. Thanks in advance,
Tiffany




-----Original Message-----
Tiffany

What is the underlying structure of the "date field"?
You describe it as a
"general number", but is it a number like Access uses, or
is it a text
string like "20040530"?

Access has a DateSerial(Y,M,D) function you could check
into using (try HELP
on DateSerial).


--
Good luck

Jeff Boyce
Access MVP

.

Pardon me for jumping in. As long as your field is never null, you should be
able to use something like the following.

DateSerial(Left(YourField,4),Mid(YourField,5,2),Ri ght(YourField,2))

If it can be null, then

IIF(YourField is Not Null,
DateSerial(Left(YourField,4),Mid(YourField,5,2),Ri ght(YourField,2)), Null)

Tiffany wrote:

Jeff it is a text string "20040530". I took your advice
and looked it up in Help but I'm confused I only want to
convert the string into a date. The example returns a
number of days? So this is what I think I should do.

Function ConvertTxtToDate(dteInput As Date) As Integer
Dim intDays As Integer

' Add one month, subtract dates to find difference.
intDays = DateSerial(Year(dteInput), _
Month(dteInput), Day(dteInput)) _
DateSerial(Year(dteInput), _
Month(dteInput), Day(dteInput))
ConvertTxtToDate = intDays
Debug.Print intDays
End Function

The following is the Sub procedure for the
ConvertTxtToDate function.

Sub CallConvertTxtToDate()
Dim intDays As Integer

intDays = DaysInMonth("4-1-1996")

End Sub

If this is correct then I know I should make a module
called ConvertTxtToDate but I do not know where or how to
put the Sup procedure. In the Query, Report Query, SQL in
the query? If in the SQL then how to add and where? If
anywhere then how to add and where?. Thanks in advance,
Tiffany


-----Original Message-----
Tiffany

What is the underlying structure of the "date field"?
You describe it as a
"general number", but is it a number like Access uses, or
is it a text
string like "20040530"?

Access has a DateSerial(Y,M,D) function you could check
into using (try HELP
on DateSerial).


--
Good luck

Jeff Boyce
Access MVP

.

John, it worked thank you, Tiffany


-----Original Message-----
Pardon me for jumping in. As long as your field is never
null, you should be
able to use something like the following.

DateSerial(Left(YourField,4),Mid(YourField,5,2),Ri ght
(YourField,2))

If it can be null, then

IIF(YourField is Not Null,
DateSerial(Left(YourField,4),Mid(YourField,5,2),R ight
(YourField,2)), Null)

Tiffany wrote:

Jeff it is a text string "20040530". I took your advice
and looked it up in Help but I'm confused I only want to
convert the string into a date. The example returns a
number of days? So this is what I think I should do.

Function ConvertTxtToDate(dteInput As Date) As Integer
Dim intDays As Integer

' Add one month, subtract dates to find difference.
intDays = DateSerial(Year(dteInput), _
Month(dteInput), Day(dteInput)) _
DateSerial(Year(dteInput), _
Month(dteInput), Day(dteInput))
ConvertTxtToDate = intDays
Debug.Print intDays
End Function

The following is the Sub procedure for the
ConvertTxtToDate function.

Sub CallConvertTxtToDate()
Dim intDays As Integer

intDays = DaysInMonth("4-1-1996")

End Sub

If this is correct then I know I should make a module
called ConvertTxtToDate but I do not know where or how
to
put the Sup procedure. In the Query, Report Query, SQL
in
the query? If in the SQL then how to add and where? If
anywhere then how to add and where?. Thanks in advance,
Tiffany


-----Original Message-----
Tiffany

What is the underlying structure of the "date field"?
You describe it as a
"general number", but is it a number like Access uses,
or
is it a text
string like "20040530"?

Access has a DateSerial(Y,M,D) function you could check
into using (try HELP
on DateSerial).


--
Good luck

Jeff Boyce
Access MVP

.

.