Access conversion functions???

[ccarpediem]

I have a table (lets say TABLE1) that has a column (call it COL1) defined as text. Even though it is defined as text it may hold valid alphnumeric, numeric, or date data. Depending on other settings in the table I will know what datatype is stored in that column.

Say if COL2 = 1 then I know what is in COL1 should be treated as a date value. This simplifies it, but serves as an example.

I was hoping to use a scalar function and write a query like this.

Select *
From Tab1
Where COL2 = 1
And Date(COL1) > #9/9/2001#

I see this is not available in Access (not sure if it would work in any thing else either), does anyone have any ideas how to accomplish this in Access?

Thanks much!

 

[David R]

Depending on your database design, it seems to me you'd be better off storing alpha, numeric, and date data in separate fields with appropriate data types. You're probably going to find it MUCH more usable in the future (I speak from the voice of painful experience here).

If it has to be mixed, consider using the IsDate, IsText, and IsNumber functions to sort out your data?

 

[pcs]

i agree with David...don't go down this road.

you won't gain anything in terms of making the db smaller, etc.

and it will drive you absolutely crazy in the long-term

al

 

[ccarpediem]

The application is going to be a VERY simple document retrieval program. The use can define any number of document types and define what type of indexing data they want for each type.

The only other solutions I had thought of was to have to dynamically create a new table to store the indexing information for each new document type. Which of course means having to track what table goes with what document type dynamically in another table.

I was just REALLY hoping to avoid all of this. If there a easier solutions I am over looking?

 

[pcs]

there might be easier solutions...but think you are going to have to provide more details of what you are trying to achieve.

al