“The expression you entered has a function containing the wrong # of arguments”

[mdkarkowsky]

Hi,
Newbie here, still learning the basics. Thanks in advance for any help. I am working in VBA Access and feeding my code through a macro. I am getting the error message “The expression you entered has a function containing the wrong # of arguments.” I know this can be a pretty basic error but I can't find the problem. It's probably something silly. The strange thing is that the macro works fine and finishes making my table so I guess it has to do with some syntactical matter at the end (?)
Anyway, I appreciate any input. Code is below...
MK

Option Compare Database
 
Function ImportingDailyWarrantsFile()
 
Dim DateInput As String
Dim WarrMsg As String
Dim TableName As String
 
WarrMsg = "Pull warrants for which date [mmdd]?"
DateInput = InputBox(Prompt:=WarrMsg, Title:="InputBox Title")
TableName = "FT" & DateInput & "2013"
 
'Import the file
DoCmd.TransferText acImportDelim, "Regions Import Specification", TableName, "J:\Invest\Positive Pay\Download Files\FT" & DateInput & "2013.txt"
 
Call FormattingDailyWarrantsFile(TableName)
 
End Function    
 
Function FormattingDailyWarrantsFile(TableName)
 
FormatText = "SELECT CDbl(Left([Account],10)) AS [Account_No], CDbl(Mid([Account],11,10)) AS [Check_No], "
FormatText = FormatText & "CDbl(Mid([Account],21,10))/100 AS [Amount], "
FormatText = FormatText & "Cdate(Cstr(Mid([Account],33,2)) & '/' & Cstr(Mid([Account],35,2)) & '/' & Cstr(Mid([Account],31,2)))  AS [Date_Txt] "
FormatText = FormatText & "INTO " & TableName & "F FROM " & TableName
 
DoCmd.RunSQL FormatText
 
End Function

[Seth Schrock]

Try reading through Before Posting (VBA or SQL) Code, follow the directions in both A and B. This should tell us where the code is that is having the trouble if the error is on the VBA side.

I am working in VBA Access and feeding my code through a macro.

I'm not sure what you mean by "feeding my code through a macro." Does the macro call the VBA code or does the VBA code call the macro?

[zmbd]

Insert at line 8

STOP

Insert at line 26:

debug.print FormatText

run your code
The VBA should stop and the VBE open at line 8 with a yellow highlight.
press [F8] function key to step thru the code.

Two things to note:
At what point does the error occur
If you get to line 26, press [F8]
then press <ctrl><g> to open the immediates window.
Check that the string posted in this window matches what you think it should.
You should also post that string back here (you can cut and paste :) within this window) ... click on the [CODE/] button in the format bar and past the string between the [code]..............[/code] tags.

[mdkarkowsky]

Thanks all for your expert input. It was a silly rookie mistake and I found it. But all the suggestions helped me put it together...

[zmbd]

Would you mind posting back what the solution you found was?
You'd be surprised at how often "Rookie" mistakes are made by us "experts" and having that solution here can help jog the old grey cells...
(point in fact, I was working with one of my union queries and just couldn't figure it out until I was reading thru stuff hereon Bytes and realized that it was a scope issue in my SQL!)

[mdkarkowsky]

I was calling a second function both inside the first function and then also in the Macro. Its a matter of familiarizing myself with how the discrete chunks of code communicate with each other and pass information to each other. Also, physically clicking and running a macro seems like a whole different world than when functions are run straight from the code world. Just need some more experience. Thanks...

[zmbd]

Well,
That is true that when you execute a VBA function or sub via the macro interface, the scope changes greatly.

I am still courious about how that string resolved...

[mdkarkowsky]

the string was fine. the second function was called in the first function and the parameter passed effectively. Since I mistakenly had the second function as a RunSQL step in my macro (originally I thought that was the proper way to construct these things), that one did not have the proper parameter to work with.