|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#11
October 31st, 2008, 02:40 PM
posted to microsoft.public.access
|
|||
|
|||
Date/Time questions
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 "John Spencer" wrote: 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 "John W. Vinson" wrote: On Tue, 28 Oct 2008 09:23:02 -0700, Amin wrote: 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 W. Vinson [MVP] 
|
|
#12
October 31st, 2008, 06:51 PM
posted to microsoft.public.access
|
|||
|
|||
|
Date/Time questions
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 "John Spencer" wrote: 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 "John W. Vinson" wrote: On Tue, 28 Oct 2008 09:23:02 -0700, Amin wrote: 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 W. Vinson [MVP]
|
|
#13
November 3rd, 2008, 02:27 PM
posted to microsoft.public.access
|
|||
|
|||
|
Date/Time questions
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 "John Spencer" wrote: 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 "John Spencer" wrote: 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 "John W. Vinson" wrote: On Tue, 28 Oct 2008 09:23:02 -0700, Amin wrote: 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 W. Vinson [MVP]
|
|
#14
November 3rd, 2008, 05:24 PM
posted to microsoft.public.access
|
|||
|
|||
|
Date/Time questions
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 Amin wrote: 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 "John Spencer" wrote: 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 "John Spencer" wrote: 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 "John W. Vinson" wrote: On Tue, 28 Oct 2008 09:23:02 -0700, Amin wrote: 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 W. Vinson [MVP]
|
|
#15
November 3rd, 2008, 09:33 PM
posted to microsoft.public.access
|
|||
|
|||
|
Date/Time questions
Thanks! I know that was not terribly quick.
"John Spencer" wrote: 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 Amin wrote: 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 "John Spencer" wrote: 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 "John Spencer" wrote: 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 "John W. Vinson" wrote: On Tue, 28 Oct 2008 09:23:02 -0700, Amin wrote: 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 W. Vinson [MVP]
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
