Take a value from Query

[DanJames]

New to VBA. I have an if statement and its something like If this = true then
text1 = "...payments due including " & (by here i want it to say the value of record1(the first cell thing) in the query "qryPaymentsDue"

So the text box would say something like There are 7 payments due including John James. as the first cell in "qryPaymentsDue says "John James". Any Ideas, Thanks.

 

[DCrake]

You new column should look one the similar lines of:

Msg:"There " & IIF([Howmany]=0,"are no payments ",IIF([Howmany]=1,"is " & [Howmany] & "payment" ,"are " & [Howmany] & " payments due")) & " due Including " & [FirstColumnFieldName]

This syntax also takes care of grammatics based on quantity.

ie
are no payments
is 1 payment
are 2 payments

The [Howmany] is the sum of the number of records in your query
The [FirstColumnFieldName] is the column that holds the persons name

David

 

[DanJames]

I've tried that but how does it know what query to take the value from ect. Also Id ont want it in a msgBox.. Heres the code I've used:

If Forms!PaymentsDue!List14.ListCount >= 1 Then
Forms!Form1!No5.Value = "There are " & Forms!PaymentsDue!List14.ListCount & " clients with payments due at the moment including" & "by here i want the value of Full_Name first cell in QryPaymentsDue"
Else
Forms!Form1!No5.Value = "no payments due"

This works but doesnt show the last bit of the name, If I could use this If statement, and someone can tell me the QueryValue bit.

Thanks Alot, Dan.

 

[DCrake]

There is a thread missing from this post. Can you send a copy of your mdb to test.

David

 

[DanJames]

Its a a large database that I'm not allowed to send over the web. But, any help will be appreciated? Thanks, Dan.