Query problem

[evanhughes]

Hi All

My problem is this.
I have a Table that I need to query. The field I need to query by is a combination of two fields in another table selected at time of data input. It contains job number and model type. I need to query it by job number only and I need user input to select the job number. I have tried like name = number = and nothing seems to work, even tried using wildcards to no avail.
The field presents like 1111 -- model type and I need an expression that will prompt the user for job number then display all records related to that job number only Blank fields disregarded.

Any insight would be appreciated.

Evan

 

[Galaxiom]

I would strongly advise you to rework your database input to use two separate fields.Then you would have a very simple query.

Is the job number a fixed length?
Left([fieldname], 4) will get the four characters on the left of the field.

 

[evanhughes]

Thanks for the reply I will try it. But how do I word the expression to allow user input with the 4 digit name required.

Evan

 

[Galaxiom]

Place an unbound textbox on the form for the input.

Refer to the textbox in your Record Source query as:
Forms!formname!textboxname

eg:
WHERE [jobnumber] = Forms!formname!textboxname

Also if you want to display the two field in your original format use the control source expression:
=[jobnumber] & [modeltype]

Or for a fixed number of digits (including leading zeros if required) in the jobnumber:
=Format([jobnumber],"0000") & [modeltype]