look in a table for all the date/time fields and chop off the time 1

[ruthcali]

I am using Access97.

Every morning, i delete and then import a large table from another database into my existing database. That large table has 85 fields, 40 of which are Date/Time data types.

I do calculations with those date/time fields and I'm having troubles because some fields contains the time, ex: 01/31/2002 9:28:00 AM.

When i run a query to give me all the projects between #01/01/02# and #01/31/02#, the record above isn't included because it contains a time. Or if i want to see if a project is on time and the due date is 01/31/02, the record above will show up late when it should be on time.

Instead of putting: format(datefield,"short date&quot for all my queries and calculations, is there a way, using code, to look in the table for all the date/time fields and then chop off the time portion?

Thanks,
ruth

 

[SgtJarrow]

I don't know about your exact question, but couldn't you just run one update query each time you import and chop off the time there?? Then, you would not even have to worry about the time being chopped off later....Just a suggestion. Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[ruthcali]

Robert,
thanks for your quick response!

I wanted to avoid creating an update query with the 40 hard-coded date/time fields.

Can i do it with code that opens a table, loops thru the fields and when it finds a date/time field, chop off the time?

I was hoping someone could help me with that general code. i'm not familiar with looping thru a table, searching by data type and then updating.

Thanks again!

 

[RickSpr]

The update query is probably the simplest solution, but you could also change your queries that depend on [date field] to use CDate(Int([date field])). The integer part of a date/time field holds the date. The time is represented in the fractional part. Rick Sprague

 

[SgtJarrow]

I agree with RickSpr....the update query is the best solution. It may take a short while to create, but once it is done, you shouldn't have to mess with it too much and it can do all the fields at once.

Here is the code string that will walk you through the table. This looks for a field and then can update. But I don't know how you can make this look in every field and check the format.

Maybe you can use this.....don't know. I also don't have much experience in walking through recordsets.....I tend to use querydefs and the like for this stuff.

Good Luck!


'****************Start Code***************
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("YouTableName", dbOpenTable)
With rs
.FindFirst "[Field] = 'Date'"
If Not .NoMatch Then
.Edit
![Field] = "Date"
.Update
End If
End With Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[ruthcali]

Well, i guess i'll go to majority and do the update query.
(Robert, i tried your code but got a run time error).

This is what i was planning on doing:

UPDATE MyTable SET MyTable.MyDate = Format([MyDate],"Short Date&quot
WHERE (((MyTable.MyDate) Is Not Null));

Does it make a difference which i use:
Format([MyDate],"short date&quot or
CDate(Int([date field])).

Thanks,
Ruth

 

[RickSpr]

Ruth,

Ultimately, both formulas will give the same result, but Format will cause the date to be converted to a string, and then converted back to a date so it can be stored in the record. CDate(Int(date)) converts it to an integer and then back to a date, which is a little faster. (Actually, I think you could just use Int(date) in this context, since storing it in the record will automatically convert it back to a date.)

Here's a general routine that will removes times from all date fields in an arbitrary table:

    Sub DeleteTimes(TableName As String)
        Dim rst As DAO.Recordset, fld As DAO.Field
        
        Set rst = CurrentDb.OpenRecordset(TableName)
        Do While Not rst.EOF
            rst.Edit
            For Each fld In rst.Fields
                If fld.Type = dbDate _
                And Not IsNull(fld.Value) Then
                    fld.Value = CDate(Int(fld.Value))
                End If
            Next fld
            rst.Update
            rst.MoveNext
        Loop
        Set rst = Nothing
    End Sub

You could tweak this a little to avoid updating rows where you didn't find any dates with times included. Rick Sprague

 

[ruthcali]

Rick!! it's perfect.

i ran the code and in my table of 3300 records, it only took 3 seconds to run the code and update my 40 date/time fields to chop all the times off.

This is also great for when new date fields are added to the table. They will automatically get their times chopped off.

The code is great. Thank you so much!!!

 

[apkohn]

For your date check, why just not look for all dates between #1/1/02# and #1/31/02 23:59.99#.

ie, simply set the time part of the upper date to 1 minute before midnight (or even set it to 0:00 on the next morning). To all intents and purposes, the code will function as desired, and it will only take a one line change in your query:
(WHERE [Date] > StartDate AND [DATE] < (DateAdd(&quot;d&quot;,1,EndDate)

 

[ruthcali]

Apkohn,
that's a good idea, but i was trying to avoid complicating my queries since i have many of them. Instead of going into each query and adding a check for the Time, i prefer to chop all of the times with code once and be done with it.

To give you an example, i had one query that before looked like:
(BusDaysInterval is code to calculate bus days between 2 days).

Interval: IIf(Format(nz([final_date]),&quot;Short Date&quot>=Format(nz([system_date]),&quot;Short Date&quot,BusDaysInterval(Format(nz([final_date]),&quot;short date&quot,Format(nz([project_date]),&quot;short date&quot),BusDaysInterval(Format(nz([system_date]),&quot;short date&quot,Format(nz([project_date]),&quot;short date&quot))

A bit hairy, i think. Now with Rick's code, i could change it to:

Interval: IIf(nz([final_date])>=nz([system_date]),BusDaysInterval(nz([final_date]),nz([project_date])),BusDaysInterval(nz([system_date]),nz([project_date])))