Partial Field Query

[dataheadache]

Hi, I'm new to Access, and have managed to self-teach myself it up to now. I have built a database that tells me what I have recorded on my DVD Recorder, however, one of the main things I want it to do, I can't get it to - and was hoping someone could help.

I have a field called [Prog_Name] and would like to enter into an expression value box (when the query is run) part of the name of a programme I have recorded - i.e. Enders would find EastEnders. My last attempt managed to bring up the said programmes, but also brought up quite a few others that I didn't want and didn't contain what I asked it to search for at all.

What do I need to type into the criteria box to be able to achieve my objective?

HELP!

 

[Mile-O]

I don't know where you are typing Enders so I'll leave the criteria as one of those horrible parameter prompts. You can change it to reference a form, function, etc.

This one will return all records where the word can begin with anything but must end with whatever you type
Like "*" & [Enter Text]

This one will return all records where the text you type can be anywhere within the record
Like "*" & [Enter Text] & "*"

 

[dataheadache]

Thanks. It worked! (And yes, I was using one of those horrible parameter prompts - at the moment - they are the only thing I've worked out to help get specific data out of the database!)

 

[Mile-O]

Put a textbox on a form - for this purpose I'll call them txtExample and frmExampe.

Basically, you refer to the textbox control:

Like "*" & [Forms]![frmExample]![txtExample] & "*"

 

[dataheadache]

Sorry, you've lost me now - I haven't ventured into the world of forms much yet - except those created by a wizard...

 

[dataheadache]

I have also previously managed to get it to sort items into Disc Order (i.e what disc they were recorded on) - is there anyway to combine that and the Programme search into one Parameter value box? i.e.

So it would display as:

Disc Number: [Enter Number] (where it reports what is on a disc)

OR
Programme Search Term:
[Prog_Name]

and be able to enter either (not both) for it to report the results.

 

[Mile-O]

Fair enough...at least you know what to do when you start to use forms as you shouldn't really be entering details directly into the table; you're okay, though, as it's just a personal database.

 

[dataheadache]

Fair enough...at least you know what to do when you start to use forms as you shouldn't really be entering details directly into the table; you're okay, though, as it's just a personal database.

I'm not entering them into a table - I mamged to get a wizard to build me a form.. Any ideas on the other question I posted above your last reply?

 

[Mile-O]

If you go up to the menu, where it says Query there is an option called Parameters.

In here you can define a parameter, we'll call it Example and you have to set it's data type; this'll be text.

Now, in the query grid, you can put - on one line:

Like "*" & [Example] & "*"

And, on the other field that has the number:

CLng([Example])

 

[Mile-O]

Sorry, my mistake for being hasty.

The only problem is that if you type a text field in then you will get a data type mismatch.

So, rather than CLng([Example]) you need to change it to this:

Eval(IIf(IsNumeric([Example]),CLng([Example]),0))

 

[dataheadache]

Sorry to go back to this, but is there anyway to programme the parameter value box to display a combo box rather than a text box?

For example - I have a [Type] field - in the table setup, I have this set to a combo box where I can choose FILM or TV or DV (Digital Video Camera) when entering records - rather than asking me to type in the FILM or TV in to the parameter value box when I want to get data out of the database, can you just select it from a programme, and press OK to get the required results, rather than having to type the actual words?

 

[Mile-O]

Sorry to go back to this, but is there anyway to programme the parameter value box to display a combo box rather than a text box?

No. That's why it's best to make a form. To ease the user's method of searching.