date conversion from a numeric value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a field [xact_dat_x] with a value of 2005073122312300

using query designer, how do i format the field to display 7/31/2005
 
i have a field [xact_dat_x] with a value of 2005073122312300

using query designer, how do i format the field to display 7/31/2005
Click to expand...

I'm not certain what the 22312300 portion might be - but a calculated
field

DateSerial(Left([xact_dat_x], 4), Mid([xact_dat_x], 5, 2),
Mid([xact_dat_x], 7, 2))

will give you a Date/Time field which will sort chronologically and
can be displayed using any date format you wish (including m/d/yyyy).

John W. Vinson[MVP]
 
Try typing this into the Field row in your query:
DateSerial(Left([[xact_dat_x],4), Mid([xact_dat_x], 5,2),
Mid([xact_dat_x], 7,2))

That should display correctly for whatever your regional settings are, and
sort and respond to criteria correctly as a date.
 
works great. thanks for the tip.

John Vinson said:
i have a field [xact_dat_x] with a value of 2005073122312300

using query designer, how do i format the field to display 7/31/2005
Click to expand...

I'm not certain what the 22312300 portion might be - but a calculated
field

DateSerial(Left([xact_dat_x], 4), Mid([xact_dat_x], 5, 2),
Mid([xact_dat_x], 7, 2))

will give you a Date/Time field which will sort chronologically and
can be displayed using any date format you wish (including m/d/yyyy).

John W. Vinson[MVP]
Click to expand...
 
how could i continue the statment to include to the time resulting in:

2005073122312300 translated as

07/31/2005 10:31:23 PM



Allen Browne said:
Try typing this into the Field row in your query:
DateSerial(Left([[xact_dat_x],4), Mid([xact_dat_x], 5,2),
Mid([xact_dat_x], 7,2))

That should display correctly for whatever your regional settings are, and
sort and respond to criteria correctly as a date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

samuel said:
i have a field [xact_dat_x] with a value of 2005073122312300

using query designer, how do i format the field to display 7/31/2005
Click to expand...
Click to expand...
 
Use the TimeSerial() function as well, following similar logic.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

samuel said:
how could i continue the statment to include to the time resulting in:

2005073122312300 translated as

07/31/2005 10:31:23 PM



Allen Browne said:
Try typing this into the Field row in your query:
DateSerial(Left([[xact_dat_x],4), Mid([xact_dat_x], 5,2),
Mid([xact_dat_x], 7,2))

That should display correctly for whatever your regional settings are,
and
sort and respond to criteria correctly as a date.

samuel said:
i have a field [xact_dat_x] with a value of 2005073122312300

using query designer, how do i format the field to display 7/31/2005
Click to expand...
Click to expand...
Click to expand...
 
Back
Top