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 |
#1
|
|||
|
|||
convert dd and mm and yy fields to one dd/mm/yy field
I have 3 x character fields for dd, mm, yy. How do I update my table to show
these as one date field? Thks |
#2
|
|||
|
|||
convert dd and mm and yy fields to one dd/mm/yy field
Create a query, and type an expression like this into the Field row:
IIf(IsNumeric([dd]) AND IsNumeric([mm]) AND IsNumeric([yy]), DateSerial([yy], [mm], [dd]), Null) You don't want to store both the text and the date in the table, as this could give you inconsistent results. If you are trying to convert the text into a real date (so you can remove the 3 text fields), then turn the query into an Update query, and put the expression in the Update row in query design under your date field. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Vic33" wrote in message ... I have 3 x character fields for dd, mm, yy. How do I update my table to show these as one date field? Thks |
#3
|
|||
|
|||
convert dd and mm and yy fields to one dd/mm/yy field
Phew, you assume that I know what I'm doing! Can't I just use the dateserial
function? I've tried this: set [datefield] = dateserial([day],[mo],[yr]); to no avail. My three fields are txt fields, not numeric. I'm doing something basic wrong but not sure what. Regds Vic "Allen Browne" wrote: Create a query, and type an expression like this into the Field row: IIf(IsNumeric([dd]) AND IsNumeric([mm]) AND IsNumeric([yy]), DateSerial([yy], [mm], [dd]), Null) You don't want to store both the text and the date in the table, as this could give you inconsistent results. If you are trying to convert the text into a real date (so you can remove the 3 text fields), then turn the query into an Update query, and put the expression in the Update row in query design under your date field. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Vic33" wrote in message ... I have 3 x character fields for dd, mm, yy. How do I update my table to show these as one date field? Thks . |
#4
|
|||
|
|||
convert dd and mm and yy fields to one dd/mm/yy field
Hi -
The problem is your text fields. Here are a couple of ways around it: yz = "2009" mz = "12" dz = "6" ? dateserial(cstr(yz), cstr(mz), cstr(dz)) 12/6/2009 ? cdate(yz & "/" & mz & "/" & dz) 12/6/2009 HTH - Bob Vic33 wrote: Phew, you assume that I know what I'm doing! Can't I just use the dateserial function? I've tried this: set [datefield] = dateserial([day],[mo],[yr]); to no avail. My three fields are txt fields, not numeric. I'm doing something basic wrong but not sure what. Regds Vic Create a query, and type an expression like this into the Field row: IIf(IsNumeric([dd]) AND IsNumeric([mm]) AND IsNumeric([yy]), [quoted text clipped - 12 lines] . -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/201005/1 |
#5
|
|||
|
|||
convert dd and mm and yy fields to one dd/mm/yy field
Vic33 wrote:
Phew, you assume that I know what I'm doing! Can't I just use the dateserial function? I've tried this: set [datefield] = dateserial([day],[mo],[yr]); to no avail. My three fields are txt fields, not numeric. I'm doing something basic wrong but not sure what. DateSerial's arguments go Year, Month, Day. You have them backwards. Allen's code was simply trying to make sure that three entries are strings that represent numeric values. If someone were to enter alpha-characters DateSerial would raise an error. |
#6
|
|||
|
|||
convert dd and mm and yy fields to one dd/mm/yy field
Ahhh...the simple explanation goes a long way. Thanks! I should be in 'new
user' forum I realise now. All sorted, much appreciated. Regds Vic "Rick Brandt" wrote: Vic33 wrote: Phew, you assume that I know what I'm doing! Can't I just use the dateserial function? I've tried this: set [datefield] = dateserial([day],[mo],[yr]); to no avail. My three fields are txt fields, not numeric. I'm doing something basic wrong but not sure what. DateSerial's arguments go Year, Month, Day. You have them backwards. Allen's code was simply trying to make sure that three entries are strings that represent numeric values. If someone were to enter alpha-characters DateSerial would raise an error. . |
Thread Tools | |
Display Modes | |
|
|