Wrong amount of arguments!

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

What I want is tblInvoice.ClientDetail but if blank tblInvoice.HorseName but
if blank
funGetHorseName(tblInvoice.InvoiceID,tblInvoice.HorseID),tblInvoice.HorseName)

& "iif(tblInvoice.ClientDetail='',tblInvoice.HorseName='
',funGetHorseName(tblInvoice.InvoiceID,tblInvoice.HorseID),tblInvoice.HorseName)
AS ClientDetail," _
 
Not sure I'm following comletely correctly, but perhaps this is what you
seek:

IIf(Len(tblInvoice.ClientDetail & "") = 0, IIf(Len(tblInvoice.HorseName &
"") = 0, funGetHorseName(tblInvoice.InvoiceID, tblInvoice.HorseID),
tblInvoice.HorseName), tblInvoice.ClientDetail)
 
Ken Snell said:
Not sure I'm following comletely correctly, but perhaps this is what you
seek:

IIf(Len(tblInvoice.ClientDetail & "") = 0, IIf(Len(tblInvoice.HorseName &
"") = 0, funGetHorseName(tblInvoice.InvoiceID, tblInvoice.HorseID),
tblInvoice.HorseName), tblInvoice.ClientDetail)
Click to expand...
Thanks Ken, How do I incorerate it into this string....Regards Bob

Private Sub Report_Open(Cancel As Integer)

Me.Caption = "Monthly Horse Invoice Report"

Dim strSQL As String

strSQL = "SELECT tblInvoice.InvoiceDate, tblInvoice.InvoiceNo," _
& "tblInvoice.OwnerName, tblInvoice.TotalAmount," _
& "tblInvoice.OwnerPercentAmount FROM tblInvoice WHERE" _
& " tblInvoice.OwnerID=Forms!frmMonthlyClientInvoice!cbHorseName.value" _
& " AND tblInvoice.InvoiceDate>=" &
Format("Forms!frmMonthlyClientInvoice!tbDateFrom.value", "mm/dd/yyyy") _
& " AND tblInvoice.InvoiceDate<=" &
Format("Forms!frmMonthlyClientInvoice!tbDateTo.value", "mm/dd/yyyy") & ";"
Report.RecordSource = strSQL


End Sub
 
Sorry, I am not seeing anything in the VBA code where it appears logical for
the expression to be inserted? Could you provide more information? Is this
expression meant to replace one of the fields currently in your string? Or
to be a new field in the string?
 
Thanks Ken, my Original code for reporting Invoices for horses is below I am
trying to create a code for reporting Invoices for Clients
Regards Bob

Private Sub Report_Open(Cancel As Integer)

Me.Caption = "Monthly Horse Invoice Report"
Dim strSQL As String
strSQL = "SELECT tblInvoice.InvoiceDate, tblInvoice.InvoiceNo," _
& "tblInvoice.OwnerName, tblInvoice.TotalAmount," _
& "iif(tblInvoice.HorseName='
',funGetHorseName(tblInvoice.InvoiceID,tblInvoice.HorseID),tblInvoice.HorseName)
AS HorseName," _
& "tblInvoice.OwnerPercentAmount FROM tblInvoice WHERE" _
& " tblInvoice.HorseID=Forms!frmMonthlyHorseInvoice!cbHorseName.value" _
& " AND tblInvoice.InvoiceDate>=" &
Format("Forms!frmMonthlyHorseInvoice!tbDateFrom.value", "mm/dd/yyyy") _
& " AND tblInvoice.InvoiceDate<=" &
Format("Forms!frmMonthlyHorseInvoice!tbDateTo.value", "mm/dd/yyyy") & ";"
Report.RecordSource = strSQL

End Sub
-------------------------------------------------------
My Code for Client reporting Invoices!

Private Sub Report_Open(Cancel As Integer)

