dlookup using multiple criteria (1 Viewer)

[109bow]

Hi All,
I have a text box in a report that uses DLOOKUP to select a record from a query but I have no idea how this should look, so would like a bit of help.
I created below using the expression builder, but not surprisingly it doesn't work and returns #NAME.
=DLookUp([record_sheet3_qry]![axleserial],[record_sheet3_qry],[record_sheet3_qry]![EvenOddunits]=1, [record_sheet3_qry]![carid] =110, [record_sheet3_qry]![wheelposition] =1)

As I understand it, I am looking for axle serial, in record_sheet3_qry, where EvenOddunits = 1 AND carid=110 and wheelposition=1

Any ideas, thanks

 

[Minty]

Your syntax is off by some margin.
Dlookup("TheFieldName", "TheTableName", "Criteria goes in here")

So your expression is going to be something like

Dlookup("axelserial","record_sheet3_qry", "[EvenOddUnits] = 1 AND [carid] =110 AND [wheelposition] = 1")

Check the link in my signature if you need to use text or date criteria instead of numbers.

 

[109bow]

Thanks Minty,
I have rearranged the criteria to the same order as seen in query record_sheets3_qry but I am now getting #ERROR.

=DLookUp("axelserial","record_sheet3_qry","[carid]=110 AND [wheelposition]=1 AND [EvenOddunits]=1")

Does it make any difference the report has no control source?
Thanks

 

[Minty]

It shouldn't do.
Are the fields carid, wheelposition and EvenOddUnits all number fields?

Add your saved query to a new blank query window and pull in the axelserial, and paste the dlookup into it as a calculated field. See if it runs or gives you a may meaninful error.

 

[109bow]

the 3 fields you stated are all expressions.
wheelsetposition is an expression in record_sheet3_qry
carid and EvenOddunits are expressions in record_sheet2_qry, which record_sheet3_qry is based on.

Not sure I understand your suggestion. Have created a new query with a field of axleserial and pasted the dlookup into the criteria, it returns a data mismatch type

 

[Minty]

No don't put it in the criteria. Put it in as a calculated field, on the top line of the query designer;

Blurt: DLookUp("axelserial","record_sheet3_qry","[carid]=110 AND [wheelposition]=1 AND [EvenOddunits]=1")

Interesting that it returns a type mismatch though... If you view query 3 in datasheet view where are the fields justified with the cells, left or right??

 

[109bow]

carid and wheelposition are justified to the left and EvenOddunits to the right.
Have created a query as you said and it returns a data type mismatch in criteria expression

 

[Minty]

That means EvenOddUnits is being treated as a number field, but carid and wheelposition are text, hence your error. Put some delimiters around the first two criteria as per;

Blurt: DLookUp("axelserial","record_sheet3_qry","[carid]=[COLOR="red"]'110'[/COLOR] AND [wheelposition]=[COLOR="red"]'1'[/COLOR] AND [EvenOddunits]=1")

 

[109bow]

thanks Minty,
have just tried as you suggested,
blurt: DLookUp("axleserial","record_sheet3_qry","[carid]='110' AND [wheelposition]='1' AND [EvenOddunits]=1")

but still get the data type mismatch error

 

[Minty]

Okay - remove the dlook up and simply add the carid wheelposition and EvenOddUnits to the query output. Put your criteria 110 , 1 ,1 under the appropriate field.
What does the query do when you try and run it? Can you post a picture of it after it has run.

 

[109bow]

done as requested
thanks

 

[Minty]

Okay - and the results were... ?

 

[109bow]

when I added the criteria got an error message, please see attachment

 

[Minty]

Soz - no attachment for me to peer at?

 

[isladogs]

I must need new glasses as I can't see anything....

 

[109bow]

sorry, trying to do 10 things at once at work!
Have played about a bit using a different field, shortno" in the query, this now gives what I'm after in a query, just need to make it work using a dlookup

SELECT record_sheet3_qry.carid, record_sheet3_qry.wheelposition, record_sheet3_qry.shortno, record_sheet3_qry.axleserial, record_sheet3_qry.avepostwd
FROM record_sheet3_qry
WHERE (((record_sheet3_qry.carid)="110") AND ((record_sheet3_qry.wheelposition)="1") AND ([shortno] Mod "2"<>"0"));

 

[Minty]

Ewww that's messy. If you are creating numbers in an expression then please leave them as numbers. I'm amazed Mod "2" even works ?

Can you post up the calculations from the underlying query ? Having numbers stored or even calculated as text is going to give you a whole host of issues moving forwards...

 

[109bow]

Sorry for taking a while to get back, but work has been manic!
I have attached a slimmed down version of my bd.
On front page2 info is entered, a number of queries are then used so in the end text boxes in the 2 reports can be populated.
The problem is using the DLookup method, it takes a long time to fill all the text boxes in the reports. In the full db there are 25 reports that will use DLookup, making it very slow.
As yet I havn't been able to find a way, without DLookup, of creating a query that has all the required data in one row, ie. serialnumber and avepostwd for all 32 wheel positions.
Any thoughts very welcome.

 

[Minty]

Okay, based on a very quick look, I don't think you have your data layout correct. The following are just observations, not criticisms.

You appear to have a large number of text fields holding numeric values. Whilst this isn't a complete no no, it does make your data very messy to check and deal with, as you have found. Generally a field that is called SomethingNo or ThingID would be considered by default to an outsider to be a numeric data field.

My first thought is wherever possible change those into number fields.

It also appears that you have two Identical tables Even Units and Odd units, with the only difference being that one holds Odd ID's and the other Even ID's. This doesn't really make sense as you could easily determine what was odd or even, and keep this in one table that would probably make your queries easier to develop and maintain.

You also seem to have quite a lot of data stored in the main table data_tbl, that you could easily reference back to a another table - the carNo seems to be directly related to the Carid - so don't store both.

The KM field appears to be pulled from the data in kmnow_tbl related back to the shortno and the reading date - so you are storing that data twice.

The general rule of thumb with any database layout is - only store things once. Any time you store the same information twice you enter the world of uncertainty. What if one value is edited, which of your two stored values is then correct... ?

It might assist us if you could describe in simple terms what you are trying to do, what is the business model for your database? No database terms just good old fashioned explaining it to a person you are walking around on a visit.

 

[109bow]

Thanks for your time Minty,
I work for a train company where we turn the train wheels on a lathe. As part of the turning process, I need to create a number of reports / forms, that when printed off lathe operators can see wheel serial numbers and last turn wheel sizes for every wheel on the train. Also some forms will have blank boxes where the operators will write a number.
Each train is made up of 2 units, one even numbered and one odd numbered unit, each unit is made up of 4 carriages so a total of 8 carriages.
Form front page2 allows operators to select an even number and odd number unit along with entering mileage (km), work order number, date, and maintainers name.
As you have seen, I am struggling to find an efficiently fast way of populating the text boxes in the reports.
Thanks again