Dates ?

[Alice Spencer]

I have dates in the following format: '06/02/2004 10:36:14' in a table with
a text datatype.
I would like to query these dates for +/- 20 days for example.
I wish to change the data type to date/time so I can do this
How do I put a validation rule in the table so that a date and time will be
accepted as input. ie what is the syntax?
Must I convert this string to a number?
Thanks
A Spencer

 

[Van T. Dinh]

Much better to use DateTime DataType.

If the data entry is not a valid DateTime value, Access / JET will reject
the entry for you.

Not sure what you meant by +/-20 days???

Perhaps you meant within 20 days of a *reference* DateTime point?

 

[Alice Spencer]

I am only just designing the DB and am well aware that I should be using the
date data type. This is what I am trying to do.
I have pulled out the date / time from a SAM database using ADSI calls of a
users last logon time.
It is returned in text and goes into my table (text at the moment)
I know I can use LEFT etc to get just the date part to put in the table
I want to use a validation rule so the date is entered #xx/xx/xxxx# if poss
(I can do without the time if it is too trickY).
Then I want to query the column so I can see who hasn't logged in for the
last 20 days.
I seem to think I must convert the date to a number to do this. Is this
right?
Thanks for your help
Alice

 

[Van T. Dinh]

* You can use the DateValue function to convert the DateText to date value
without time, e.g.

?DateValue("06/02/2004 10:36:14")
06/02/2004

* Like I wrote in my previous reply, it is better to use DateTime Field.
You can use the above in an Update Query to populate a DateTime Field using
Text value you imported.

* I am not sure what validation rule you are referring to since you
imported the data from a SAM table and not manual entry. The DateValue()
and the CDate() will reject any Test String that cannot be converted to a
Date value.

* You will have to convert to Date values to do date comparison.

* Post the details of your Table(s) and some sample data if you need help
with the Query. I guess you will need 2 Tables to do this sort of Queries:
tblUser (user names) and tblLogIn (Records of log-in by users).

 

[Alice Spencer]

My mistake, I used the wrong terminology.
I meant date 'mast' rather than date validation and i have done this now
with ##/##/####
I used the 'LEFT' function to get the first 10 chars for the date retrived
and it goes in the table ok (different column).
Then I use 'DateDiff("d",[NT4UserInfo]![LastLogin2],Date())>20' to query for
the difference.
I think I have cracked it now.
Thanks