Date/Time questions

[Amin]

So I have two fields that are numeric that represent date/time fields.

The first field is date and the numbers are in this form : 20080926

The second field is time and the numbers are in this form: 110339

Now, when I tried to import the data (from a .txt field) and put the field
type as Date/Time, it would have errors and the field became empty. I also
tried to convert the data to Date/Time AFTER importing it as a number type,
and the field would still have errors and the field became empty. Why is this
happening, and how can I fix it?

Thanks in advance for any help,
Amin

 

[Wayne-I-M]

Hi Amin

Try importing it as text - then use a query to change it to the format you
want

 

[JOCELYN SHIMIZU]

So I have two fields that are numeric that represent date/time fields.

The first field is date and the numbers are in this form : 20080926

The second field is time and the numbers are in this form: 110339

Now, when I tried to import the data (from a .txt field) and put the field
type as Date/Time, it would have errors and the field became empty. I also
tried to convert the data to Date/Time AFTER importing it as a number
type,
and the field would still have errors and the field became empty. Why is
this
happening, and how can I fix it?

Thanks in advance for any help,
Amin

 

[John W. Vinson]

So I have two fields that are numeric that represent date/time fields.

The first field is date and the numbers are in this form : 20080926

The second field is time and the numbers are in this form: 110339

Now, when I tried to import the data (from a .txt field) and put the field
type as Date/Time, it would have errors and the field became empty. I also
tried to convert the data to Date/Time AFTER importing it as a number type,
and the field would still have errors and the field became empty. Why is this
happening, and how can I fix it?

Thanks in advance for any help,
Amin

These numbers will not be recognized as either dates or times, without some
punctuation.

You can create a single date/time field containing this data by using an
Update query. Assuming that these are two Number fields, try

CDate(Format([datenumber], "@@@@\-@@\-@@") & " " & Format([timenumber],
"@@\@\@")

This will generate a text strin like "2008-09-26 11:03:39" which CDate will be
able to properly parse into a date/time.

 

[Guest]

So I have two fields that are numeric that represent date/time fields.

The first field is date and the numbers are in this form : 20080926

The second field is time and the numbers are in this form: 110339

Now, when I tried to import the data (from a .txt field) and put the field
type as Date/Time, it would have errors and the field became empty. I also
tried to convert the data to Date/Time AFTER importing it as a number
type,
and the field would still have errors and the field became empty. Why is
this
happening, and how can I fix it?

Thanks in advance for any help,
Amin

 

[John W. Vinson]

Thanks! So I've decided to import all of documents with data type, "text",
and you have shown me how to change these to date/time. How would I change
them to numbers?

Well... if the data represented by the value is in fact a date, you would be
doing yourself a disservice by casting them as numbers. If you are importing
data and want a number value, you can use CLng() to convert a text string to a
Long Integer, or CDbl to convert it to a Double Float, or CCur() to convert to
Currency.

 

[Amin]

I keep getting an error when I run this query to change the number to a date?

UPDATE CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME]
"@@\@\@")) FROM [WFLOW,OLD]

Why is this? Can I run a Macro to convert this?

Thanks,
Amin

 

[Douglas J. Steele]

Your Update statement is invalid.

If you want to alter the table, you need

UPDATE [WFLOW,OLD]
SET NewDateField = CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME] "@@\@\@")) FROM [WFLOW,OLD]


If all you want is the date value (as opposed to changing the table), use

SELECT CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME] "@@\@\@")) FROM [WFLOW,OLD]

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I keep getting an error when I run this query to change the number to a
date?

UPDATE CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME]
"@@\@\@")) FROM [WFLOW,OLD]

Why is this? Can I run a Macro to convert this?

Thanks,
Amin

Well... if the data represented by the value is in fact a date, you would
be
doing yourself a disservice by casting them as numbers. If you are
importing
data and want a number value, you can use CLng() to convert a text string
to a
Long Integer, or CDbl to convert it to a Double Float, or CCur() to
convert to
Currency.

 

[John Spencer]

If either the date field or the time field is blank (null or otherwise) or
cannot be interpreted as a date, you will get the errors. Also your QUERY
syntax is wrong and the FORMAT function for the time is missing a comma.

UPDATE [Name of Your Table]
SET [Name of a Field] = CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME], "@@\@\@") )
WHERE CLCREATEDate is not null and WLCreateTime is Not Null

A better check would be the following
WHERE IsDate(Format([WLCREATEDATE], "@@@@\-@@\-@@")) and
IsDate(Format([WLCREATETIME], "@@\@\@"))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I keep getting an error when I run this query to change the number to a date?

UPDATE CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME]
"@@\@\@")) FROM [WFLOW,OLD]

