Changing format of imported spreadsheet field

[ITguy1981]

I currently have a database that compares some data between two tables such as firstname, lastname, and dob to see if there is a match between two tables. The second table is created from a spreadsheet that is received monthly. In the past the DOB field has been a short date. Recently the database stopped working because the DOB field has changed format on the spreadsheet. Instead of it being dd/mm/yyyy format it has gone to just a string of numbers. For instance, 05/24/2016 is now 20160524 which I can't import due to inconsistent format. I'm wondering how to go about fixing this problem.

 

[sneuberg]

If the string has a consistent format then you could convert it with an expression like:

CDate(Mid(strDate, 5, 2) & "/" & Right(strDate, 2) & "/" & Left(strDate, 4))

where you would need to substitute the field name for the strDate in this.

 

[ITguy1981]

Thank you for the reply. Right now the database imports the excel data in to a table. It pretty much shoots the data in to matching fields from the excel sheet to the matching fields of the table. I guess I need a bit more help figuring out where to put conversion as well. Right now it runs from visual basic. I can't import the data unless I somehow change the format of the data to an actual date before it's imported in to the table or change the data type of the field to text and then later convert the string to a date. If I do the conversion afterward would I just make a query to change the field entries?

MY CURRENT VBS for import of data.

Private Sub Update_Click()

On Error GoTo Err_Update_Click

With DoCmd
.SetWarnings False
.OpenQuery "updated table delete query"
.SetWarnings True
.TransferSpreadsheet transfertype:=acImport, tablename:="Updated Table", FileName:=Me.BrowseTextBox, HasFieldNames:=True
End With

Exit_CmdImportExcel_Click:

Exit Sub

Err_Update_Click:

MsgBox Err.Description

Resume Exit_Update_Click

Exit_Update_Click:

End Sub

 

[sneuberg]

Yes, create a query of the Update Table with that expression and use the query in place of direct access to the Update Table. Hopefully that table isn't accessed in a lot of different places.

 

[ITguy1981]

Noob question. Does that go in "Update To"? Do I need a criteria?

I would assume it should look like

Field: DOB
Table: Updated Table
Update To: CDate(Mid(DOB, 5, 2) & "/" & Right(DOB, 2) & "/" & Left(DOB, 4))

I get an error "Data type mismatch in criteria expression". I'm assuming this is because the field type in the table is text and not date? I can't change the field type because then it can't import the data from the excel sheet to the table.

Thanks for your help thus far.

 

[sneuberg]

I didn't mean to update the table with a query albeit it would be nice if you could do it that way that directly. I meant to create a select query of the Update Table with this expression and use that query in place of the Update Table. This would mean the field name would be different as you could use DOB so you would have to change that wherever it is used.

I different way, more like what you were trying to do, would be to write code to:

1. Add a Date field to the Update Table, lets say DOBTemp
2. Use the expression to update DOBTemp from DOB
3. Remove or rename the DOB field
4. Rename DOBTemp to DOB

You would do most of this with the Alter Table statements https://msdn.microsoft.com/en-us/library/bb177883(v=office.12).aspx

One more thing. If Access is considering DOB a number instead of text you may have to add CStr to the expression like

CDate(Mid(CStr(DOB), 5, 2) & "/" & Right(CStr(DOB), 2) & "/" & Left(CStr(DOB), 4))

 

[sneuberg]

Here's some code that might do what you want.

CurrentDb.Execute "ALTER TABLE [Update Table] ADD COLUMN DOBTemp Date;"
CurrentDb.Execute "UPDATE [Update Table] SET [Update Table].DOBTemp = CDate(Mid(CStr([DOB]),5,2) & '/' & Right(CStr([DOB]),2) & '/' & Left(CStr([DOB]),4));"
CurrentDb.TableDefs("Update Table").Fields("DOB").Name = "DOBString"
CurrentDb.TableDefs("Update Table").Fields("DOBTemp").Name = "DOB"

 

[ITguy1981]

Thanks for the information. I don't do a lot of databases, but I guess I know a bit more than the average user. I have Table one and Table two. Both have identical fields except the second table's data comes from an imported spreadsheet. I have a query for a report that lists a match of compared data, but it's not done using normal query methods. It's typed in SQL I believe.
I'll see what I can do. If I can actually find a way to update the data in the table I'll let you know.

 

[sneuberg]

. If I can actually find a way to update the data in the table I'll let you know.

Maybe you didn't see it but the code in my last post should do that for you.

 

[ITguy1981]

Sorry for the late reply. I just now had some time to actually fix this thing. your suggestion helped using the code to update the table. I used that to make a temp field and that worked. This process has to be done repeatedly as the excel import is changed monthly so I had to add some code to rename the fields back and drop the extra temp field before the import, but it's working. Thanks a lot.