ODBC link

[deb]

I have an Access database with a ODBC link to a Lotus Notes Database.

The link creates all fields in the table as Text. Even the date fields.

I have created a "Make Table" and then changed the fields to Date, but when I run the "make table" again it changes the fields back to Text.

I have tried creating queries and setting property to date format.

Nothing works.

Please help with this!!!

 

[Russell Hauf]

In your make table query, try bringing the date in as something like:

MyDate: DateValue([NotesDate])

HTH,

Russell

 

[deb]

In the link, it is impossible to change the way the field is dumped into Access. When I try to change the field type, it tells me the change is not possible.

I even tried to create a table from this table but when one is updated, both change back to the text field.

I think I need some type of macro to change the table field type to date. I can run this macro after the tables are updated with the new data.

I don't know how to write a macro that will change the Table field type to Date instead of text.


Thank you sooo much for helping.

 

[Russell Hauf]

You don't want to change the linked table.
Your make-table query should not change any other table...so I'm not sure what is really happening there. Can you post the SQL of your make-table query? If you're not sure how to do this, open the query in Design view, then go to View-SQL View.

Thank you,

Russell

 

[deb]

I have a table linked via ODBC to lotus notes db.
Then I use a Make table query(below), using the fields from the linked table, to create a new table.

I change the fields in the new table to date instead of text. I even tried changing the properties in the Make table query to be formatted as date.
When I run the make table query again(to update the data) it changes the fields back to text.


Make Table Query...

SELECT [02__Projects_k__evista_view].Project, [02__Projects_k__evista_view].UnitNumber, [02__Projects_k__evista_view].Status, [02__Projects_k__evista_view].ShopOrder, [02__Projects_k__evista_view].UnitActFirstFire, [02__Projects_k__evista_view].UnitFcFirstFire, [02__Projects_k__evista_view].UnitSchFirstFire, Mid([UnitActFirstFire],6,3)+Mid([UnitActFirstFire],9,2)+"-"+Left([UnitActFirstFire],4) AS Act INTO Tbl_PPI_2K_Evista
FROM 02__Projects_k__evista_view;

It's making me crazy...

 

[Russell Hauf]

Which field is the date, and can you give me a couple of example values from this field?

Thank you,

Russell

 

[deb]

There are 3 fields that should be dates...
UnitActFirstFire UnitFcFirstFire UnitSchFirstFire

Examples of how they are loading as text...
UnitActFirstFire UnitFcFirstFire UnitSchFirstFire
2002-03-23 2002-03-24 2001-12-31
2002-03-31 2002-03-29 2002-01-30

Want this field to be a date...
03/23/2002 so I can run queries and reports using the date functions.


Mid([UnitActFirstFire],6,3)+Mid([UnitActFirstFire],9,2)+"-"+Left([UnitActFirstFire],4) AS Act INTO Tbl_PPI_2K_Evista

This was my attempt to force the date format. Didn't work so good!!

 

[Russell Hauf]

What you are doing is concatenating 4 strings - the result? A string!

Instead of:

Mid([UnitActFirstFire],6,3)+Mid([UnitActFirstFire],9,2)+"-"+Left([UnitActFirstFire],4) AS Act

Try:

DateValue([UnitActFirstFire]) as Act

HTH,

Russell

 

[deb]

You are my "new best friend"
Not to mention just plain briliant!!


Thank you!!!!

 

[deb]

I ran a query using this "Act" field as a grouping and it will not group with this field.

The month area is blank.

Do you have any idea why, or how I can fix this?


The Month grouping is blank, and it does not group the fields