Me.Caption = "Monthly Client Invoice Report"
Dim strSQL As String
strSQL = "SELECT tblInvoice.InvoiceDate, tblInvoice.InvoiceNo," _
& "tblInvoice.OwnerName, tblInvoice.TotalAmount," _
& "tblInvoice.OwnerPercentAmount FROM tblInvoice WHERE" _
& " tblInvoice.OwnerID=Forms!frmMonthlyClientInvoice!cbClientName.value" _
& "IIf(Len(tblInvoice.ClientDetail & "") = 0, IIf(Len(tblInvoice.HorseName &
"") = 0, funGetHorseName(tblInvoice.InvoiceID,
tblInvoice.HorseID),tblInvoice.HorseName), tblInvoice.ClientDetail)" _
& " AND tblInvoice.InvoiceDate>=" &
Format("Forms!frmMonthlyClientInvoice!tbDateFrom.value", "mm/dd/yyyy") _
& " AND tblInvoice.InvoiceDate<=" &
Format("Forms!frmMonthlyClientInvoice!tbDateTo.value", "mm/dd/yyyy") & ";"
Report.RecordSource = strSQL

End Sub
 
Assuming you want that calculated field for which I gave the expression to
be in the SELECT clause (watch for the mutiple, consecutive " characters):

strSQL = "SELECT tblInvoice.InvoiceDate, tblInvoice.InvoiceNo," _
& "tblInvoice.OwnerName, tblInvoice.TotalAmount," _
& "tblInvoice.OwnerPercentAmount, " & _
"IIf(Len(tblInvoice.ClientDetail & """") = 0, IIf(Len(tblInvoice.HorseName &
" & _
""""") = 0, funGetHorseName(tblInvoice.InvoiceID, tblInvoice.HorseID), " & _
"tblInvoice.HorseName), tblInvoice.ClientDetail) AS ClientDetailField" & _
" FROM tblInvoice WHERE" _
& " tblInvoice.OwnerID=Forms!frmMonthlyClientInvoice!cbClientName.value" _
& " AND tblInvoice.InvoiceDate>=" & _
Format("Forms!frmMonthlyClientInvoice!tbDateFrom.value", "mm/dd/yyyy") _
& " AND tblInvoice.InvoiceDate<=" & _
Format("Forms!frmMonthlyClientInvoice!tbDateTo.value", "mm/dd/yyyy") & ";"
 
Ken I am getting this error
The database engine can not find..........Thanks Bob
"IIf(Len(tblInvoice.ClientDetail & """") = 0, IIf(Len(tblInvoice.HorseName &
"" & """") = 0, funGetHorseName(tblInvoice.InvoiceID, tblInvoice.HorseID), "
& _
"tblInvoice.HorseName), tblInvoice.ClientDetail) AS ClientDetailField" & _
" FROM tblInvoice WHERE" _
 
Try this:

strSQL = "SELECT tblInvoice.InvoiceDate, tblInvoice.InvoiceNo," _
& "tblInvoice.OwnerName, tblInvoice.TotalAmount," _
& "tblInvoice.OwnerPercentAmount, " & _
"IIf(Len(tblInvoice.ClientDetail & """") = 0, " & _
"IIf(Len(tblInvoice.HorseName & " & _
""""") = 0, funGetHorseName(tblInvoice.InvoiceID, tblInvoice.HorseID), " & _
"tblInvoice.HorseName), tblInvoice.ClientDetail) AS ClientDetailField" & _
" FROM tblInvoice WHERE" _
& " tblInvoice.OwnerID=Forms!frmMonthlyClientInvoice!cbClientName.value" _
& " AND tblInvoice.InvoiceDate>=" & _
Format(Forms!frmMonthlyClientInvoice!tbDateFrom.value, _
"\#mm\/dd\/yyyy\#") _
& " AND tblInvoice.InvoiceDate<=" & _
Format(Forms!frmMonthlyClientInvoice!tbDateTo.value, _
"\#mm\/dd\/yyyy\#") ") & ";"
 
Ken i am getting a error ' expect end of statement on -> ")& "
4 of the last six symbols
Thanks bob
 
Thanks Ken got it going now, just gatting a parameter error on ClientDetail
Will try and work it out
Regards Bob
 
Back
Top