elseif query in vba (1 Viewer)

[NickNeville]

Good afternoon all

could you kindly help with the following pls.

If Me.Card_Issue_Sub_Form_Subform!ShootType = "Ladies Prepaid 60" Then
MsgBox " Please Check Membership Card Punch Holes ", 48, "CHECK MEMBERS CARD !"
ElseIf Me.Card_Issue_Sub_Form_Subform!ShootType = "Members Prepaid 60" Then
MsgBox " Please Check Membership Card Punch Holes ", 48, "CHECK MEMBERS CARD ! "


AS you can see I have used the elseif code but is there a way I can just run the code with just searching for the "prepaid' in the string rather than repeating the elseif ?

Also if I wanted to count the "prepaids" like say
If prepaid and count > 3 then
is this also possible ?

Thanks
Nick

 

[DCrake]

Instead of using the = operater change it to the Like operator

If [field] Like "*prepaid*" Then

End If

 

[NickNeville]

great thankyou, works a treat.

Can you help with the count bit also or should I post that separately ?
rgds
Nick

 

[DCrake]

You can use the DCount is much the same way, such as

X = Nz(DCount("*","Table","[Field] Like '*Prepaid*'"),0)

I have wrapped the Nz() function around the count to prevent no records returning false.

David

 

[NickNeville]

Hi David
I have a little diff with this as new to VB
but
I put in If Me.Card_Issue_Sub_Form_Subform!ShootType = Nz(DCount(",=3","Table","[ShootType] Like '*Prepaid*'"),0) then
Msg box etc

It doesn't like the table or the <= pieces
?

Thanks
Nick

 

[DCrake]

A little bit morer explanation required:

Lets say we wanted to know how many records where prepaid and we wanted to show that figure on the form. First we would add a textbox to the form and make it Enabled = False and Locked = True.

Then in the control source of the textbox we would enter

= Nz(DCount("*","<<TableName>>","[ShootType] Like '*Prepaid*'"),0)

What we are saying is count the number of records in the table where the Shoot type contains the phrase PrePaid

You need to replace the <<TableName>> with the actual name of the table in your application.

David

 

[NickNeville]

Oh I see, yes that would work fine.
Then I could show a message based on the result of the text box ?
Many many thanks for your patience
Nick

 

[NickNeville]

Hi David
Just one last thing, that works fine, but can I show the individual members Prepaid count within this code, as presently it shows the total for all the members. ?

Nick

 

[DCrake]

If you create a new query and bring down the ShootType and group by this field. Then add another column to the query Cnt:1 and set this to Count. Then under the ShootType in the condition row enter Like "*PrePaid*"

This shoud group your data by by shoot type giving you a total for each different one it finds.

David

 

[NickNeville]

Yes thankyou
I just realised that I would have to quote the Form in the query and show the result
on the form from that query.

Once again
many thanks
best rgds
Nick

 

[DCrake]

Don't do that. Use a dlookup on the query from the form for that particular ite.By rferencing the form from the query you are restricting the query only used by that form.

David

 

[NickNeville]

ok , I will try that.
I may be back !
Thanks
Rgds
Nick