467,926 Members | 1,396 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,926 developers. It's quick & easy.

compile errors Expected expression

I have received alot of help from this site and I am grateful. My problem is that I was able to get the first part of code to work. I tested it via the immediate window. I received the compile error "expected expression" when I tested for function InputFile.

?InputFile(Optional strPath As String)
"expected expression"

?InputFile()
"expected function or variable"
Below is the code that I was given-with slight modification

Expand|Select|Wrap|Line Numbers
  1. Public Function readFile(Optional strPath As String) As String
  2.     'Create a filesystemobject
  3.         Dim fso As Object
  4.         Set fso = CreateObject("scripting.filesystemobject")
  5.  
  6.     'Open file for reading
  7.         strPath = "C:\Users\jeffrey.musgray\Desktop\TcossDocument.txt"
  8.         readFile = fso.OpenTextFile(strPath, 1).ReadAll
  9.  
  10.     'Cleanup
  11.         Set fso = Nothing
  12.  
  13. End Function
  14.  
  15. Public Sub InputFile(Optional strPath As String)
  16.      Dim strContent As String
  17.      strContent = readFile(strPath)
  18.  
  19.      Dim rsDao As DAO.Recordset
  20.      Set rsDao = CurrentDb.OpenRecordset("SELECT * FROM tblTSR", dbOpenDynaset)
  21.      rsDao.AddNew
  22.      With rsDao
  23.         ![TSR Number] = getValue(strContent, 101, 103)
  24.         ![Type Action] = getValue(strContent, 103, 104)
  25.         ![TYPE OF SERVICE] = getValue(strContent, 104, 105)
  26.         ![Requesting Activity's Requirement Number] = getValue(strContent, 514)
  27.      End With
  28.      rsDao.Update
  29.      Set rsDao = Nothing
  30.  
  31. End Sub
Mar 4 '11 #1

✓ answered by gershwyn

InputFile is not a function, it is a subroutine. Because it does not return a value, it is not legal to print it (the ? is a shortcut for the print command.)

Also, the subroutine takes an optional parameter, which is declared as a string. You don't have to pass anything, but if you do it needs to be a string. Your first attempt is just repeating the sub declaration, which makes no sense in the immediate window.

If you want to test your code from the immediate window, the syntax to call it would be either of the following (with or without a parameter):
Expand|Select|Wrap|Line Numbers
  1. InputFile
  2. InputFile "c:\path\to\files\"

  • viewed: 7527
Share:
10 Replies
100+
InputFile is not a function, it is a subroutine. Because it does not return a value, it is not legal to print it (the ? is a shortcut for the print command.)

Also, the subroutine takes an optional parameter, which is declared as a string. You don't have to pass anything, but if you do it needs to be a string. Your first attempt is just repeating the sub declaration, which makes no sense in the immediate window.

If you want to test your code from the immediate window, the syntax to call it would be either of the following (with or without a parameter):
Expand|Select|Wrap|Line Numbers
  1. InputFile
  2. InputFile "c:\path\to\files\"
Mar 4 '11 #2
Thank you sir for your reply-I am fairly new at the VBA side of access. I tried what you said and I still get compile errors. I am just trying to import a text file-the complete code is attached. Can you tell me what is wrong with this code (I know I need to correct the heart)Thanks in advance

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Public Function readFile(Optional strPath As String) As String
  4.     'Create a filesystemobject
  5.         Dim fso As Object
  6.         Set fso = CreateObject("scripting.filesystemobject")
  7.  
  8.     'Open file for reading
  9.         strPath = "C:\Users\jeffrey.musgray\Desktop\TcossDocument.txt"
  10.         readFile = fso.OpenTextFile(strPath, 1).ReadAll
  11.  
  12.     'Cleanup
  13.         Set fso = Nothing
  14.  
  15. End Function
  16.  
  17. Public Sub InputFile(Optional strPath As String)
  18.      Dim strContent As String
  19.      strContent = readFile(strPath)
  20.  
  21.      Dim rsDao As DAO.Recordset
  22.      Set rsDao = CurrentDb.OpenRecordset("SELECT * FROM tblTSR", dbOpenDynaset)
  23.      rsDao.AddNew
  24.      With rsDao
  25.         ![TSR NUMBER] = getValue(strContent, 101, 103)
  26.         ![TYPE OF ACTION] = getValue(strContent, 103, 104)
  27.         ![TYPE OF SERVICE] = getValue(strContent, 104, 105)
  28.         ![NETWORK REQUIREMENT] = getValue(strContent, 105, 1061)
  29.         ![OPERATIONAL SVC DATE] = getValue(strContent, 1061, 1062)
  30.         ![REQUESTED SVC DATE] = getValue(strContent, 1062, 107)
  31.         ![CCSD TRUNK ID] = getValue(strContent, 104, 105)
  32.         ![PURPOSE USE CODE] = getValue(strContent, 104, 105)
  33.         ![TYPE OPERATION] = getValue(strContent, 104, 105)
  34.         ![MODULATION BANDWIDTH] = getValue(strContent, 104, 105)
  35.         ![SVC AVAILABILITY] = getValue(strContent, 104, 105)
  36.         ![SIGNALING MODE] = getValue(strContent, 104, 105)
  37.         ![COMM SVC AUTHORIZATION] = getValue(strContent, 104, 105)
  38.         ![PROG DESIGNATOR CODE] = getValue(strContent, 104, 105)
  39.         ![OTIME EXPEDITING CHARGES] = getValue(strContent, 104, 105)
  40.         ![TRANSMISSION MEDIA AV] = getValue(strContent, 104, 105)
  41.         ![TERMINAL NODE LOCATION] = getValue(strContent, 104, 105)
  42.         ![STATE COUNTRY CODE] = getValue(strContent, 104, 105)
  43.         ![AREA CODE] = getValue(strContent, 104, 105)
  44.         ![FACILITY CODE] = getValue(strContent, 104, 105)
  45.         ![ADDRESS SITE] = getValue(strContent, 104, 105)
  46.         ![ROOM NUMBER] = getValue(strContent, 104, 105)
  47.         ![TYPE CRYPTO EQUIPMENT] = getValue(strContent, 104, 105)
  48.         ![INTERFACE] = getValue(strContent, 104, 105)
  49.         ![USER TECHNICAL POC] = getValue(strContent, 104, 105)
  50.         ![MAIL ADDRESS] = getValue(strContent, 104, 105)
  51.         ![UNIT IDENTIFICATION] = getValue(strContent, 104, 105)
  52.         ![TERMINAL NODE LOCATION 2] = getValue(strContent, 104, 105)
  53.         ![STATE COUNTRY CODE 2] = getValue(strContent, 104, 105)
  54.         ![AREA CODE 2] = getValue(strContent, 104, 105)
  55.         ![FACILITY CODE 2] = getValue(strContent, 104, 105)
  56.         ![ADDRESS SITE 2] = getValue(strContent, 104, 105)
  57.         ![ROOM NUMBER 2] = getValue(strContent, 104, 105)
  58.         ![TERMINAL EQUIPMENT 2] = getValue(strContent, 104, 105)
  59.         ![TYPE CRYPTO EQUIPMENT 2] = getValue(strContent, 104, 105)
  60.         ![INTERFACE 2] = getValue(strContent, 104, 105)
  61.         ![USER TECHNICAL POC 2] = getValue(strContent, 104, 105)
  62.         ![MAIL ADDRESS 2] = getValue(strContent, 104, 105)
  63.         ![UNIT IDENTIFICATION 2] = getValue(strContent, 104, 105)
  64.         ![NARRATIVE INFORMATION] = getValue(strContent, 104, 105)
  65.         ![TSR CONTACT] = getValue(strContent, 104, 105)
  66.         ![NATIONAL SEC SYSTEM] = getValue(strContent, 104, 105)
  67.         ![CMO ACCEPT SERVICE] = getValue(strContent, 104, 105)
  68.         ![SECURITY REQUIREMENTS] = getValue(strContent, 104, 105)
  69.         ![SHIPPING INFORMATION] = getValue(strContent, 104, 105)
  70.         ![EXERCISE PROJECT NAME] = getValue(strContent, 104, 105)
  71.         ![COST THRESHOLD] = getValue(strContent, 104, 105)
  72.         ![REMARKS] = getValue(strContent, 104, 105)
  73.         ![ESTIMATED SVC LIFE] = getValue(strContent, 104, 105)
  74.         ![CPIWI] = getValue(strContent, 104, 105)
  75.         ![CPIWM] = getValue(strContent, 104, 105)
  76.         ![FUNDING TCO APPROVAL] = getValue(strContent, 104, 105)
  77.         ![REQ ACTIVITY REQ NUMBER] = getValue(strContent, 514)
  78.      End With
  79.      rsDao.Update
  80.      Set rsDao = Nothing
  81.  End Sub
  82.  
  83. Public Function getValue(strInput As String, lngStart As Long, Optional lngEnd As Long = 0)
  84.     'Get starting location
  85.         Dim lngPosStart As Long
  86.         Dim strStart As String
  87.         strStart = vbCrLf & lngStart & "."
  88.         lngPosStart = InStr(1, strInput, strStart) + Len(strStart)
  89.     'Get end location (if any)
  90.         Dim lngPosEnd As Long
  91.         If lngEnd <> 0 Then
  92.             lngPosEnd = InStr(1, strInput, vbCrLf & lngEnd & ".")
  93.         Else
  94.             lngPosEnd = Len(strInput)
  95.         End If
  96.  
  97.     getValue = Mid(strInput, lngPosStart, lngPosEnd - lngPosStart)
  98.  
  99. End Function
  100.  
Mar 4 '11 #3
NeoPa
Expert Mod 16PB
Hi Jeffry.

You seem to be trying your best to work with us so I will just gently point you towards next steps.

When correcting code that doesn't compile you have to take each error in turn and fix it. The clue for each is in the error message so, I'm afraid, just posting a whole module here and asking us to find & fix the problems isn't a good way forward. Even if the code were mine I'd be looking to see what the compiler told me are the problems. To do it blind simply doesn't make sense.

So, that means you need to take one error at a time; read the message; attempt to determine what is wrong from that; if you can't work it out then feel free to post the question with the compiler error message. It may seem laborious but even the experts have to go through this when dealing with a bunch of code they didn't create themselves. It's actually very common for those of us that work as consultants to have to deal with databases that simply don't work.

So, step-by-step, find & fix all the problems. We'll be happy to help where required if provided with the appropriate information.

Gershwyn's post is actually a very good response to your original question so I will leave it in place as Best Answer regardless of where this goes from here.
2 Weeks Ago #4
ADezii
Expert 8TB
The Code is a little confusing to me, are you trying to extract Sub-Strings, given specific Start and End Values, from a larger String? If this is the case, it is a relatively simple matter to resolve.
2 Weeks Ago #5
isladogs
Expert 128KB
Jeffrey last replied in 2011. I believe this thread was resurrected by someone else whose post has since been (re)moved.
2 Weeks Ago #6
ADezii
Expert 8TB
Thanks isladogs, getting senile!
2 Weeks Ago #7
NeoPa
Expert Mod 16PB
You & me both ADezii. I failed to spot that too (blush)!

It may well be that Jeffry won't be responding to this any more :-D
2 Weeks Ago #8
isladogs
Expert 128KB
I've done the same thing myself on numerous occasions. The question is, what happened to the post that resurrected this thread?
2 Weeks Ago #9
NeoPa
Expert Mod 16PB
That post was deleted by me. I didn't spam-ban them as it had no link and I just saw no sense to it at all. It was positioned before post #4. Do you not see that? It may be that such info is only visible to Moderators. Can you confirm for me.

What I see is :

Attached Images
File Type: jpg Screenshot20210219.Jpg (17.6 KB, 135 views)
2 Weeks Ago #10
isladogs
Expert 128KB
Just to confirm, its only visible to mods
2 Weeks Ago #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Ravi Chaudhary | last post: by
1 post views Thread by Ray Wampler | last post: by
1 post views Thread by Ravi Chaudhary | last post: by
2 posts views Thread by donkeyboy | last post: by
1 post views Thread by =?Utf-8?B?SG93YXJkIFBpbnNsZXk=?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.