Date time prob

[gblack]

When I use a make table Query, often I wish to convert a string to a date field.

So I make a special field and have tried different things, such as formatting the string to date. . .I have tried right clicking on the query field and setting the format to a date format. . .

When I do this, and look at the query (before running it), it appears to put the string into propper format, but then I run the Make Table query and the new table always sets the field back to text!!!. . . How do I force Access to set the table field to Date/time automatically???

 

[DonJ]

I have an idea. How about setting the format in the table you've created, clearing all data, and changing your query from Make Table to Append?

 

[raskew]

In the Help file, lookup the CDate() and DateValue() functions.
Both take an expression (eg, string) which can be interpreted
as a valid date and return a variant (date).

One approach would be to:
1. Add a new date field to the existing table or query.
2. Run an update query, populating the new field with the
Cdate() or DateValue() of the string date field.
3. Run your make table query, specifying the new field
rather than the string field.

 

[gblack]

Thanks

Thanks Don, but the append query idea is what I am doing now and trying to avoid.

Its an extra step not to mention that everytime you need to update the table you would first have to run a delete query.

I think raskews idea should work. . .I remember I use to do something like that, but had forgotten about it.

It is very hard to look up functions in Access 2000 btw!

In 97 it listed all of the functions for you plus examples on how to use them, but in 2000 its like they don't want you to know how to use their functions or something. . . . Am I missing something here? Can you get a list of functions from Access 2000 help? If so I must not be asking the right questions or using the help wrong. . .

 

[DonJ]

Hi,

Raskew's right. Obviously, you guys are light years ahead of me. You're right about the help functionality. I never tried it in 2k until tonight. I can't find a thing! Fortunately, I think you've got an answer to your predicament. THIS TIME.

Good luck & best wishes,
Don Jellie

 

[Pat Hartman]

A2K help is pretty useless and A2002 isn't any better. One thing that might help you to know is that they have split help into two parts. If you open help from the database window screen, you'll get different results than if you have a code module open and open help from that window. You'll have better luck with functions if you use help from a code window.

 

[smed]

Paste the follwing Function into a module :

''''''''''''''''
Function tbl_Change_Type(TableName As String, FieldName As String, FieldType As String)
Dim dbs As Database
Set dbs = CurrentDb
dbs.Execute "ALTER TABLE " & TableName & " ALTER COLUMN " & FieldName & " " & FieldType & ";"
dbs.Close
End Function
''''''''''''''''

Usage
Call tbl_Change_Type("Table1","Field1","Date")
Call tbl_Change_Type("Table1","Field1","Text")

If you don't want to run as a function, with a few modifications it might be able to be used in an SQL query

Hope this helps

Smed