i have a linked table that where all fields come in as text. my question is about the date which is either 2/12/02 or 2/2/02 or 12/2/02. i need the date of the month. i can't use the mid function because of the different placement of the date. any suggestions?
part of date
- Thread starter deekras
- Start date
David R
I know a few things...
- Local time
- Today, 14:15
- Joined
- Oct 23, 2001
- Messages
- 2,632
Let me make sure I understand what you're asking: You have some data that comes to you as straight text, you import it into Access, and then you need to parse out the day of the month?
As long as they always come to you in a format that matches your computer's settings (i.e. MM/DD/YY or DD/MM/YY, not both interspersed), this is fairly simple.
When you go to Import your data from the text file, click on Advanced and create an Import Spec. You will only have to do this once and then you can use that Spec on future files. Format the date fields as Date/Time and then use the Day() function to extract the day of the month for your purposes.
If for some reason you can't do this, try using Day(CDate([TextDateField])) but that is a kludge and I wouldn't recommend it if you can get around it.
HTH,
David R
As long as they always come to you in a format that matches your computer's settings (i.e. MM/DD/YY or DD/MM/YY, not both interspersed), this is fairly simple.
When you go to Import your data from the text file, click on Advanced and create an Import Spec. You will only have to do this once and then you can use that Spec on future files. Format the date fields as Date/Time and then use the Day() function to extract the day of the month for your purposes.
If for some reason you can't do this, try using Day(CDate([TextDateField])) but that is a kludge and I wouldn't recommend it if you can get around it.
HTH,
David R
the problem is that i can only use it as a linked table (and i can't change the format of the fields). if i don't use it as a linked table, then when i import it i get weird characters in front of the data, but in excel it is fine. strange.
David R
I know a few things...
- Local time
- Today, 14:15
- Joined
- Oct 23, 2001
- Messages
- 2,632
Try the CDate() version then. If it's a valid date format, it should convert fine and then you can do manipulations on it.
Make a field in a query, even, if you like:
ActualDate: CDate([WonkyTextDateField]). Then base all your forms/reports on this new query with htis field and you'll never have to see the text field again.
Make a field in a query, even, if you like:
ActualDate: CDate([WonkyTextDateField]). Then base all your forms/reports on this new query with htis field and you'll never have to see the text field again.
i have to send letters to those people. each letter is based on the fields inthe table. the letter will include lots of iif statements. i have the letter already set up, it works for a different table, now i just want to adjust it.
Similar threads