Why is this? Can I run a Macro to convert this?

Thanks,
Amin

Well... if the data represented by the value is in fact a date, you would be
doing yourself a disservice by casting them as numbers. If you are importing
data and want a number value, you can use CLng() to convert a text string to a
Long Integer, or CDbl to convert it to a Double Float, or CCur() to convert to
Currency.

 

[Amin]

Hi John,

I still can't get this to work. When I ran this code:

UPDATE azquick
SET [newdatefield] = CDate(Format([WLCREATEDATE],"@@@@\-@@\-@@") & " " &
Format([WLCREATETIME],"@@\@\@"))
WHERE WLCREATEDate is not null and WLCreateTime is Not Null;

The parametric query popped up, and whatever I put in there it gave an error
message as field is not updateable. I then changed [newdatefield] to
[WLCREATEDATE], and the update query ran, but nothing changed.

Any thoughts?

Amin

If either the date field or the time field is blank (null or otherwise) or
cannot be interpreted as a date, you will get the errors. Also your QUERY
syntax is wrong and the FORMAT function for the time is missing a comma.

UPDATE [Name of Your Table]
SET [Name of a Field] = CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME], "@@\@\@") )
WHERE CLCREATEDate is not null and WLCreateTime is Not Null

A better check would be the following
WHERE IsDate(Format([WLCREATEDATE], "@@@@\-@@\-@@")) and
IsDate(Format([WLCREATETIME], "@@\@\@"))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I keep getting an error when I run this query to change the number to a date?

UPDATE CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME]
"@@\@\@")) FROM [WFLOW,OLD]

Why is this? Can I run a Macro to convert this?

Thanks,
Amin

Thanks! So I've decided to import all of documents with data type, "text",
and you have shown me how to change these to date/time. How would I change
them to numbers?
Well... if the data represented by the value is in fact a date, you would be
doing yourself a disservice by casting them as numbers. If you are importing
data and want a number value, you can use CLng() to convert a text string to a
Long Integer, or CDbl to convert it to a Double Float, or CCur() to convert to
Currency.

 

[John Spencer]

First WHAT was the parametric query? Did it ask for NewDateField? If that is
the case, you either did not add that field to your table (azQuick?) or you
have misspelled the field name.

If you ran the query, did you simply switch to datasheet view. That will show
you what will be updated, but does not run the query. Try selecting Query:
Run from the menu and see what happens.

As a check try the following and see if any records get returned
SELECT *
FROM AZQuick
WHERE IsDate(Format([WLCREATEDATE], "@@@@\-@@\-@@")) and
IsDate(Format([WLCREATETIME], "@@\@\@"))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

I still can't get this to work. When I ran this code:

UPDATE azquick
SET [newdatefield] = CDate(Format([WLCREATEDATE],"@@@@\-@@\-@@") & " " &
Format([WLCREATETIME],"@@\@\@"))
WHERE WLCREATEDate is not null and WLCreateTime is Not Null;

The parametric query popped up, and whatever I put in there it gave an error
message as field is not updateable. I then changed [newdatefield] to
[WLCREATEDATE], and the update query ran, but nothing changed.

Any thoughts?

Amin

If either the date field or the time field is blank (null or otherwise) or
cannot be interpreted as a date, you will get the errors. Also your QUERY
syntax is wrong and the FORMAT function for the time is missing a comma.

UPDATE [Name of Your Table]
SET [Name of a Field] = CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME], "@@\@\@") )
WHERE CLCREATEDate is not null and WLCreateTime is Not Null

A better check would be the following
WHERE IsDate(Format([WLCREATEDATE], "@@@@\-@@\-@@")) and
IsDate(Format([WLCREATETIME], "@@\@\@"))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I keep getting an error when I run this query to change the number to a date?

UPDATE CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME]
"@@\@\@")) FROM [WFLOW,OLD]

Why is this? Can I run a Macro to convert this?

Thanks,
Amin

:

Thanks! So I've decided to import all of documents with data type, "text",
and you have shown me how to change these to date/time. How would I change
them to numbers?
Well... if the data represented by the value is in fact a date, you would be
doing yourself a disservice by casting them as numbers. If you are importing
data and want a number value, you can use CLng() to convert a text string to a
Long Integer, or CDbl to convert it to a Double Float, or CCur() to convert to
Currency.

 

[Amin]

Sorry for being unclear, the parametric query did ask for "NewDateField", and
I don't know how to insert a new empty field, which is why I just changed
[NewDateField] to [WLCREATEDATE] because I thought it would update that new
field with the Date and Time. I guess that is not possible.

I just ran the "check" code you wrote and it returned the entire table with
the date and time fields unchanged. The date and time fields are currently
text, does that matter? I'm sorry this has been so difficult, my biggest need
to have it in that form is that I want to get the number of business days
between two dates.

