Dates ?

  • Thread starter Thread starter Alice Spencer
  • Start date Start date
A

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
 
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?
 
The problem is you don't have a date in your table, you have a text
string. The only two suggestions I can think of is to write some code to
strip out the information you need, or create a new field - date format and
populate it using an update query striping the date part of the text string.

Just because it looks like a date does not mean it is a date.

While often it is hindsight that tells us we missed it, a little
foresight can make life a lot easier when we first design a database. If
you are going to record dates, it is best to record them as dates.

BTW what all is in that field? Is that time down to the second? You
can do that with a date filed if you like. Mmm .... maybe you can even
just convert the filed to a date type.
 
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
 
Alice Spencer said:
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
Click to expand...


No, no need to convert. If it is a date, you can determine the number
of days between now and then. I am sure someone has some nice code and using
the actual number for NOW(). I just have not fooled enough with it to do it
that way. I can do a crudge to make it work however if no one comes up with
a nice clean way.
 
* 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).
 
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
 
Back
Top