Replies

Thanks Wendel, Hans
After reading your replies I ended up doing something different.
Created a temporary table to hold the data then append the data to the table with the correct datatype.
Finally delete the temporary table.
Thanks for your help,
Scott

Thanks Charlotte,
The split function did the trick. I have some users of Access97 and I was able to find this split
function on the Lotus web site. It works in Access97
Here it is for anyone interested.

Function Split(splitstr As String, delim As String, includeempties As Variant) As Variant
Dim words() As String
Dim holder As String
Dim x As Integer
Dim counter As Integer
Dim laststop As Integer
On Error GoTo errorhandler
ReDim words(0) As String
laststop = 1
For x = 0 To (Len(splitstr) – 1)
holder = Mid$(splitstr, x + 1, 1)
If (x 0) Then
If (Mid$(splitstr, x, 1) = delim) Then
If (includeempties = False) Then
laststop = x + 1
GoTo getnext
End If
End If
ElseIf ((x = 0) And (holder = delim)) Then
If (includeempties = False) Then
laststop = x + 1
GoTo getnext
End If
End If
If (x = Len(splitstr) – 1) Then
If (holder = delim) Then
words(UBound(words)) = Mid$(splitstr, laststop, (x + 1) – laststop)
laststop = x + 2
ReDim Preserve words(UBound(words) + 1) As String
Else
words(UBound(words)) = Mid$(splitstr, laststop, (x + 2) – laststop)
laststop = x + 2
ReDim Preserve words(UBound(words) + 1) As String
End If
Else
If (holder = delim) Then
If (x Len(splitstr) – 1) Then
words(UBound(words)) = Mid$(splitstr, laststop, (x + 1) – laststop)
laststop = x + 2
Else
words(UBound(words)) = Mid$(splitstr, laststop, x – (laststop – 1))
End If
ReDim Preserve words(UBound(words) + 1) As String
End If
End If
getnext:
Next x
If (UBound(words) > 0) Then
ReDim Preserve words(UBound(words) – 1) As String
Else
words(0) = “”
End If
Split = words
Exit Function
errorhandler:
words(0) = “”
Split = words
Exit Function
End Function

Mark,
Here is an application from VBCode.com for scanners.
Maybe you can adapt it in access
Scott

Steve,
Here is an example of open dialog box in Access97
Copy the API and the code on the form to suit your needs
Scott

Paul,
A new table is added each month with a new table name using the mm/yyyy_RMAP30 format.
I guess I’ll stick to the original idea of importing, copying to the back end and then deleting the original.
Thanks,
Scott

Paul,
I’m working from the front end of the database using:
DoCmd.TransferText acImportDelim, “RMAP_IMPORT”, Format(strDATE, “MM/YYYY”) & “_” & “RMAP30”, “C:” & Format(strDATE, “MM”) & “_” & “RMAP30.TXT”
to import. It imports into the front end not the back end like I need it to.
Scott

How do you import to a linked table?
Scott

Paul,
As long as it gets put into the back end.
Scott

Stewart,
I dont see any reference to GetCodeDBPath_TSB()
Can you provide?
Thanks,
Scott

Hans,
Many thanks. Up and working.
Scott

Thanks Hans,
Here is another * to ad to your 4 Star rating
Scott

Cecilia,
I don’t think a input mask can accept an underscore( _ )
If they type in ATLANTA GA or type in HOME DEPOT in the field, I want to reject it.
They should type in ATLANTA_GA or HOME_DEPOT as the instructions say.
The longest the string of characters would be is about 20.
Scott

Looking for code in the BeforeUpdate event to check for the conditions listed above.
I could do it with a select case stmt but I would have to have it check for all the characters in the string
except the ones that I wanted. Or use instr to search for everything I don’t want.
I was looking for an easier way.
Maybe I’m not explaining myself clear enough. If input A to Z or _, msgbox “Use only A to Z or _”

Cecilia,
Not an Input mask. I want to restrict the user from entering other than A to Z or an underscore.
I don’t want them to be able to enter a * ,& , %, any other punctuation, or a space.
The input is used in some code to generate some tables and do some other processing and the non alphabet characters cause errors.
Scott

Gary,
I’m entering the info into a field on a form then does some VBA processing
Thanks