Importing Data when some of the fields are blank

[Moxioron]

Hello.

I am trying to import a date field that is sometimes blank into a table. If I leave the Data Type as text all is well. However, I want to provide the users the ability to search date ranges using this particular field.

When I change the Data Type to Date/Time format it brings in all of the data fine until it reaches a field that is blank and then stops. I have required set to No and I am at a loss as to what to do next.

Any thoughts? Thanks for your help.

 

[alansidman]

I would import the dates as text. Then I would run a query to change the text date to date format using the CDate function. You can then change the query to an update query or leave it and use the new field as necessary.

Alan

 

[Joe4]

I have no problems importing null values into date fields.

Are you trying to import a null value, or blank spaces?
I would take a closer look at your data, and how your field is formatted, because it should work.

 

[Moxioron]

Some of the fields are actually blank.

I used the code below for some of the fields and it is working on two of the three. Any idea why it won't pull anything after MICRAcctNumber?

'Get MICR Serial #, Account #, and Routing Number
If StrComp(Mid(strFileData, 1, 12), "Capture MICR", vbBinaryCompare) = 0 Then
If Trim(Mid(strFileData, 30, 10)) <> "" Then
strMICRSerial = Mid(strFileData, 30, 10)
Else
strMICRSerial = "0"
End If
If Trim(Mid(strFileData, 56, 9)) <> "" Then
strMICRRT = Mid(strFileData, 56, 9)
Else
strMICRRT = "0"
End If
If Trim(Mid(strFileData, 80, 13)) <> "" Then
strMICRAcctNumber = Mid(strFileData, 80, 13)
Else
strMICRAcctNumber = "0"
End If
End If

 

[Joe4]

Without seeing your data and what is in spaces 80-92, I don't think we can really say what the problem is.

 

[Moxioron]

Here is where the import stops in the text file:

Capture MICR - Serial: Trrt: 222222222 Acct: 12345678910
Credit MICR - Serial: Trrt: Acct:

Every transaction up to the one above has the Trrt: and the Acct: on the Credit MICR - Serial line.

Occassionally these fields are blank and that is why I am having issues with the import.

Hope that helps. Thanks for your help.

 

[Joe4]

Let's take a step back, why are you doing the import that way (by code) instead of a straightforward text file import?

I have a feeling that your issue may have to do with those blank record fields. Those records may be less than 92 spaces, throwing your import off. So maybe you need to include a length check in your code.

 

[Moxioron]

We are using the Switchboard Manager for users to select the button and import the text file (with message boxes, etc.). I had all of the Data Types in the table set to Text and never had an issue with blank fields.

However, I wanted to format the fields appropriatly so users could perform searches on queries that we built. That is when I started to get this problem. I changed the Data Type to Number/Double/Fixed and here we are.

The text file is very large and the fields are all over the place, we tried to use the TransferText Macro, but the text file is very messy so it was easier to build a module and identify specific fields.

Hope that helps.

 

[Joe4]

You might be better off then to import them as all text, like your did before, but to a Temporary Table. You could then use an Append Query to format them appropriately and write them to your final (permanent) table in your desired format.

 

[Moxioron]

Thanks for help and insight.

I created an append query and it worked fine except I got the 'Microsoft Access can't append all the records in the append query'

I found out why. One field, which I set the Data Type to 'Date/Time' in the formated table is a zero in the original table and for some reason causes this message. I select 'yes' and it imports the entire row that the field is on, but I don't want that message.

Any thoughts on how I can get around this message?