wildcards in vba string??

[CHAOSinACT]

i am trying to sort all variations of a job number
ie:
job 093 has

093-20
093-10
etc

if i enter 093* in query criteria it works (as i would think!) but i need it to adapt to whatever job number is current on the fly. i can write a script to get current job number from form:

Form_frmCivilMinorJobs.txtCivilJobNumberDisplay.Value

and return through a function, that give me "093" only (missing variations) but if i type

Form_frmCivilMinorJobs.txtCivilJobNumberDisplay.Value & "*"

it comes back with NOTHING instead of more! i've tested the variable, its sending "093*" and doent work, though typing that into query directly does!

is there some trick to using wildcards within VBA?

 

[boblarson]

In the criteria of a query you don't use Form_ to do it you use:

[Forms]![frmCivilMinorJobs]![txtCivilJobNumberDisplay] & "*"

And if it is a text field you need quotes:

Chr(34) & [Forms]![frmCivilMinorJobs]![txtCivilJobNumberDisplay] & "*" & Chr(34)


And don't get in the habit of using the Form_FormName...etc. as a reference because that will actually open the form HIDDEN and can cause you pain.

 

[CHAOSinACT]

And don't get in the habit of using the Form_FormName...etc. as a reference because that will actually open the form HIDDEN and can cause you pain.

thats unfortunate.... lol.

 

[boblarson]

Oh, I just noticed you said VBA -

So it would be:

Chr(34) & Forms!frmCivilMinorJobs.txtCivilJobNumberDisplay & "*" & Chr(34)

 

[CHAOSinACT]

huh no joy anyway. very odd.

 

[boblarson]

thats unfortunate.... lol.

Not really, you just need to use

Forms!FormNameHere.WhateverControl

or

Forms("FormNameHereInQuotes).WhateverControl

 

[boblarson]

huh no joy anyway. very odd.

Did you see my other post - I gave you originally the syntax for in a QUERY not VBA. You need to use

Chr(34) & Forms!YourFormName.ControlName & "*" & Chr(34)

in VBA.

 

[CHAOSinACT]

got it!

thanks for the help, the final baby was

Like "*" & [Forms]![frmCivilMinorJobs]![txtCivilJobNumberDisplay] & "*"

*looks at mass find and replace regarding your previous comment*
cheers

 

[boblarson]

Re: got it!

thanks for the help, the final baby was

Like "*" & [Forms]![frmCivilMinorJobs]![txtCivilJobNumberDisplay] & "*"

*looks at mass find and replace regarding your previous comment*
cheers

For VBA don't use the square brackets - just use

Like "*" & Forms!frmCivilMinorJobs.txtCivilJobNumberDisplay & "*"

And note the use of the DOT (not the bang) after the form name and before the control name.

 

[Galaxiom]

Re: got it!

Like "*" & Forms!frmCivilMinorJobs.txtCivilJobNumberDisplay & "*"

And note the use of the DOT (not the bang) after the form name and before the control name.

My understanding is the Bang is correct as it is separating Objects in this context. The Dot is for separating properties and methods from an object.

Since Access will tolerate the dot in the place of a bang but not vice versa, and it works with the Bang, the technically correct operator really must be the Bang. (Many developer use the dot everywhere because the bang doesn't work with Intellisense.)

You do have to use the Dot where the control is named like this:

Forms!formname.Controls("controlname")

Controls is a Collection and in this context Controls("controlname") becomes a property of the form rather than an object in its own right.

 

[Galaxiom]

And don't get in the habit of using the Form_FormName...etc. as a reference because that will actually open the form HIDDEN and can cause you pain.

Form_formname is a reference via the module rather than the form object which would explain why the form is kept hidden.

This nomenclature and the separation of the event procedure name from the module name by the underscore is another reason why I discourage the use of underscores in form names.

 

[boblarson]

Re: got it!

My understanding is the Bang is correct as it is separating Objects in this context. The Dot is for separating properties and methods from an object.

Since Access will tolerate the dot in the place of a bang but not vice versa, and it works with the Bang, the technically correct operator really must be the Bang. (Many developer use the dot everywhere because the bang doesn't work with Intellisense.)

Not completely - The Dot can be important in the event that the field and the control are named the same. Usng the dot will refer to the control explicitly in that case and using the bang will refer to the field. Depending on what you need to do, one or the other may be extremely important which you use.

 

[CHAOSinACT]

*grin* i love an academic argument .... yeah i think i fell for loving intellisense too as i hadn't used access in about 10 years when i started again...

fortunately i don't call the forms_ argument unless the form is going to be open anyway, still a good point. i must say, i love this place. like a university library with a GOOD discussion. thanks again guys

 

[Galaxiom]

Re: got it!

Not completely - The Dot can be important in the event that the field and the control are named the same. Usng the dot will refer to the control explicitly in that case and using the bang will refer to the field. Depending on what you need to do, one or the other may be extremely important which you use.

Thanks for that Bob.

Additionally I recall (hopefully accurately) that the dot will refer to the field in the Record Source if there is no control by the name. Apparently Access does this kind of substitution according to a hierarchy of defaults. Since the form wizard names the controls to match the field this can cause surprising results especially when a control is changed to unbound. The use of the dot or bang further confuses and it is certainly to be aware of what Bob just explained.

Sometimes I think Access is way too willing to guess what the developer wants and stands in the way of actually comprehending what goes on.

If I really wanted to refer explicitly to a field I think I would use its full reference:
Forms!formname.Form.RecordSet!fieldname

When I was getting my head around subform references I made a little database that clearly demonstrated the variants and defaulting sequence. I should dig it our again, add what Bob just elucidated and post it here.

 

[datAdrenaline]

Re: got it!

>> Additionally I recall (hopefully accurately) that the dot will refer to the field in the Record Source if there is no control by the name. <<

Actually, a form has no idea what a field is, unless you go through the Recordset or RecordsetClone properties. An Access form object creates a set of "special" hidden controls that hook up to the fields of its bound recordsource. So the dot is still refering to a control ... not a field.

 

[datAdrenaline]

Re: got it!

Oh ...

>> If I really wanted to refer explicitly to a field I think I would use its full reference:
Forms!formname.Form.RecordSource!fieldname <<

Is an invalid syntax ... I am certain you were going for this:

Forms!formname.Form.Recordset!fieldname

 

[Galaxiom]

Re: got it!

Oh ...

>> If I really wanted to refer explicitly to a field I think I would use its full reference:
Forms!formname.Form.RecordSource!fieldname <<

Is an invalid syntax ... I am certain you were going for this:

Forms!formname.Form.Recordset!fieldname

Exactly. I finished that post in a hurry and had to leave immediately. Usually I read my posts after they are uploaded.

I actually fixed it when I came back in and read your previous post. I was relieved when I thought nobody had noticed. Then I saw the thread had a second page.

 

[datAdrenaline]

Re: got it!

>> I was relieved when I thought nobody had noticed. Then I saw the thread had a second page. <<

ROFL!! ... I've been there many times!!! ... so I can sympathize with the scenario!