Thanks again for all of your help,

Amin

First WHAT was the parametric query? Did it ask for NewDateField? If that is
the case, you either did not add that field to your table (azQuick?) or you
have misspelled the field name.

If you ran the query, did you simply switch to datasheet view. That will show
you what will be updated, but does not run the query. Try selecting Query:
Run from the menu and see what happens.

As a check try the following and see if any records get returned
SELECT *
FROM AZQuick
WHERE IsDate(Format([WLCREATEDATE], "@@@@\-@@\-@@")) and
IsDate(Format([WLCREATETIME], "@@\@\@"))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

I still can't get this to work. When I ran this code:

UPDATE azquick
SET [newdatefield] = CDate(Format([WLCREATEDATE],"@@@@\-@@\-@@") & " " &
Format([WLCREATETIME],"@@\@\@"))
WHERE WLCREATEDate is not null and WLCreateTime is Not Null;

The parametric query popped up, and whatever I put in there it gave an error
message as field is not updateable. I then changed [newdatefield] to
[WLCREATEDATE], and the update query ran, but nothing changed.

Any thoughts?

Amin

If either the date field or the time field is blank (null or otherwise) or
cannot be interpreted as a date, you will get the errors. Also your QUERY
syntax is wrong and the FORMAT function for the time is missing a comma.

UPDATE [Name of Your Table]
SET [Name of a Field] = CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME], "@@\@\@") )
WHERE CLCREATEDate is not null and WLCreateTime is Not Null

A better check would be the following
WHERE IsDate(Format([WLCREATEDATE], "@@@@\-@@\-@@")) and
IsDate(Format([WLCREATETIME], "@@\@\@"))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Amin wrote:
I keep getting an error when I run this query to change the number to a date?

UPDATE CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME]
"@@\@\@")) FROM [WFLOW,OLD]

Why is this? Can I run a Macro to convert this?

Thanks,
Amin

:

Thanks! So I've decided to import all of documents with data type, "text",
and you have shown me how to change these to date/time. How would I change
them to numbers?
Well... if the data represented by the value is in fact a date, you would be
doing yourself a disservice by casting them as numbers. If you are importing
data and want a number value, you can use CLng() to convert a text string to a
Long Integer, or CDbl to convert it to a Double Float, or CCur() to convert to
Currency.

 

[John Spencer]

After you import the data, open the table in design view and add a field to
store the results of the update query. Once you have done that you should be
able to go back to the update query and run it to populate the field you have
added. You will need to use the name of the added field in the update query.

If you want to calculate business days between two dates, then you really
don't need to inlcude the time.

The check query was to ensure that the text fields could be interpreted as
datetime fields and you really needed to run the second version.

UPDATE [Name of Your Table]
SET [New Field Name] = CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME], "@@\@\@") )
WHERE IsDate(Format([WLCREATEDATE], "@@@@\-@@\-@@")) and
IsDate(Format([WLCREATETIME], "@@\@\@"))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Sorry for being unclear, the parametric query did ask for "NewDateField", and
I don't know how to insert a new empty field, which is why I just changed
[NewDateField] to [WLCREATEDATE] because I thought it would update that new
field with the Date and Time. I guess that is not possible.

I just ran the "check" code you wrote and it returned the entire table with
the date and time fields unchanged. The date and time fields are currently
text, does that matter? I'm sorry this has been so difficult, my biggest need
to have it in that form is that I want to get the number of business days
between two dates.

Thanks again for all of your help,

Amin

First WHAT was the parametric query? Did it ask for NewDateField? If that is
the case, you either did not add that field to your table (azQuick?) or you
have misspelled the field name.

If you ran the query, did you simply switch to datasheet view. That will show
you what will be updated, but does not run the query. Try selecting Query:
Run from the menu and see what happens.

As a check try the following and see if any records get returned
SELECT *
FROM AZQuick
WHERE IsDate(Format([WLCREATEDATE], "@@@@\-@@\-@@")) and
IsDate(Format([WLCREATETIME], "@@\@\@"))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John,

I still can't get this to work. When I ran this code:

UPDATE azquick
SET [newdatefield] = CDate(Format([WLCREATEDATE],"@@@@\-@@\-@@") & " " &
Format([WLCREATETIME],"@@\@\@"))
WHERE WLCREATEDate is not null and WLCreateTime is Not Null;

The parametric query popped up, and whatever I put in there it gave an error
message as field is not updateable. I then changed [newdatefield] to
[WLCREATEDATE], and the update query ran, but nothing changed.

Any thoughts?

Amin
:

