Wrong amount of arguments!

[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," _

 

[Ken Snell]

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)

 

[Bob Vance]

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)

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

 

[Ken Snell]

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?

 

[Bob Vance]

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

 

[Ken Snell]

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") & ";"

 

[Bob Vance]

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" _

 

[Ken Snell]

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\#") ") & ";"

 

[Bob Vance]

Ken i am getting a error ' expect end of statement on -> ")& "
4 of the last six symbols
Thanks bob

 

[Bob Vance]

Thanks Ken got it going now, just gatting a parameter error on ClientDetail
Will try and work it out
Regards Bob

 

[Bob Vance]

Got it
changed AS ClientDetailField" to AS ClientDetail"
Brilliant thanks Bob

 

[Ken Snell]

You're welcome. Glad you were able to "fix" those last few items!