Prev: EMKAi- looking for a Graphic Designer/Brand Manager
Next: Error message on Treeview
From: Vic33 on 11 May 2010 23:20
I have 3 x character fields for dd, mm, yy. How do I update my table to show
these as one date field?
Thks
From: Allen Browne on 11 May 2010 23:28
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" <Vic33(a)discussions.microsoft.com> wrote in message
news:BCF08BE0-FF8A-409A-B66B-9881B9EFAC5E(a)microsoft.com...
> I have 3 x character fields for dd, mm, yy. How do I update my table to
> show
> these as one date field?
> Thks

From: Vic33 on 12 May 2010 01:14
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" <Vic33(a)discussions.microsoft.com> wrote in message
> news:BCF08BE0-FF8A-409A-B66B-9881B9EFAC5E(a)microsoft.com...
> > I have 3 x character fields for dd, mm, yy. How do I update my table to
> > show
> > these as one date field?
> > Thks
>
> .
>
From: raskew via AccessMonster.com on 12 May 2010 02:22
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/Forums.aspx/access/201005/1

From: Rick Brandt on 12 May 2010 02:33
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.
 |  Next  |  Last
Pages: 1 2
Prev: EMKAi- looking for a Graphic Designer/Brand Manager
Next: Error message on Treeview