If either the date field or the time field is blank (null or otherwise) or
cannot be interpreted as a date, you will get the errors. Also your QUERY
syntax is wrong and the FORMAT function for the time is missing a comma.

UPDATE [Name of Your Table]
SET [Name of a Field] = CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME], "@@\@\@") )
WHERE CLCREATEDate is not null and WLCreateTime is Not Null

A better check would be the following
WHERE IsDate(Format([WLCREATEDATE], "@@@@\-@@\-@@")) and
IsDate(Format([WLCREATETIME], "@@\@\@"))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Amin wrote:
I keep getting an error when I run this query to change the number to a date?

UPDATE CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME]
"@@\@\@")) FROM [WFLOW,OLD]

Why is this? Can I run a Macro to convert this?

Thanks,
Amin

:

Thanks! So I've decided to import all of documents with data type, "text",
and you have shown me how to change these to date/time. How would I change
them to numbers?
Well... if the data represented by the value is in fact a date, you would be
doing yourself a disservice by casting them as numbers. If you are importing
data and want a number value, you can use CLng() to convert a text string to a
Long Integer, or CDbl to convert it to a Double Float, or CCur() to convert to
Currency.

 

[Amin]

Thanks! I know that was not terribly quick.

After you import the data, open the table in design view and add a field to
store the results of the update query. Once you have done that you should be
able to go back to the update query and run it to populate the field you have
added. You will need to use the name of the added field in the update query.

If you want to calculate business days between two dates, then you really
don't need to inlcude the time.

The check query was to ensure that the text fields could be interpreted as
datetime fields and you really needed to run the second version.

UPDATE [Name of Your Table]
SET [New Field Name] = CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME], "@@\@\@") )
WHERE IsDate(Format([WLCREATEDATE], "@@@@\-@@\-@@")) and
IsDate(Format([WLCREATETIME], "@@\@\@"))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Sorry for being unclear, the parametric query did ask for "NewDateField", and
I don't know how to insert a new empty field, which is why I just changed
[NewDateField] to [WLCREATEDATE] because I thought it would update that new
field with the Date and Time. I guess that is not possible.

I just ran the "check" code you wrote and it returned the entire table with
the date and time fields unchanged. The date and time fields are currently
text, does that matter? I'm sorry this has been so difficult, my biggest need
to have it in that form is that I want to get the number of business days
between two dates.

Thanks again for all of your help,

Amin

First WHAT was the parametric query? Did it ask for NewDateField? If that is
the case, you either did not add that field to your table (azQuick?) or you
have misspelled the field name.

If you ran the query, did you simply switch to datasheet view. That will show
you what will be updated, but does not run the query. Try selecting Query:
Run from the menu and see what happens.

As a check try the following and see if any records get returned
SELECT *
FROM AZQuick
WHERE IsDate(Format([WLCREATEDATE], "@@@@\-@@\-@@")) and
IsDate(Format([WLCREATETIME], "@@\@\@"))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Amin wrote:
Hi John,

I still can't get this to work. When I ran this code:

UPDATE azquick
SET [newdatefield] = CDate(Format([WLCREATEDATE],"@@@@\-@@\-@@") & " " &
Format([WLCREATETIME],"@@\@\@"))
WHERE WLCREATEDate is not null and WLCreateTime is Not Null;

The parametric query popped up, and whatever I put in there it gave an error
message as field is not updateable. I then changed [newdatefield] to
[WLCREATEDATE], and the update query ran, but nothing changed.

Any thoughts?

Amin
:

If either the date field or the time field is blank (null or otherwise) or
cannot be interpreted as a date, you will get the errors. Also your QUERY
syntax is wrong and the FORMAT function for the time is missing a comma.

UPDATE [Name of Your Table]
SET [Name of a Field] = CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME], "@@\@\@") )
WHERE CLCREATEDate is not null and WLCreateTime is Not Null

A better check would be the following
WHERE IsDate(Format([WLCREATEDATE], "@@@@\-@@\-@@")) and
IsDate(Format([WLCREATETIME], "@@\@\@"))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Amin wrote:
I keep getting an error when I run this query to change the number to a date?

UPDATE CDate( Format([WLCREATEDATE], "@@@@\-@@\-@@") & " " &
Format([WLCREATETIME]
"@@\@\@")) FROM [WFLOW,OLD]

Why is this? Can I run a Macro to convert this?

Thanks,
Amin

:

Thanks! So I've decided to import all of documents with data type, "text",
and you have shown me how to change these to date/time. How would I change
them to numbers?
Well... if the data represented by the value is in fact a date, you would be
doing yourself a disservice by casting them as numbers. If you are importing
data and want a number value, you can use CLng() to convert a text string to a
Long Integer, or CDbl to convert it to a Double Float, or CCur() to convert to
Currency.